Nested MySQL query - getting Array as output

Question

I am starting with the answer on this page PHP/mySQL - how to fetch nested rows into multidimensinal array because I am working with the same kind of relationship between tables. My code now looks like this:

$query=sprintf("SELECT * FROM product INNER JOIN production ON product.ID = production.product_ID");
    $data = mysql_query($query);
    $products = array();

while ($row = mysql_fetch_assoc($data))
{
    if (!isset($products[$row['product_ID']]))
    {
        $row['production'] = array();
        $products[$row['product_ID']] = $row;
    }

    $products[$row['product_ID']]['production'][] = $row;
}

foreach ($products as $product)
{
    foreach ($product['production'] as $item)
        //... do stuff
}

The query is solid - I have tried this directly on the database. My problem is that I can't work out the final bit where it says //... do stuff No matter what combination of variables etc I stick in there I get ArrayArrayArray (etc). What I want is to get a table of output which I can work with. I hope this is enough information, this is my first question on StackOverflow. Thank you.

When I do var_dump($item) I get a long display which looks like this:

array(9) { ["ID"]=> string(1) "2" ["product_name"]=> string(10) "Drums 12kg" ["order"]=> string(2) "20" ["hidden"]=> string(1) "0" ["id"]=> string(1) "4" ["product_ID"]=> string(1) "2" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(3) "130" ["production"]=> array(0) { } } array(8) { ["ID"]=> string(1) "2" ["product_name"]=> string(10) "Drums 12kg" ["order"]=> string(2) "20" ["hidden"]=> string(1) "0" ["id"]=> string(2) "21" ["product_ID"]=> string(1) "2" ["date"]=> string(10) "2012-07-13" ["quantity"]=> string(3) "705" } array(8) { ["ID"]=> string(1) "2" ["product_name"]=> string(10) "Drums 12kg" ["order"]=> string(2) "20" ["hidden"]=> string(1) "0" ["id"]=> string(2) "23" ["product_ID"]=> string(1) "2" ["date"]=> string(10) "2012-07-13" ["quantity"]=> string(2) "23" } array(8) { ["ID"]=> string(1) "2" ["product_name"]=> string(10) "Drums 12kg" ["order"]=> string(2) "20" ["hidden"]=> string(1) "0" ["id"]=> string(2) "26" ["product_ID"]=> string(1) "2" ["date"]=> string(10) "2012-07-13" ["quantity"]=> string(2) "23" } array(8) { ["ID"]=> string(1) "2" ["product_name"]=> string(10) "Drums 12kg" ["order"]=> string(2) "20" ["hidden"]=> string(1) "0" ["id"]=> string(2) "28" ["product_ID"]=> string(1) "2" ["date"]=> string(10) "2012-07-19" ["quantity"]=> string(2) "12" } array(9) { ["ID"]=> string(1) "3" ["product_name"]=> string(12) "Nibbles 12kg" ["order"]=> string(2) "65" ["hidden"]=> string(1) "0" ["id"]=> string(1) "5" ["product_ID"]=> string(1) "3" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(2) "10" ["production"]=> array(0) { } } array(8) { ["ID"]=> string(1) "3" ["product_name"]=> string(12) "Nibbles 12kg" ["order"]=> string(2) "65" ["hidden"]=> string(1) "0" ["id"]=> string(2) "10" ["product_ID"]=> string(1) "3" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(2) "10" } array(8) { ["ID"]=> string(1) "3" ["product_name"]=> string(12) "Nibbles 12kg" ["order"]=> string(2) "65" ["hidden"]=> string(1) "0" ["id"]=> string(2) "14" ["product_ID"]=> string(1) "3" ["date"]=> string(10) "2012-07-12" ["quantity"]=> string(3) "140" } array(8) { ["ID"]=> string(1) "3" ["product_name"]=> string(12) "Nibbles 12kg" ["order"]=> string(2) "65" ["hidden"]=> string(1) "0" ["id"]=> string(2) "16" ["product_ID"]=> string(1) "3" ["date"]=> string(10) "2012-07-12" ["quantity"]=> string(3) "220" } array(8) { ["ID"]=> string(1) "3" ["product_name"]=> string(12) "Nibbles 12kg" ["order"]=> string(2) "65" ["hidden"]=> string(1) "0" ["id"]=> string(2) "17" ["product_ID"]=> string(1) "3" ["date"]=> string(10) "2012-07-04" ["quantity"]=> string(3) "120" } array(9) { ["ID"]=> string(1) "4" ["product_name"]=> string(16) "Breast meat 12kg" ["order"]=> string(2) "40" ["hidden"]=> string(1) "0" ["id"]=> string(1) "6" ["product_ID"]=> string(1) "4" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(2) "20" ["production"]=> array(0) { } } array(8) { ["ID"]=> string(1) "4" ["product_name"]=> string(16) "Breast meat 12kg" ["order"]=> string(2) "40" ["hidden"]=> string(1) "0" ["id"]=> string(2) "11" ["product_ID"]=> string(1) "4" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(2) "20" } array(8) { ["ID"]=> string(1) "4" ["product_name"]=> string(16) "Breast meat 12kg" ["order"]=> string(2) "40" ["hidden"]=> string(1) "0" ["id"]=> string(2) "13" ["product_ID"]=> string(1) "4" ["date"]=> string(10) "2012-10-10" ["quantity"]=> string(2) "75" } array(9) { ["ID"]=> string(1) "5" ["product_name"]=> string(11) "Value Birds" ["order"]=> string(2) "50" ["hidden"]=> string(1) "0" ["id"]=> string(1) "7" ["product_ID"]=> string(1) "5" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(2) "40" ["production"]=> array(0) { } } array(8) { ["ID"]=> string(1) "5" ["product_name"]=> string(11) "Value Birds" ["order"]=> string(2) "50" ["hidden"]=> string(1) "0" ["id"]=> string(2) "12" ["product_ID"]=> string(1) "5" ["date"]=> string(10) "2012-09-07" ["quantity"]=> string(2) "40" } array(9) { ["ID"]=> string(1) "1" ["product_name"]=> string(11) "Thighs 12kg" ["order"]=> string(3) "105" ["hidden"]=> string(1) "1" ["id"]=> string(1) "8" ["product_ID"]=> string(1) "1" ["date"]=> string(10) "2012-10-01" ["quantity"]=> string(3) "500" ["production"]=> array(0) { } } array(9) { ["ID"]=> string(1) "7" ["product_name"]=> string(19) "Whole birds size 13" ["order"]=> string(2) "70" ["hidden"]=> string(1) "1" ["id"]=> string(2) "29" ["product_ID"]=> string(1) "7" ["date"]=> string(10) "2012-07-13" ["quantity"]=> string(3) "500" ["production"]=> array(0) { } } array(8) { ["ID"]=> string(1) "7" ["product_name"]=> string(19) "Whole birds size 13" ["order"]=> string(2) "70" ["hidden"]=> string(1) "1" ["id"]=> string(2) "30" ["product_ID"]=> string(1) "7" ["date"]=> string(10) "2012-07-13" ["quantity"]=> string(3) "500" } array(8) { ["ID"]=> string(1) "7" ["product_name"]=> string(19) "Whole birds size 13" ["order"]=> string(2) "70" ["hidden"]=> string(1) "1" ["id"]=> string(2) "31" ["product_ID"]=> string(1) "7" ["date"]=> string(10) "2012-07-19" ["quantity"]=> string(3) "500" } array(8) { ["ID"]=> string(1) "7" ["product_name"]=> string(19) "Whole birds size 13" ["order"]=> string(2) "70" ["hidden"]=> string(1) "1" ["id"]=> string(2) "32" ["product_ID"]=> string(1) "7" ["date"]=> string(10) "2012-07-13" ["quantity"]=> string(3) "500" }

What do you get when you do var_dump($item)? If it's an array, you most likely need to specify either an index ($item[0]) or an array key ($item['key']). When in doubt, dump your data and inspect. — Matthew Blancarte
– Matthew Blancarte, Commented Jul 12, 2012 at 3:14

John Moses · Accepted Answer · 2012-07-12 15:50:40Z

0

You are going to construct your table's html inside your last two foreach loops. I would recommend naming your columns better as the text that identifies your array keys in item is confusing (you have three ID fields).

print "<table><thead><tr><th>ID</th><th>Product</th><th>Order</th><th>Hidden</th><th>ID</th><th>Product ID</th><th>date</th><th>Quantity</th></tr></thead><tbody>";
foreach ($products as $product)
{
    foreach ($product['production'] as $item)
    {
        print "<tr>";        
        print "<td>$item['ID']</td>";
        print "<td>$item['product_name']</td>";
        print "<td>$item['order']</td>";
        print "<td>$item['hidden']</td>";
        print "<td>$item['id']</td>";
        print "<td>$item['product_id']</td>";
        print "<td>$item['date']</td>";
        print "<td>$item['quantity']</td>";
        print "</tr>";
    }
}
print "</tbody></table>";

edited Jul 12, 2012 at 15:50

answered Jul 12, 2012 at 4:54

John Moses

1,2381 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Fyles Over a year ago

I've updated your answer, thank you very much for your help. This is a screengrab of what I see now: would you have expected this? [link] dl.dropbox.com/u/35912963/stackoverflow.jpg

John Moses Over a year ago

I've updated my answer to include the tr tag inside the inner loop

Dan Fyles Over a year ago

John that is very helpful to me and I am most grateful to you for the time you have taken to answer my question. Thank you.

Collectives™ on Stack Overflow

Nested MySQL query - getting Array as output

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related