problems reusing MySQL query results

[Earl Parker II]

Please refer to the following code:

$results = mysql_query("SELECT * FROM price_list WHERE page='$txtPage'");
$num_rows = mysql_num_rows($results);

for($n = 0;$n < $num_rows;$n++)
{
list($part_number,$page,$section,$price,$discount) = mysql_fetch_row($results);
print("$part_number<br>");
}
for($n = 0;$n < $num_rows;$n++)
{
list($part_number,$page,$section,$price,$discount) = mysql_fetch_row($results);
print("$price<br>");
}

The problem is that after $part_number has been printed $price won't print. If I comment out the first loop then $price will print. It would seem that a pointer needs to be reset but I'm not sure of how to do it- any suggestions?

[Spookster]

I think you are making this much more complex than it needs to be. One while loop should suffice for this and just fetch each record using the loop until it runs out of records which then the loop will stop



$results = mysql_query("SELECT * FROM price_list WHERE page='$txtPage'");

while($row = mysql_fetch_array($results)){
echo "Part number: " . $row['part_number'] . "<br />\n";
echo "Price: " . $row['price'] . "<br />";
echo "<br />";
}



I was assuming that your column names in the database table were part_number and price.

[Earl Parker II]

Thanks for replying.

You're exactly right but here's the catch- and I should have mentioned this before. I'm having to print/echo the part number and price in two different table cells (yes, CSS positioning would be better, long story).

In one cell is a function call to one for loop to print the part numbers, while in a separate cell is another function call to another for loop to print the prices. The problem is that the second function call doesn't produce any results. It seems as though you get one opportunity to use the results of the query, which is what I'd like to find a way around.

[raf]

why not something like:

$results = mysql_query("SELECT_*_FROM_price_list_WHERE_page='$txtPage'");

if (mysql_num_rows($result)==0){
echo "<br/>No rows found"; // check recordset isn't empty
echo "<br/><br/><a href=\"java script:history.back();\">Back</a>" ;
}

else {
echo "<table ...>"; //open table
while($row = mysql_fetch_array($results)){ //loop through recordset
echo "<tr>"; // For each record, a new row is started
echo "<td>Part number: " . $row['part_number'] . "</td>"; // each value comes in a cell
echo "<td>Price: " . $row['price'] . "</td>";
echo "</tr>";
}
echo "</table>";
}
mysql_free_result($results); // free memoryspace after you used the info from the recordset

[ConfusedOfLife]

Even though I totally agree with Spookster and raf, but then if you really wana continue with your structure and wana reset your mysql result pointer (even though I see no need to do that! The code seems to be ok), then you can use:


$bool = mysql_data_seek($results, 0);
if ( !$bool )
die("Couldn't do that!");

[Earl Parker II]

First, thanks to everyone who replied- you've been a bigger help than you know. Second, the problem is solved (thank you, ConfusedOfLife). Including mysql_data_seek inside the second function, as below, did the trick perfectly.

function return_price()
{
mysql_data_seek($results, 0);

for($n = 0;$n < $num_rows;$n++)
{
list($part_number,$page,$section,$price,$discount)
= mysql_fetch_row($results);
print("$price<br>");
}

I had no problem with creating a new table row in each in each instance of the loop (thank you, raf, I may be able to use your suggestion in the future) but decided to try the simpler solution first.

Interestingly, the following code also did not work:

$results = mysql_query("SELECT * FROM price_list WHERE page='$txtPage'");
$num_rows = mysql_num_rows($results);
$a_results = $results;
$a_num_rows = $num_rows;
$b_results = $results;
$b_num_rows = $num_rows;

function1()
{
use a
}
function2()
{
use b
}

The idea, of course, was to use $a_results, $a_num_rows in the first funtion and $b_results, $b_num_rows in the second function. Still ran into the same problem.

Anyway, thanks again.

[Ökii]

why not build an array within the while() loop of the query and then iterate that array where-ever needed?

while($row = mysql_fetch_array($result))
{
$data_array[] = array($row['part_number'],$row['price']);
}

then

foreach($data_array AS $indx=>$vals)
{
echo 'number = ' .$vals[0]. '<br />';
}
foreach($data_array AS $indx=>$vals)
{
echo 'price = ' .$vals[1]. '<br />';
}