PHP: mysql_fetch_array() in a while-loop takes too long

Question

I am creating an online calendar for a client using PHP/MySQL.

I initiated a <table> and <tr>, and after that have a while loop that creates a new <td> for each day, up to the max number of days in the month.

The line after the <td>, PHP searches a MySQL database for any events that occur on that day by comparing the value of $i (the counter) to the value of the formatted Unix timestamp within that row of the database. In order to increment the internal row counter ONLY when a match is made, I have made another while loop that fetches a new array for the result. It is significantly slowing down loading time.

Here's the code, shortened so you don't have to read the unnecessary stuff:

$qry = "SELECT * FROM events WHERE author=\"$author\"";
$result = mysql_query($qry) or die(mysql_error());

$row = mysql_fetch_array($result);

for ($i = 1; $i <= $max_days; $i++) {

    echo "<td class=\"day\">";

    $rowunixdate_number = date("j", $row['unixdate']);

    if ($rowunixdate_number == $i) {
     while ($rowunixdate_number == $i) {
      $rowtitle = $row['title'];
      echo $rowtitle;
      $row = mysql_fetch_array($result);
      $rowunixdate_number = date("j", $row['unixdate']);
     }
    }

    echo "</td>";

    if (newWeek($day_count)) {
     echo "</tr><tr>";
    }
    $day_count++;

}

Answer 1

Have you run that query in a MySQL tool to see how long it takes?

Do you have an index on the author column?

There's nothing wrong with your PHP. I suspect the query is the problem and no index is the cause.

Answer 2

Is 'author' an id? or a string? Either way an index would help you.

The query is not slow, its the for loop thats causing the problem. Its not complete; missing the $i loop condition and increment. Or is this a typo?

Why don't you just order the query by the date?

SELECT * FROM events WHERE author=? ORDER BY unixdate ASC

and have a variable to store the current date you are on to have any logic required to group events by date in your table ex. giving all event rows with the same date the same color.

Assuming the date is a unix timestamp that does not account for the event's time then you can do this:

$currentDate = 0;
while(mysql_fetch_array($result)){
    if($currentDate == $row['unixdate']){
         //code to present an event that is on the same day as the previous event
    }else{
        //code to present an even on a date that is past the previous event
        //you are sorting events by date in the query
    } 

    //update currentDate for next iteration
    $currentDate = $row['unixdate'];
}

if unixdate includes the event time, then you need to add some logic to just extract the unix date timestmap excluding the hours and minutes.

Hope that helps

Answer 3

The slowness is most likely because you're doing 31 queries, instead of 1 query before you build the HTML table, as Nael El Shawwa pointed out -- if you're trying to get all the upcoming events for a given author for the month, you should select that in a single SQL query, and then iterate over the result set to actually generate the table. E.g.

$sql = "SELECT * FROM events WHERE author = '$author' ORDER BY xdate ASC";
$rsEvents = mysql_query($sql);
echo("<table><tr>");
while ($Event = mysql_fetch_array($rsEvents)) {
    echo("<td>[event info in $Event goes here]</td>");
}
echo("</tr></table>");

Furthermore, it's usually a bad idea to intermix SQL queries and HTML generation. Your external data should be gathered in one place, the output data generated in another. My example cuts it close, by having the SQL immediately before the HTML generation, but that's still better than having an HTML block contain SQL queries right in the middle of it.

Answer 4

aside from their comments above, also try to optimize your sql query since this is one of the most common source of performance issues.

let say you have a news article table with Title, Date, Blurb, Content fields and you only need to fetch the title and display them as a list on the html page,

to do a "SELECT * FROM TABLE" means that you are requiring the db server to fetch all the field data when doing the loop (including the Blurb and Content which you are not going to use).

if you optimize that to something like:

"SELECT Title, Date FROM TABLE" would fetch only the necessary data and would be more efficient in terms of server utilization.

i hope this helps you.