php/mysql while question

[FlipperBizkut]

OK... so I have 2 rows of tables that I am trying to populate (one on top of the other). I can get all the information I need through one query of the database, but I need two 'while' statements to populate the web page tables. I am getting no data to the second 'while' statement unless I run the query again. It is easier to explain with the code:

$query = "SELECT a, b FROM db";
$result = mysql_query($query);
echo '<tr>';

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<td>' . $col['a'] . '</td>';
}
echo '</tr><tr>';

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<td>' . $col['b'] . '</td>';
}
echo '</tr>';

Doing it this way, either the second 'while' statement is not executing, or it is getting no data passed to it. Now, if I put in the same query again (this time before the second 'while' statement), it works exactly as I would want it to. Like this:

$query = "SELECT a, b FROM db";
$result = mysql_query($query);
echo '<tr>';

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<td>' . $col['a'] . '</td>';
}
echo '</tr><tr>';

[COLOR=Magenta]$query = "SELECT a, b FROM db";
$result = mysql_query($query);[/COLOR]

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<td>' . $col['b'] . '</td>';
}
echo '</tr>';

Why is it doing this? Is the second example bad programming? Is there anything I can do to make this more 'elegant'? Won't making 2 queries use more resources and take more time than just doing it once?

Thanks for looking.

 

[HHunt]

A while-loop like that will loop until it has gone through all the rows of the result. When you start a new, identical loop on the same results, the results will already be ... used, so to speak (I don't know if they still contain all the rows and you can tell it to start over again, or if you have to find another solution. I'm sure the manual can help.)

edit: You can probably use mysql_data_seek($result, 0) to reset it.

 

[FlipperBizkut]

You can probably use mysql_data_seek($result, 0) to reset it.

You rock! That is exactly what I was looking for. It works like a champ. Used it like this:

$query = "SELECT a, b FROM db";
$result = mysql_query($query);
echo '<tr>';

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<td>' . $col['a'] . '</td>';
}
echo '</tr><tr>';

[COLOR=Lime]mysql_data_seek($result, 0);[/COLOR]

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<td>' . $col['b'] . '</td>';
}
echo '</tr>';

Thanks a bunch. I don't know if it will be any faster or better, but it seems like it would.

 

[HHunt]

I agree, it seems like it should be better.
(At the very least I doubt that it'll be worse. )

 

[generelz]

Instead of iterating over your result rows twice, why not just keep two temporary variables $first_table and $second_table, and echo your html to those variables as you are iterating over the result set once, a la:

$query = "SELECT a, b FROM db";
$result = mysql_query($query);

$first_row = '<tr>';
$second_row = '<tr>';

while ($col = mysql_fetch_array($result, MYSQL_ASSOC)) {
$first_row .= '<td>' . $col['a'] . '</td>';
$second_row .= '<td>' . $col['b'] . '</td>';
}

$first_row .= '</tr>'
$second_row .= '</tr>'

echo $first_row
echo $second_row

I'm a little rusty on my php syntax but I think what I wrote is syntactically valid. If not I think it's easy enough to comprehend. If you have a lot of rows fetched with that query you don't want to have to iterate over the result set twice. Just loop once and keep your HTML in temporary variables.

edit: for strings in php use .= not +=

 

[FlipperBizkut]

That is certainly interesting generelz. The array getting returned from mysql only has 5 rows max, so I don't think that it would be any problem running through it a second time. I wonder which would have increased performance: running through it twice, or storing all the info in variables?

 

[KingKaeru]

That is certainly interesting generelz. The array getting returned from mysql only has 5 rows max, so I don't think that it would be any problem running through it a second time. I wonder which would have increased performance: running through it twice, or storing all the info in variables?

do it like in generelz' example. it's more efficient.

--KK

 

[HHunt]

do it like in generelz' example. it's more efficient.

--KK

For 5 rows, I agree. If you ever need to handle huge data sets I'm sure the memory usage would be a problem, but that's a nonissue here.

 

[KingKaeru]

Exactly.

Unless you're short on memory, there should be no need to use two loops; it's too expensive. Now's the time to learn good programming practices and understand *why* you do things certain ways and what the implications are if you don't.

--KK

 

[HHunt]

Exactly.

Unless you're short on memory, there should be no need to use two loops; it's too expensive. Now's the time to learn good programming practices and understand *why* you do things certain ways and what the implications are if you don't.

--KK

Depending on how the result is stored, looping through it two times might not really be that more expensive. I suspect it's buffered in the connector, so all a second loop adds is moving data from the connector to PHP twice (and not the same data, at that).
Storing things locally like this is generally a good idea (because reading from external sources is often slow), but I doubt this is one of the cases where it matters. If I'm right about the connector storing the result internally, it doubles the memory use for a very small performance gain. I might however be wrong, and that's why it's still the best way to do it.

 

[KingKaeru]

I was speaking of costs at a more basic conditional count. with the two loops you are doing twice the amount of checking which is considered expensive. Buffered or not.

Regardless, it's obvious the OP is still learning and it's best to have him learn good programming practices than sloppy ones. Even though it still "works".

--KK

 

[FlipperBizkut]

Regardless, it's obvious the OP is still learning and it's best to have him learn good programming practices than sloppy ones. Even though it still "works".

--KK

And there it is. Exactly what I wanted to hear. I am just learning, and pretty much teaching myself using a book and these forums. In a structured environment, I could ask the teacher (and expect to learn from the teacher) what the best method is. But, I am really just flying by the seat of my pants right now.

Thanks for taking the time to teach this properly. I did recode the page, and it works just fine using the example generelz provided. I'm glad to have the [H]ard|Forum as a resource, and I am sure that the [H] is glad to have such helpful members such as you guys. Thanks again, and I'm sure more questions will come soon.