Takes long time to laod

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
wasir
Forum Commoner
Posts: 49
Joined: Sun Jul 08, 2007 11:28 pm

Takes long time to laod

Post 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');
?>
Amit Rathi
Forum Newbie
Posts: 7
Joined: Fri Jul 25, 2008 6:17 am
Location: India

Re: Takes long time to laod

Post 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'))
wasir
Forum Commoner
Posts: 49
Joined: Sun Jul 08, 2007 11:28 pm

Re: Takes long time to laod

Post by wasir »

Thanks Amit.

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