How can i build a table with inner loop and only one query call.

Discussion in 'Web Design and Development' started by rdillman, Sep 23, 2010.

  1. rdillman macrumors newbie

    Joined:
    Sep 23, 2010
    #1
    Hello all, Long time reader, first time poster.

    I'm fairly new to PHP coming from CFML and I'd like to do something that seems to me very basic for web apps but as yet have not found any way of doing it.

    I need to de-normalize a stack of tables and pull both orders and the items into one query.
    Resulting in something like the following. Obviously there is a lot more but this gives the general idea.
    Order_ID Item_ID
    208 23935
    208 23950
    208 23957
    214 24148
    214 24156

    My output should have one row per order with an inner loop over the items in that order. Something like this.

    <table>
    <?php
    //outer loop
    echo '<tr>';
    echo '<td>'.$Order_ID.'</td>';
    echo '<td>';
    //inner loop
    echo $Item_ID.'<br />';
    //end inner loop
    echo '<br /></td>';
    echo '</tr>';
    //end outer loop
    ?>
    </table>

    I hope this isn't too terribly complicated. How would someone go about this?
    Apache 2.0 php 5.2 mysql 5.1.44
     
  2. ddh716 macrumors newbie

    Joined:
    Nov 18, 2009
    #2
    How about using an array?

    You could take the results of your query and put them in an array, then walk through the array (which might be easier than walking through the query results directly, but a few more lines of code). Something like:

    PHP:
    $orders = array();    // an array keyed on order id

    //run the query - then walk through the results - something like:
    while($row mysql_fetch_assoc($results))
    {
        if(!isset(
    $orders[$row["orderId"]))    // have we seen this order before?
        
    {
            
    $orders[$row["orderId"]] = array();    // add the orderId to the array
        
    }
        
    $orders[$row["orderId"]][] = $row["itemId"];  // add this item to the array
    }

    // now walk through the array and build the table
    print("<table>");
    foreach(
    $orders AS $thisOrderId => $items)
    {
        print(
    "<tr>");
        print(
    "<td>$thisOrderId</td>");
        print(
    "<td>");
        print(
    implode("<br/>"$items));
        print(
    "</td>");
        print(
    "</tr>");
    }
    print(
    "</table>");
    Good luck!
     
  3. rdillman thread starter macrumors newbie

    Joined:
    Sep 23, 2010
    #3
    THAT gets me much closer to what i need. Thanks!
     
  4. Thom_Edwards macrumors regular

    Joined:
    Apr 11, 2003
    #4
    ddh's response is correct, but I would handle it a bit differently. Always more than one way to skin a cat, right?

    * Build a quick little function that takes two arguments, namely orderID and itemID. This function simply echoes/prints a <tr> with its <td>s nested in it.
    * Use ddh's while(), but instead of adding it to an array, call the function sending row["Order_ID"] and row["Item_ID"]. Maybe something like BuildTableRow(row["Order_ID"], row["Item_ID"])

    So I guess this is similar to ddh's answer, but avoids a separate foreach() and building out arrays.

    If you wanted to get real snazzy, build a class with a method that takes a database row and is smart enough to parse it out. (HINT: don't use mysql_fetch_assoc; use indicies.)That way you can reuses that class every time you need to build a table out of a db query. Maybe this class has StartTable() that takes an array of column names, and things like that. I'll leave that as an exercise for the reader!

    Good luck with php. I'm at an all ASP.Net shop now, and I really do miss php. The .Net stuff does a lot for you, but it does a lot of stuff for you...

    -Thom
     
  5. rdillman thread starter macrumors newbie

    Joined:
    Sep 23, 2010
    #5
    Thanks Thom, maybe I have a LOT more reading to do before i get that deep? I'm not exactly sure how this is building the array (yet) but its definitely a step in the right direction.

    I have to figure out how to add the rest of the data into this. there are only 10 more fields but $items was the real problem child.

    I'm basically re-writing an existing page, but the current setup just did a separate query to the items nested inside a while loop (/facepalm)
     
  6. jpyc7 macrumors 6502

    Joined:
    Mar 8, 2009
    Location:
    Denver, CO
    #6
    One more option that depends on non-ANSI sql is to use GROUP_CONCAT from mysql. It can give you a column with the item_id in a single string for each order_id.

    SELECT order_id, GROUP_CONCAT(item_id SEPARATOR ',') FROM table GROUP BY order_id;

    You could then split the string on your SEPARATOR for the inner loop.
     
  7. Thom_Edwards macrumors regular

    Joined:
    Apr 11, 2003
    #7
    Well, in my example you're not building an array. That is where I was going with this. A returned database row *is* an array. Notice how you reference the fields is just like any other array. See how the syntax x[1] is very similar to row["fieldName"]? I don't see why you need to build an array from an array. Just access the row array (send its values to the function) from within the while loop.

    Now if you have another query to run and its result is added to the first, then you would need to build out an array. But I'm curious as to why you wouldn't write one query that uses a JOIN so you can make just one trip to the database.
     
  8. ddh716 macrumors newbie

    Joined:
    Nov 18, 2009
    #8
    Yes, always more than one way to skin a cat

    Yes, of course a database row is an array. The reason for arranging the data is to avoid the "is this orderId the same as the previous orderId" logic. Both methods work equally well - it's just a question of what is easiest to implement, and what is easiest to support in the future (once the original thought process has been lost).

    As you point out, there are several ways to skin a cat. The best way is whatever the developer is comfortable with.

    I forgot about "GROUP_CONCAT". Great add, jpyc7! That would be an excellent way to proceed as well, since only one row per orderId is returned. Rdillman: I suspect you're past this point by now, but I (or probably any of the other respondants) would be happy to provide a code sample using "GROUP_CONCAT", if you want it.

    Good luck and have fun!
     
  9. Thom_Edwards macrumors regular

    Joined:
    Apr 11, 2003
    #9
    Ah! I see that I might have interpreted the initial description incorrectly. When ddh mentioned the repeating Order ID, I read the original description again and saw where I may be confused. However, the way it is described and the way the output is shown in the original post are two different things it seems. Just to clarify, which of the following should the output <table> look like...

    Code:
    OrderID ItemID
    1          517
    2          709
               428
               329
    3          222
    
    Or...
    Code:
    OrderID ItemID
    1          517
    2          709
    2          428
    2          329
    3          222
    
    Or...
    Code:
    OrderID ItemID
    1          517
    2          709     428     329
    3          222
    
    Also, are you using two queries or one, and how does this denormalize the tables? I'm a little confused when you say you are trying to denormalize tables with one query. Denormalization is generally accomplished with a database schema change. Now you can certainly make your returned dataset *look* like it came from a denormalized table, which is exactly what you do when you use a JOIN in your SQL. (I'm not sure where you are in terms of SQL knowledge, etc., so please excuse me if this seems elementary or advanced.)

    I'm not trying to start a debate or anything. I just like offering help and having discussions about different ways to do things. I, too, get to learn by teaching and by listening to others' ideas.
     
  10. rdillman thread starter macrumors newbie

    Joined:
    Sep 23, 2010
    #10
    OK here is my simplified query (full query is pulling back 22 fields..)
    SELECT
    o.id,
    c.name,
    c.address,
    i.item_id,
    i.product_name,
    FROM orders o
    INNER JOIN customer c ON c.id = o.ship_id
    INNER JOIN items i ON i.order_id = o.id
    ORDER BY
    o.id


    and output would render like:
    <table>
    <tr>
    <td>$id</td>
    <td>$first_name $last_name</td>
    <td>
    <span class='$item_id'>$product_name</span>
    <span class='$item_id'>$product_name</span>
    <span class='$item_id'>$product_name</span>
    <span class='$item_id'>$product_name</span>
    </td>
    </tr>
    </table>

    customers can have multiple orders and multiple items in each order

    I'm OK displaying each order as its own row but the items in those orders have to show all inside the same td.

    I'm thinking if I can some how stuff the whole thing into a multidimensional array it would just be a for loop inside the wile loop, just not sure how to get from A to B

    Again thanks for all the help guys.
     
  11. rdillman thread starter macrumors newbie

    Joined:
    Sep 23, 2010
    #11
    Sorry I guess i didn't actually address everything you asked Thom...
    Been using MS SQL for years and always referred to this kind of select statement as de-normalizing. I'm only using one query. and the item ID's and names should all render inside the same TD each order rendering on its own TR

    Maybe I'm making this harder than it really needs to be... but I'll get there eventually.
     
  12. BertyBoy macrumors 6502

    Joined:
    Feb 1, 2009
    #12
    Skinning the cat another way ....

    parse the rows one at a time, if the $order_id changes, finish off the previous row (if there was one) and start a new row. Remembering to finish the last row at the end if you found any at all during the search.

    In psudo-code ...

    Code:
    if order_id != prev_order_id then
      if orev_order_id != 0
        print "</tr>\n"
      endif
      print "<tr><td>order_id</td>"
      prev_order_id = order_id
    endif
    print "<td>item_id</td>"
     

Share This Page