[RESOLVED] Can I speed up this code?

peanutgallery83

I'm having some trouble. In the following code I'm trying to do something very simple, but the way I'm doing it causes the page to load TERRIBLY slowly.

What I'm doing is loading a list of names from a table in the database, and populating 10 drop down menus, on a form, with all of the names. (The drop down menus are for submitting attendance for clinics, hence the reason for 10 of them).

The problem is, the way I'm doing this right now (the only way I've been able to get it to work), is that I have to call the data from the table 10 different times (1 time for each of the 10 drop down menus in the form). Once the names are selected, the individual presses "SUBMIT" and the names are all added to the attendance table.

Is there any way to make this code more efficient? Or is this the only way of doing it?

Any help would be GREATLY appreciated.

 
<?
//set up table and database names
require($_SERVER['DOCUMENT_ROOT'] . '/include/reflist.php');
//build and issue query
$sql = "SELECT reg_id,  reg_l_name,  reg_f_name,  reg_m_name,  trim(concat(reg_l_name, ', ', reg_f_name, ' ' ,reg_m_name)) as referee_name, reg_city
FROM $table_name ORDER BY reg_l_name";

$result1 = @mysql_query($sql,$connection) or die(mysql_error());
$result2 = @mysql_query($sql,$connection) or die(mysql_error());
$result3 = @mysql_query($sql,$connection) or die(mysql_error());
$result4 = @mysql_query($sql,$connection) or die(mysql_error());
$result5 = @mysql_query($sql,$connection) or die(mysql_error());
$result6 = @mysql_query($sql,$connection) or die(mysql_error());
$result7 = @mysql_query($sql,$connection) or die(mysql_error());
$result8 = @mysql_query($sql,$connection) or die(mysql_error());
$result9 = @mysql_query($sql,$connection) or die(mysql_error());
$result10 = @mysql_query($sql,$connection) or die(mysql_error());

?>

Later on, the actual drop down menus are populated with this code (each of the 10 looks the same except they have different names, etc to keep them separated):

<select name="ca_referee1" id="ca_referee1" onFocus=populate(event) onKeyDown=setSelection(event) onKeyPress="javascript:return false">
                            <option selected></option>
                            <?
// put data into drop-down list box 

while ($row = mysql_fetch_array($result1)) { 

$ref_name1 = $row["referee_name"]; 
$ref_city1 = $row["reg_city"]; 
$ref_id1 = $row["reg_id"]; 

echo "<OPTION value=\"$ref_id1\">$ref_name1 - $ref_city1</OPTION>"; 

} 
?>
</select>

NogDog

Just do one query, then after looping through the results to populate the first select list, use mysql_data_seek($result, 0) to "rewind" the result set back to the first row.

Weedpacket

Or loop through the result set once, storing the records in an array; then loop over the array as often as you like.

bradgrafelman

Weedpacket's solution is the most effecient (and the most likely method you'd see in this situation). Something like:

$data = array();
while ($row = mysql_fetch_array($result)) { 
    $data[] = $row;
}

Then you'd just do a [man]foreach[/man] to loop through the $data array (each item being an array itself - one row from the table) to use the data.

NogDog

For that matter, if they're all going to have the same option list, you could just build the option string once, then re-use it for each select list:

<?php
// create options string:
$options = "";
while ($row = mysql_fetch_array($result1)) {
  $ref_name1 = $row["referee_name"];
  $ref_city1 = $row["reg_city"];
  $ref_id1 = $row["reg_id"];
  $options .= "<optionvalue=\"$ref_id1\">$ref_name1 - $ref_city1</option>\n";
}
// then for each select list:
?>
<select name="ca_referee1" id="ca_referee1" onFocus=populate(event) onKeyDown=setSelection(event) onKeyPress="javascript:return false">
                            <option selected></option>
<?php echo $options ?>
</select>

Weedpacket

NogDog wrote:
you could just build the option string once, then re-use it for each select list

Better still. I'm just wondering why all those other fields are being selected from the table as well - is something else going on?

peanutgallery83

Thank you all for your help. My apologies for being so late in replying - flu season has gotten the better of me!

I got it to work. I'm not all that familiar with working with arrays and the foreach functions...haven't ever really had a reason to employ them, though sounds like I need to break out my PHP manuals.

I did get it to work somewhat faster by creating the option list and calling it each time.

With regard to the question about the other data being called from the db...i'm assuming you mean why am I calling things such as "city." The reason being that I'm dealing with over 2500 names, so there are times when people have the same names, so I have to be able to differentiate between them, and by showing the city of residence along with the name, it allows for that.

Thanks again for the help!