Do queries only work once?

Master_Zen

I am running repeated while() statements using mysql_query. However, I have noticed that only the first while() statement works. All the others don't show up unless I repeat the query just above the while().

Ex:

This does NOT work:

$qdata = mysql_query("SELECT * FROM data_ww2 WHERE model = '$model'", $conn);
echo "<table cellspacing=\"0\" cellpadding=\"0\" class=\"data_tab\">
  <tr class=\"data_toprow\">
    <td class=\"data_list\"></td>\n";
	while($data = mysql_fetch_array($qdata)) { echo "<td class=\"data_top\"><img src=\"" . $img . "sflag_" . $data['country'] . ".gif\"></td> \n"; };
echo "  </tr>
  <tr>
    <td class=\"data_list\">Design</td>";
    while($data = mysql_fetch_array($qdata)) { echo "<td class=\"data_bold\">" . $data['design'] . "</td> \n"; };
echo "  </tr>
  <tr>
    <td class=\"data_list\">Name</td>";
    while($data = mysql_fetch_array($qdata)) { echo "<td class=\"data_bold\">" . $data['name'] . "</td> \n"; };

This DOES work

echo "<table cellspacing=\"0\" cellpadding=\"0\" class=\"data_tab\">
  <tr class=\"data_toprow\">
    <td class=\"data_list\"></td>\n";
	$qdata = mysql_query("SELECT * FROM data_ww2 WHERE model = '$model'", $conn);
    while($data = mysql_fetch_array($qdata)) {
		echo "<td class=\"data_top\"><img src=\"" . $img . "sflag_" . $data['country'] . ".gif\"></td> \n"; };
echo "  </tr>
  <tr>
    <td class=\"data_list\">Design</td>";
	$qdata = mysql_query("SELECT * FROM data_ww2 WHERE model = '$model'", $conn);
    while($data = mysql_fetch_array($qdata)) { echo "<td class=\"data_bold\">" . $data['design'] . "</td> \n"; };
echo "  </tr>
  <tr>
    <td class=\"data_list\">Name</td>";
	$qdata = mysql_query("SELECT * FROM data_ww2 WHERE model = '$model'", $conn);
    while($data = mysql_fetch_array($qdata)) { echo "<td class=\"data_bold\">" . $data['name'] . "</td> \n"; };

My question is whether it is always necessary to repeat the mysql_query() above every while() loop even though its the exact same query instead of having every while() loop reference that same query.

Thanks.

leatherback

Yeah,

You are retrieveing data from the results array, untill there are no more records left. I think you can reset it somehow. Not sure how, and this is probably more efficient:

$qdata = mysql_query("SELECT * FROM data_ww2 WHERE model = '$model'", $conn);

echo "<table cellspacing=\"0\" cellpadding=\"0\" class=\"data_tab\">
\n";

while($data = mysql_fetch_array($qdata)) 
  {

   $countrylist.="
<tr class=\"data_toprow\">
  <td class=\"data_list\"></td><td class=\"data_top\"><img src=\"" . $img . "sflag_" . $data['country'] . ".gif\">
  </td>
</tr>";

$designlist .="
<tr>
  <td class=\"data_list\">Design</td>";
  <td class=\"data_bold\">" . $data['design'] . "</td>
</tr>";

$namelist .="<tr>
    <td class=\"data_list\">Name</td>
   <td class=\"data_bold\">" . $data['name'] . "</td>
</tr>";
}

echo $coountrylist;
echo $designlist;
echo $namelist;

Master_Zen

Indeed it would be more efficient, however, it would also repeat the first column (which only has text) in each loop. I only want the second rows to repeat (hence the reason why I use multiple while loops) not the first one.

But thanks for the heads up about the array. I guess I will have to run it everytime I do a while loop. 😉

-MZ

Master_Zen

btw, pardon my n00bness, what's the difference between "=" and ".=" when defining variables?

drew010

= assigns the value on the right to the left value, .= concatenates the value on the right to the left value.

$x = 'hello';
$x .= ' there';
//$x now contains "hello there"
$x = 'drew010';
//$x now only contains drew010. if .= was used it would be hello there drew010

Drakla

If you do need to 'rewind' a query you can use [man]mysql_data_seek[/man]($this->res, 0); Where $this->res is a query result. I shoved it into my db abstraction class, but I can't remember using it on more than one occasion.

leatherback

Hi Master Zen,

You could easily add an if statement in the loop, solving the dsiplay only once problem:

 $qdata = mysql_query("SELECT * FROM data_ww2 WHERE model = '$model'", $conn);

echo "<table cellspacing=\"0\" cellpadding=\"0\" class=\"data_tab\">
\n";
$i = 0;
while($data = mysql_fetch_array($qdata)) 
  {
if($i = 0)
  {
   $countrylist.="
<tr class=\"data_toprow\">
  <td class=\"data_list\"></td><td class=\"data_top\"><img src=\"" . $img . "sflag_" . $data['country'] . ".gif\">
  </td>
</tr>";
   $i++;
}
$designlist .="
<tr>
  <td class=\"data_list\">Design</td>";
  <td class="data_bold\">" . $data['design'] . "</td>
</tr>";

$namelist .="<tr>
    <td class=\"data_list\">Name</td>
   <td class=\"data_bold\">" . $data['name'] . "</td>
</tr>";
}

echo $coountrylist;
echo $designlist;
echo $namelist;

Master_Zen

This is what I want the results to look like. As you can see only the cells which actually fetch data from the mySQL db repeat in the while() loop since the first column is merely the title of each row.

Would the above code work? I am having a bit of trouble digesting it (I refuse to add code unless I understand how it works 100%, otherwise the learning process is moot and that's what I'm trying to get out of this.)