while loop with SQL

[a4udi]

I've got a table with about 50 items and am wondering how I can grab them all from the database and spit it out on the page without having to type each one obviously. I just can't quite get it right.

Example... I just want to grab each item from the table and display it:

  $query1 = "select item1 from products";
  $result1 = mysql_query($query1);      
  $row1 = mysql_fetch_array($result1);    
  list($item1) = $row1;

So with 50+ items I need something like...

while ( $counter <= 50;) {
  $query . '$counter'; = "select item from products...
  ....
  $counter ++1;

I obviously don't know how to code this right, but I think the above will give some expert the idea of what I want to do and you can show me the proper syntax so I can just replace the "item + #" with the counter variable?

[Zangeel]

I don't understand, you have 50 items, and want to query them all? If that's the case why not use while to run through all the items like

PHP Code:

$q = mysql_query("SELECT * FROM `products`");

while ($row = mysql_fetch_assoc($q))
{
   echo $row['item'] . '<br />';
} 


That will echo all the items from the product table.

Also, this

  $counter ++1;

Is improper syntax, use $counter++ to increment up

But if you want many queries, use something like

PHP Code:

for ($i = 1; $i = 50; $i++)
{
  $sql[] = "SELECT `item".$i."` FROM `products`";
} 


Thus creating an array of 50 sql queries. So $sql[0] would be SELECT `item1` .... , $sql[1] would be SELECT `item2` ... and so on

[a4udi]

I don't understand, you have 50 items, and want to query them all?

Well they are stored in the database so a user can login and update the price of each item and they may not all be listed on the page in the same order.

So I'd like to be able to grab the price of each item using the loop so all 50 are available and then spit them out on the page later on in various order... like

Table Cost <?php echo $item24; ?>

Chair Cost <?php echo $item27; ?>

etc...

Does that make sense? I just want to have all the values available to print out later on the page.

[SKDevelopment]

If you need to show your items later at the page, you could store them in an array and later show anywhere you wish ...

You could use the code Zangeel has provided, but instead of outputting the entries right away you could store them in an array (I do not know you column names, so I am giving some arbitrary names just to show the idea):

PHP Code:

$q = mysql_query("SELECT * FROM `products`");

$items = array();
$counter = 0;
while ($row = mysql_fetch_assoc($q))
{
   $items['item_name'] = $row['item_name'];
   $items['price'] = $row['price'];

  ++counter;
}
// You could output your array $items entries later at the page 


If items have some field containing item category, I would also reformat the array by categories to make it simpler to work with it. If you show your table `products` structure, I could be more specific and show the idea more clearly.

The table structure could be obtained with the MySQL command

SHOW CREATE TABLE `products`;

Also if you need to move all your resultset to an array (without any formatting), you could do it simpler (also a modification of code provided by Zangeel):

PHP Code:

$q = mysql_query("SELECT * FROM `products`");

while ($row = mysql_fetch_assoc($q))
{
  $items[] = $row;
}
// You could output your array $items entries later at the page
// Some reformatting of the array $items could be possibly useful 


Again: I think it would be possible to be much more specific if your posted your table `products` structure.

Also normally I move such code to a class methods. It simplifies the code reusing if necessary and also makes page code more readable ...