Page 1 of 1

Takes long time to laod

Posted: Thu Jul 31, 2008 10:26 pm
by wasir
I have few similar pages to show tabular data. But there's something wrong in my coding that it takes really long to load the page. I've also tried changing to different server, but no big difference. Please help...

Code: Select all

<?php
include ('../../includes/functions.php5');
session_start();
if ($_SESSION['group'] == manager) {
    authenticate_mgr();
} elseif ($_SESSION['group'] == admin) {
    authenticate_adm();
} elseif ($_SESSION['group'] == concierge) {
    authenticate_con();
} else {
    header ('location:../index.php5');
}
 
// connecting to database
if (!$link = dbconn()) {
   echo '<p class=err>Error1: ' . mysqli_connect_error() . '</p>';
   exit;
}
 
// script for getting page numbers
$rowsPerPage = 15;
$pageNum = 1;
if(isset($_GET['page'])) {
    $pageNum = $_GET['page'];
};
$offset = ($pageNum - 1) * $rowsPerPage;
 
// query
if (!$result = @mysqli_query($link, 'SELECT rep_relates.sno, rep_relates.timestamp, rep_location.repno, rep_location.bldng, rep_location.unit, rep_location.level, rep_type.repno, rep_type.type, rep_description.repno, rep_description.date, rep_description.heading, rep_status.repno, rep_status.start_date, tbl_bldngname.sno, tbl_bldngname.name FROM rep_relates, rep_location, rep_type, rep_description, rep_status, tbl_bldngname WHERE rep_relates.sno=rep_location.repno AND rep_location.repno=rep_type.repno AND rep_type.repno=rep_description.repno AND rep_description.repno=rep_status.repno AND rep_status.compl_date<>\'0000-00-00\' AND rep_location.bldng=tbl_bldngname.sno ORDER BY rep_relates.timestamp DESC')) {
    echo mysqli_error($link);
    exit;
}
 
// page starts here
include ('myincludes/header.inc');
 
if (($_SESSION['group'] == concierge) && ($_SESSION['logging'] == home)) {
    include ('myincludes/nav_con_h.inc');
} elseif (($_SESSION['group'] == concierge) && (!$_SESSION['logging'] == home)) {
    include ('myincludes/nav_con_s.inc');   
} else {
    include ('myincludes/nav_mgr.inc'); 
}
?>
 
<div id="listing1">
    <div id="listingbar">
        <?php include ('myincludes/nav_reports.inc'); ?>
    </div>
    <div id="listing">
        <table>
            <caption align="left">Completed Reports</caption>
            <thead>
                <th>Date</th>
                <th>Building</th>
                <th>Unit</th>
                <th>Level</th>
                <th>type</th>
                <th>heading</th>
                <th>view</th>
            </thead>
            <tbody>
                <?php
                while ($row = mysql_fetch_assoc($result)) {
                    $id = $row['repno'];
                    echo '<tr>';
                    echo '<td>'.date('d M y', strtotime($row['date'])).'</td>';
                    echo '<td class=capize>'.$row['name'].'</td>';
                    echo '<td>';
                    if ($row['unit']=='') {
                        echo '-';
                    } else {
                        echo $row['unit'];
                    };
                    echo '</td>';
                    echo '<td>';
                    if ($row['level']=='') {
                        echo '-';
                    } else {
                        echo $row['level'];
                    };
                    echo '</td>';
                    
                    echo '<td class=capize>';
                    if ($row['type']==service) {
                        echo 'service request';
                    } else {
                        echo $row['type'];
                    };
                    echo '</td>';
                    
                    echo '<td>'.$row['heading'].'</td>';
                    
                    // checking status will go here
                                        
                    echo '<td><a href=report_view.php5?repno='.$id.'>View</a></td>';
                    echo '</tr>';
                }
                
            // getting total number of rows
            if (!$result1 = @mysqli_query($link, 'SELECT COUNT(*) AS numrows FROM rep_status WHERE compl_date<>\'0000-00-00\'')) {
                echo '<p>Error: ' . mysqli_error($link) . '.</p>';
                exit;
            }
            $row1 = mysqli_fetch_array($result1);
            $numrows = $row1['numrows'];
            
            // how many pages we have when using paging?
            $maxPage = ceil($numrows/$rowsPerPage);
            
            $self = $_SERVER['PHP_SELF'];
            
            // creating 'previous' and 'next' link
            // plus 'first page' and 'last page' link
            
            // print 'previous' link only if we're not on page one
            if ($pageNum > 1)
            {
                $page = $pageNum - 1;
                $prev = '<a href='.$self.'?page='.$page.'>&nbsp;&nbsp;<&nbsp;&nbsp;</a>';
            
                $first = '<a href='.$self.'?page=1>&nbsp;&nbsp;<<&nbsp;&nbsp;</a>';
            }
            else
            {
                $prev  = ' &nbsp;&nbsp;<&nbsp;&nbsp; ';       // we're on page one, don't enable 'previous' link
                $first = ' &nbsp;&nbsp;<<&nbsp;&nbsp; '; // nor 'first page' link
            }
            
            // print 'next' link only if we're not on the last page
            if ($pageNum < $maxPage)
            {
                $page = $pageNum + 1;
                $next = '<a href='.$self.'?page='.$page.'>&nbsp;&nbsp;>&nbsp;&nbsp;</a>';
            
                $last = '<a href='.$self.'?page='.$maxPage.'>&nbsp;&nbsp;>>&nbsp;&nbsp;</a>';
            }
            else
            {
                $next = ' &nbsp;&nbsp;>&nbsp;&nbsp; ';      // we're on the last page, don't enable 'next' link
                $last = ' &nbsp;&nbsp;>>&nbsp;&nbsp; '; // nor 'last page' link
            }
            
            // print the page navigation link
            echo '<tr><td colspan=8>' . $first . $prev . ' Showing page <strong>' . $pageNum . '</strong> of <strong>' . $maxPage . '</strong>' . $next . $last . '</td></tr>';
            
            ?>
            
            </tbody>
        </table>    
    </div>
</div>
 
<?php
include ('myincludes/footer.inc');
?>

Re: Takes long time to laod

Posted: Fri Aug 01, 2008 12:25 am
by Amit Rathi
Optimize this query as you are using JOIN QUERY with ORDER BY clause, which create a temporary table in memory before displaying data.




# // query
# if (!$result = @mysqli_query($link, 'SELECT rep_relates.sno, rep_relates.timestamp, rep_location.repno, rep_location.bldng, rep_location.unit, rep_location.level, rep_type.repno, rep_type.type, rep_description.repno, rep_description.date, rep_description.heading, rep_status.repno, rep_status.start_date, tbl_bldngname.sno, tbl_bldngname.name FROM rep_relates, rep_location, rep_type, rep_description, rep_status, tbl_bldngname WHERE rep_relates.sno=rep_location.repno AND rep_location.repno=rep_type.repno AND rep_type.repno=rep_description.repno AND rep_description.repno=rep_status.repno AND rep_status.compl_date<>'0000-00-00' AND rep_location.bldng=tbl_bldngname.sno ORDER BY rep_relates.timestamp DESC'))

Re: Takes long time to laod

Posted: Sun Aug 03, 2008 9:35 pm
by wasir
Thanks Amit.

Optmizing query was totally a new topic to me. Using index on mysql tables helped me.