PHP page taking a long time to load

Question

I'm building my website and in one of its pages, I use MySQL and PHP to load some information. Although, the page is taking a long time to load when compared to all the other pages that don't use the database.

In my code, I use many times:

$selectMember = mysql_query("SELECT name, nametag FROM members");
$countMembers = mysql_num_rows($selectMember);
[...]
    for($ID = 1; $ID <= $countMembers ; $ID++){
         $member = mysql_query("SELECT name, nametag FROM members WHERE ID = $ID");
         while($row = mysql_fetch_array($member)) {

I believe the problem is in this part of the code. Didn't want to put the whole one because it has 200 lines. Is there any way to make the database research simpler and/or make the page load faster?

a query inside a loop is almost always wrong. Just get all the members in one query and loop the results — , Jun 24 '14 at 21:13
`mysql_*` functions are depreciated. You should look into using `MySQLi` or `PDO` — Hydra IO, Jun 24 '14 at 21:14

score 3 · Answer 1 · edited Jun 24 '14 at 21:59

3

All you need is:

$member = mysql_query("SELECT name, nametag FROM members");
   while($row = mysql_fetch_array($member)) {

//show results
echo $row['name'];

The pointless loop and one query per id is causing the delay.

edited Jun 24 '14 at 21:59

halfer

19,824
17
99
186

answered Jun 24 '14 at 21:17

score 2 · Answer 2 · edited May 23 '17 at 12:10

In this case you are running queries needlessly, and the "inner" queries are wrong. They only work by chance - because you have members numbered sequentially without "holes", so the ID generated sequentially does find records in the database.

If you do this:

$selectMember = mysql_query("SELECT ID, name, nametag FROM members");

The above already fetches all data you retrieve below, complete with ID:

$countMembers = mysql_num_rows($selectMember);

while($row = mysql_fetch_array($selectMember) {
    ....

That said, mysql_ functions are deprecated; you should use either mysqli_ or (IMHO, better; but your mileage may vary) PDO functions. You most assuredly do not want to learn a DB strategy that's likely to no longer exist in the next PHP version!

Here you will find some examples on how to unleash the full power of PDO with newer MySQLs.

score 0 · Answer 3 · answered Jun 24 '14 at 21:18

What you're trying to do is something like this.....

$ids = "(";
for($ID = 1; $ID <= $countMembers ; $ID++){
    $ids.=$ID.",";
 }
$ids = rtrim(",",$ids);
$ids.=")";

     $member = mysql_query("SELECT name, nametag FROM members WHERE ID IN $ids");
     while($row = mysql_fetch_array($member)) { etc etc

But all the above is redundant and pointless, because youre already fetching the data inside the $selectMember variable. So with just the one query, you already got it all....

 $selectMember = mysql_query("SELECT name, nametag FROM members");
 while($row = mysql_fetch_array($selectMember)) {
      echo $row[0];
      echo $row[1];
      // etc etc
 }

score 0 · Answer 4 · answered Jun 24 '14 at 21:19

0

Not sure why you're doing this, but one query will do it:

SELECT name, nametag FROM members
    WHERE ID BETWEEN 1 AND (SELECT COUNT(*) FROM members)

answered Jun 24 '14 at 21:19

AbraCadaver

78,200
7
66
87

while this is a literal interpretation of the question, it seems unlikely he really wants to limit it to count of id's i just think he's confused – Jun 24 '14 at 21:21

PHP page taking a long time to load

4 Answers4