如何在没有while（）循环的情况下从mysql数据库获取所有行以增加页面加载时间？

Question

Well, In my mysql database there are more than 3,000 rows exists. It's continuously growing. Now I'm using following query to show all data on html row (tr). it's take few more seconds to show the all data.

Is there any way to show all data more quickly without using PHP while loop ? Please give me your advise/solution after check my all code. Thank You.

<?php
echo "<table width='540' cellpadding='0' cellspacing='0' border='0' id='topresutl'>";
echo "<thead>";
   echo "<tr>";             
   echo "<td class='' valign='top' width='155'></td>";
   echo "<td class='' valign='top' width='110'></td>";                              
   echo "<td class='' valign='top' width='100'></td>";                              
   echo "<td class='' valign='top' width='95'></td>";                               
   echo "<td class='' valign='top' width='80'></td>";                               
   echo "</tr>";    
   echo "</thead>";
   echo "<tbody>";              

    while($res =  mysqli_fetch_array($get)){
        $cdid = (int) $res['cdid'];
        $family_name = stripslashes(htmlspecialchars($res['family_name']));
        $given_name = stripslashes(htmlspecialchars($res['given_name']));
        $work_phone = stripslashes(htmlspecialchars($res['work_phone']));
        $mobile_phone = stripslashes(htmlspecialchars($res['mobile_phone']));
        $email = stripslashes(htmlspecialchars($res['email']));
        $email_private = stripslashes(htmlspecialchars($res['email_private']));
        $cid = stripslashes(htmlspecialchars($res['cid']));
        $department = stripslashes($res['department']);
        $title = stripslashes(htmlspecialchars($res['title']));

        $getComapnyName =  mysqli_query($link, "SELECT company_name FROM company WHERE cid = '$cid' ");
        $resCompany =  mysqli_fetch_array($getComapnyName);
        $companyName =  $resCompany['company_name'];


        if (strlen($companyName) >= 15) {
            $companyName =  substr($companyName, 0, 14);
        }

        if (strlen($family_name) >= 10) {
            $family_name =  substr($family_name, 0,8);
            }
       if (strlen($given_name) >= 10) {
            $given_name =  substr($given_name, 0, 8);
           }
       if (strlen($department) >= 12) {
            $department =  substr($department, 0, 10);
           }
       if (strlen($title) >= 12) {
            $title =  substr($title, 0, 10);
            }


   echo "<tr onclick='getDetails($cdid), showthirdbox($cdid), visited(this);'>";                                
   echo "<td class='' valign='top' align='left' width='20'>$companyName</td>";
   echo "<td class='' valign='top'>$family_name</td>";
   echo "<td class='' valign='top'>$given_name</td>";
   echo "<td class='' valign='top'>$department</td>";
   echo "<td class='' valign='top'>$title</td>";

   echo "</td>";
   echo "</tr>";
   }
echo "</tbody>";
echo "</table>";    
?>

Answer 1

The first thing you should do is stop running a query to get company name inside your while loop. You need to use a JOIN to select the company name from the company table with your main query.

Since you didn't include your main query, I can't write it for you. But it will look something like this (I'm assuming your main table is named "employees"):

SELECT e.*, c.company_name 
FROM employees AS e 
LEFT JOIN company AS c ON e.cid = c.id

You might want to Google for join syntax to learn more about using joins or to adapt it to your exact usage.

Chances are good this will significantly improve your render time.

If it's still too slow then I would ask how many records you're trying to show on the screen at one time. If you're trying to show all 3,000 records on the screen at once then yes, this is going to be a bit slow no matter how you code it. Instead of showing all records, you need to add pagination. You can start by using the LIMIT syntax of MySQL to select only 10 or 50 or whatever records at a time.