Slow Page Load Large SQL Table - Help

[coolshrimp]

I have a php page that loads a table with value from a MYSQL Table but it has almost 5000 rows. witch makes the page load very slow somtimes times freeze browser.

$SQL = "SELECT ID, First_Name, Last_Name, DOB, Phone FROM Customer_Info";$result = mysql_query($SQL);$fields_num = mysql_num_fields($result);$row_num = mysql_num_rows($result);echo <<<EOT<table class="datatable"><thead><tr>EOT;// printing table headersfor($i=0; $i<$fields_num; $i++){$field = mysql_fetch_field($result);echo <<<EOT<th>{$field->name}</th>EOT;}echo <<<EOT<th>Action</th></tr></thead><tbody>EOT;// printing table rowswhile($row = mysql_fetch_assoc($result)){    echo "<tr>";    // $row is array... foreach( .. ) puts every element    // of $row to $cell variable    foreach($row as $cell)        echo "<td>$cell</td>";        echo "<td style='text-align:center'><a href='ViewCustomer.php?id=" . $row['ID'] . "' style='color:#FFF'><button class='blue'>View</button></a> | <button onclick='var rowID=" . $row['ID'] . "; var string="includes/mysql/DeleteCustomer.php?id=" . $row['ID'] . ""; var result="alertBanner"; if (confirm("Are you sure you want to delete this customer?")) {ajaxLoad(string, result); $("#dt1").find("td:contains(" . $row['ID'] . ")").parent().remove(); }' style='color:#FFF' >Delete</button></td>";    echo "</tr>n";}echo <<<EOT</tr></tbody></table>EOT;mysql_free_result($result);mysql_close($connnect);

[Ingolme]

Search for PHP pagination tutorials on your preferred search engine. The concept of pagination was invented for this reason.

[coolshrimp]

im allready using a jquery datatabel script with does pagination with search and all that."http://www.datatables.net/"

[Ingolme]

You need a PHP solution. jQuery still requires the entire table to be downloaded before doing the pagination.

[coolshrimp]

do you know of a code that can genereate what this jquery script does?i need the abbility to search rows, have pagignation, order colums by

[Ingolme]

SQL is the language you use to limit the amount of results you get and to order them. You use the LIMIT and ORDER BY clauses.

 

Like I said, search for PHP pagination tutorials.

[coolshrimp]

is there anyway i can make my current code work faster without changing to php pasignation? i really like how this jquery one works.

[Ingolme]

The reason your page is loading so slowly is that you're sending the data of 5000 records to the client. That takes some time to load. jQuery can only work with data that has already be sent from the server, so it cannot be used as a solution to the slow loading times.

 

You could reduce the content length a little bit by removing the inline style attributes from the elements you're generating and using a stylesheet to apply it instead (something you should be doing anyways), it would reduce the data by over 200 kilobytes.You can move all that Javascript you're generating into a generalized function and assign event handlers from a script tag somewhere else on the page instead of using event attributes. These changes probably would not decrease the download time enough.

 

What would really solve the problem is to reduce the amount of data you're sending. 5000 records is a whole lot, sending 50 at a time would be more reasonable.