...

View Full Version : while loop with SQL



a4udi
09-06-2009, 01:59 AM
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? :D

Zangeel
09-06-2009, 02:07 AM
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



$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


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
09-06-2009, 03:01 AM
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
09-06-2009, 11:30 AM
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):


$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):


$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 ...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum