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

Answer 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;
}

Answer 2

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.

Answer 3

Thanks to RoBorg I got to the right track, his solution is flawed though, or at least didn't work for me. Here is how I solved it:

public function aggregateData($dataID)
{
    $sql = "SELECT s.scaleID, c.length, c.start, i.itemID, i.recode 
   FROM surveyscales AS s
   LEFT JOIN surveychoices AS c ON s.choiceID = c.choiceID 
   INNER JOIN surveyitems AS i ON s.scaleID = i.scaleID 
   WHERE s.template='FORM_SCALE'
   ORDER BY s.scaleID ASC, i.itemID ASC";

    $db = Zend_Registry::get('db');
    $dataSet = $db->fetchAll($sql);
    $scales = array ();

    foreach ($dataSet as $row)
    {
        $scales[$row['scaleID']][] = $row;    
    }

    foreach ($scales as $scale)
    {

        foreach ($scale as $item)
        {
            //do stuff here
        }
    }
    //save data here in one query
    $this->createRow()->setFromArray($aggregatedValues)->save();
}