PHP/mySQL - how to fetch nested rows into multidimensinal array

Question

Coming from another question of mine where I learnt not to EVER use db queries within loops I consequently have to learn how to fetch all the data in a convenient way before I loop through it.

Let's say I have two tables 'scales' and 'items'. Each item in items belongs to one scale in scales and is linked with a foreign key (scaleID). I want to fetch all that data into an array structure in one query such that the first dimension are all the scales with all the columns and nested within, all items of one scale all columns.

Result would be something like that:

scale 1, scaleParam1, scaleParam2, ...
....item1, itemParam1, itemParam2, ...
....item2, itemParam1, itemParam2, ...
scale 2, scaleParam2, scaleParam2, ...
....item1, itemParam1, itemParam2, ...
....item2, itemParam1, itemParam2, ...

So far I've done mainly left joins for one-to-one relationships. This is a one-to-many and I just can't wrap my mind around it.

Is it a right join, could it also be done with a subquery, how to get the full outer rows into it as well...

later I would like to iterate through it with to nested foreach loops.

Maybe it's just that I have a headache...

Greg · Accepted Answer · 2009-01-21 15:46:33Z

6

The query should look something like this:

SELECT * FROM scales
INNER JOIN items ON scales.id = items.scale_id

If you want to iterate through with nested loops, you'll need to pull this data into an array - hopefully you're not pulling back so much that it'll eat up too much memory.

$scales = array();

while ($row = mysql_fetch_assoc($data))
{
    if (!isset($scales[$row['scale_id']]))
    {
        $row['items'] = array();
        $scales[$row['scale_id']] = $row;
    }

    $scales[$row['scale_id']]['items'][] = $row;
}

Then you can loop through:

foreach ($scales as $scale)
{
    foreach ($scale['items'] as $item)
        ; //... do stuff
}

Note: this is somewhat naive in that $scale and $item will both contain fields from BOTH tables... if that's a problem then you need to change the assignments in the loop above to pull out only the fields you want.

edited Jan 21, 2009 at 15:46

answered Dec 10, 2008 at 19:23

Greg

323k55 gold badges378 silver badges338 bronze badges

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

6 Comments

markus Over a year ago

wow, you have loads of accepted answers, amazing. thanks for this one, could also end up on top.

TravisO Over a year ago

In relation to the question that spawned this one, and to your comment about eating up too much memory, I'd rather have a large array, let's say 1mb of RAM than to run many (dozens?) of queries.

TravisO Over a year ago

Also, good job on using mysql_fetch_assoc, many people do mysql_fetch_array which is inefficient.

markus Over a year ago

In the meantime I've tried it and your solution is not entirely correct, at least in my scenario, the if (!isset($scales[$row['scale_id']])) part flaws the generation of a proper array-structure... See my solution in my own answer.

Greg Over a year ago

Oops, fixed missing ['items']

|

OIS · Accepted Answer · 2008-12-10 19:19:45Z

1

It might be easier to first get all the scales, then all the items.

//first get scales
while ($row = fetchrowfunctionhere()) {
    $scale = $scales->createFromArray($row);
}

//then get items
$lastId = null;
while ($row = fetchrowfunctionhere()) {
    $scaleId = $row['scaleID'];
    if ($lastId != $scaleId) {
        $scale = $scales->getByScaleId($scaleId);
    }
    $item = $items->createFromArray($row);
    $scale->addItem($item);
    $lastId = $scaleId;
}

or everything in one sql

$lastId = null;
while ($row = fetchrowfunctionhere()) {
    $scaleData = array_slice($row, 0, 5, true);
    $itemData = array_slice($row, 5, 5, true);
    $scaleId = $scaleData['scaleID'];
    if ($lastId != $scaleId) {
        $scale = $scales->createFromArray($scaleData);
    }
    $item = $items->createFromArray($itemData);
    $scale->addItem($item);
    $lastId = $scaleId;
}

everything as one happy array

while ($row = fetchrowfunctionhere()) {
    $scaleData = array_slice($row, 0, 5, true);
    $itemData = array_slice($row, 5, 5, true);
    $scaleId = $scaleData['scaleID'];
    if (!isset($scales[$scaleId])) {
        $scales[$scaleId] = $scaleData;
    }
    $itemId = $itemData['itemID'];
    $scales[$scaleId]['items'][$itemId] = $itemData;
}

answered Dec 10, 2008 at 19:19

OIS

10.1k3 gold badges35 silver badges41 bronze badges

2 Comments

markus Over a year ago

I have to admit that I will have to study your solutions since I don't understand exactly what they do.

OIS Over a year ago

The first 2 are objects, which makes it easier. The 3rd is with arrays. You didnt specify mysql in your text (but I see the mysql tag now) so I wrote a generic function name instead of mysql_fetch_assoc.

Collectives™ on Stack Overflow

PHP/mySQL - how to fetch nested rows into multidimensinal array

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related