Multiple queries or large array?

[nggb]

Hello all,

 

I'm kind of new to capability of php and I have a quick question.

 

I'm working on a website where I need to pull about 500 records (with 10+ field each) from a mysql database, organize them based on a field value into 50 smaller arrays, add html code and store them into variables for use in other places of the page.

 

So far, I thought of 2 ways to do this.

(1) perform 50 consecutive queries to MySQL, each time storing them to a different variable or (2) perform 1 query of the entire table, store it into a large array and then split into 50 variables. My question is which one is more efficient of the two methods? Or perhaps there is another method that is more effective. For the first method, I worry about flood warnings from the server, especially when multiple user access the page at the same time. For the 2nd method, I worry about having a large multidimension array causing slowdown or even not holding all the data in the first place.

 

Thanks in advance,

 

Here are the code just in case I'm not making sense in the above paragraph.

 

Method 1

for($i=1;$i<50;$i++)
{
$varName = "table".$i;
$$varName = "<table><tr><td>Current Guest</td><td>Number</tr>";
$list = mysql_query("SELECT * FROM Guest WHERE TableNum = '$i'");
while($row = mysql_fetch_array($list))
	{
	$$varName .= "<tr><td>" .$row['FirstName'] ." " .$row['LastName'] ."</td><td>" .$row['NumAttend'] ."</tr>";
	}
}

 

Method 2

for($i=0;$i<50;$i++)
{
$varName = "table".$i;
$$varName = "<table><tr><td>Current Guest</td><td>Number</tr>";
}
$list = mysql_query("SELECT * FROM Guest");
while($row = mysql_fetch_array($list))
{
$varName = "table".$row['TableNum'];
$$varName .= "<tr><td>" .$row['FirstName'] ." " .$row['LastName'] ."</td><td>" .$row['NumAttend'] ."</tr>";
}

[Fergal Andrews]

Hi nggb,

If you are going to be making 50 identical database queries everytime a user hits a page then your best bet is to use caching, maybe memcached. This will need to be available on your server. If the queries are going to be unique for each user then caching isn't going to help much. Making 50 hits to a database is going to kill the server it's running on if you get many consecutive users. One large query and a resulting large array is going to be slow for the user and difficult to work with but I reckon it is better than 50 queries.

Fergal

[roopurt18]

What's the table structure and what's the HTML output you want to create?  Chances are you can do this with a single query and one loop over the record set.

 

(edit) I shouldn't reply to these things when I'm tired.  Perform a

select firstname, lastname, tablenum from thetable order by tablenum

and then create a single loop.  Each time the $row['tablenum'] field changes value, close the "current" html table and start a new one.

[nggb]

Thank you for your replies.

 

Fergal, I'll take a look into memcached. The queries will be the same every time, but the information pulled from the database will change as the users make their changes.

 

roopurt18, basically after the loops are done, I like to have variables "table1" to "table50" which will be fed into a javascript function to be display individually when users initiate actions like mouseover or click. And I'll definitely prune the mysql select to just the fields I need. Thanks for reminding me. However, I'm not sure what you mean by using a single query and a single loop.

[jl5501]

If the query results are only going to appear on mouse over or click, then you could consider getting the response from the server when the data is actually needed, with an ajax call

[roopurt18]

<?php

$rows = mysql_query( "select `FirstName`, `LastName`, `NumAttend`, `TableNum` from `Guest` order by `TableNum`" );
if( $rows ) {
    $current_table = false;
    while( $row = mysql_fetch_object( $rows ) ) { // I prefer *_fetch_object() as the syntax is easier when getting column data
        if( $current_table !== $row->TableNum ) {
            echo <<<EOT
                <h1>Table {$row->TableNum}</h1>
                <table>
                    <tbody>
                        <tr>
                            <th>Current Guest</th>
                            <th>Number</th>
                        </tr>
EOT;
        }
        echo <<<EOT
                        <tr>
                            <td>{$row->FirstName} {$row->LastName}</td>
                            <td>{$row->NumAttend}</td>
                        </tr>
EOT;
        if( $current_table !== $row->TableNum ) {
            echo <<<EOT
                    </tbody>
                </table>
EOT;
        }
        $current_table = $row->TableNum;
    }
}else{
    echo "No table data.";
}
?>