better way of retrieving results that extract?

yahyac

Being a newbie, I try to follow reputable tutorials and move forward from there. Hudzilla, has a nice tutorial on php, and one specific part, talks about how one can format (basic formatting) of mysql results into a table. I say basic, because each row simply represents one row from the database. They use the extract button to extract one row from the $results at a time. And then, you can apparently print the values from the extract function.

I used this concept on a product.php page, and here's what I came up against. I have several price breaks and their respective quantites. And these are all different for each product (including the price breaks). So basically, I need to iterate through the results, and put the price breaks into columns, then start a new row for the matching prices, and then AGAIN iterate through the results, and put the prices under their matching quantity.

However, because I'm using "extract", as shown below, I am having to re-issue the query to the database. Is there a smarter/better way to do this?

<td width="20%" bgcolor="#181EA5">Quantity</td>
<?php
     $result3 = mysql_query("SELECT quantity
                                             FROM products_pricing
                                             WHERE item_num = '$prodID'
                                             ORDER BY quantity", $conn);
     while($myrow2 = mysql_fetch_assoc($result3)) {
          extract($myrow2);
          print "<TD valign=\"middle\" align=\"right\" bgcolor=\"#181EA\">$quantity</TD>";
     }
?>
</tr>
<tr class="content10b">
<td width="20%">Price</td>
<?php
     $result3 = mysql_query("SELECT quantity, price
                                             FROM products_pricing
                                             WHERE item_num = '$prodID'
                                             ORDER BY quantity", $conn);
     while($myrow2 = mysql_fetch_assoc($result3)) {
          extract($myrow2);
          print "<TD valign=\"middle\" align=\"right\">$$price</TD>";
     }
?>

Thanks.

bradgrafelman

Uh.. I think you're confused about what [man]extract/man does - it has nothing to do with MySQL.

And yeah.. instead of issueing another query just to get the price, all you'd need to do is add the price field into the column list on the first SELECT query.

Also, if you read about [man]extract/man, you'll see that it's in no way necessary when working with SQL results. In fact, I never use it. $myrow2 is an associative array, with column names as indeces (meaning you can just use $row['quantity'] to get the quantity field of the current row.

yahyac

The reason I cannot get the price at the same time as the quantity is because they are in two different rows of html. The quantity row is shaded with white text, and the price row is different. They are two rows of data for design purposes.

Sure, I can simply bring in the quantity, put a <br> and then bring in the price, but that is not how my design is. So as they are different rows, I need to iterate through the $results twice. The only question is was I doing it wrong by having to requery the database just to get the same result. Perhaps I should, after the first query, just copy the $result into another $result2. Thus, I would have two copies.?.

No doubt, there is probably a smarter way of doing this which is why I asked. I just wanted to explain the reason for not being able to retrieve the Price at the same moment of its respective quantity.

bradgrafelman

yahyac wrote:
No doubt, there is probably a smarter way of doing this which is why I asked

Yup - retrieve both at the same time and store them in arrays.

Then, you'd use a foreach() on each array to print out their contents. That way you only use 1 query.

I have to leave for work, otherwise I'd show you some sample code... sorry!

NogDog

You can use mysql_data_seek() to "rewind" the result set back to the first row:

mysql_data_seek($result, 0);

yahyac

NogDog wrote:
You can use mysql_data_seek() to "rewind" the result set back to the first row

That would be nice. In which case, could you please correct my understanding of the following line of code that I am using:

$myrow2 = mysql_fetch_assoc($result3))

I thought that mysql_fetch_assoc or mysql_fetch_array actually removes the first row from the $result. In which case one could not "rewind". However, I'm probably wrong and maybe no result is actually removed. Rather, a pointer is just advanced to the next row.?.

Thanks.