Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    189
    Thanks
    6
    Thanked 0 Times in 0 Posts

    while loop with SQL

    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:

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

    Code:
    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?

  • #2
    Regular Coder Zangeel's Avatar
    Join Date
    Oct 2007
    Location
    public_html/
    Posts
    638
    Thanks
    17
    Thanked 79 Times in 79 Posts
    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
    Code:
      $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
    Last edited by Zangeel; 09-06-2009 at 01:10 AM.
    PHP Code:
    $aString is_string((string)array()) ? true false// true :D 
    [/CENTER]

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    189
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    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
    Code:
    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 ...
    Last edited by SKDevelopment; 09-06-2009 at 10:34 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •