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

[rdillman]

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

 

[ddh716]

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:

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

 

[rdillman]

THAT gets me much closer to what i need. Thanks!

 

[Thom_Edwards]

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

 

[rdillman]

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)

 

[jpyc7]

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.

 

[Thom_Edwards]

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.

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.

 

[ddh716]

Yes, always more than one way to skin a cat

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.

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!

 

[Thom_Edwards]

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

OrderID ItemID
1          517
2          709
           428
           329
3          222

Or...

OrderID ItemID
1          517
2          709
2          428
2          329
3          222

Or...

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.

 

[rdillman]

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.

 

[rdillman]

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.

 

[BertyBoy]

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

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>"