current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

I am trying to add 3 combo boxes which all display the exact same information that comes from my MySQL db. It seems like the code I wrote makes the entire page wait until all 3 combo boxes are populated, before continuing.

<?
$query = "Select * from tblWriters order by surname";

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

    $result = mysql_query($query);

    echo "<tr><td>Writer".$i." *</td><td>";
    echo "<select name='txtWriter".$i."' style='width: 200px;'>";
    echo "<option value ='' selected='selected'></option>";

    while ($row = mysql_fetch_array($result))
    {
         echo "<option value ='" . $row['id'] . "'> " . $row['surname'] . ", " . $row['name'] . "</option>";
    }

    echo "</select><td></tr>";
}
?>

I would like to optimize this piece of code, so the query will not be executed 3 times, as I believe this is where the page slows down.

If I put

$result = mysql_query($query);

outside of the for loop, the 2nd and 3rd combo box do not populate. I tried looking into resetting the pointer of the result, but I can't seem to figure out how that works.

Also, is there a way I can reuse the while loop, so I don't have to execute it 3 times?

Can someone point me in the right direction? I'm pretty new to PHP and trying to learn on my own. Any help would be much appreciated. Thanks!

share|improve this question

2 Answers 2

up vote 0 down vote accepted

If you move your mysql_query() out of the loop again, you can reset your mysql-result-pointer by using mysql_data_seek() at the beginning or end of your loop.

This will result in:

 mysql_query($query);
 for($i=1;$i<=3;$i++);
 {
     mysql_data_seek(0); // reset datapointer

     // output querydata
 }

I'm obliged however to point out that the mysql-extension is deprecated by now and you should use mysqli or pdo for new projects and code.

share|improve this answer
    
Many thanks! This was what I was looking for. Also thanks for the tip. I'll look into that! –  VolrathTheFallen May 19 '12 at 0:16
up vote 0 down vote

Cache the query result in an array, then generate your markup:

$query = "Select * from tblWriters order by surname";
$result = mysql_query($query);
$data = array();
while ($row = mysql_fetch_array($result))
{
    $data[] = $row;
}

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

    echo "<tr><td>Writer".$i." *</td><td>";
    echo "<select name='txtWriter".$i."' style='width: 200px;'>";
    echo "<option value ='' selected='selected'></option>";
    foreach ($data as $row) {
       echo "<option value ='" . $row['id'] . "'> " . $row['surname'] . 
            ", " .   $row['name'] . "</option>";        
    }

    echo "</select><td></tr>";
}
share|improve this answer
    
Thanks. This works too, although it doesn't seem to improve performance. Would upvote, but don't have enough reputation yet. –  VolrathTheFallen May 19 '12 at 0:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.