PHP / mySQL - 如何将嵌套行提取到multidimensinal数组中

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.

假设我有两个表'scale'和'items'。项目中的每个项目都属于一个比例尺,并与外键(scaleID)链接。我想在一个查询中将所有数据提取到数组结构中,这样第一个维度就是包含所有列并嵌套在其中的所有比例,所有项目都是一个比例所有列。

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.

后来我想用嵌套的foreach循环遍历它。

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

也许只是因为我头疼...

2 个解决方案

#1

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.

注意:这有点天真,因为$ scale和$ item都包含来自BOTH表的字段......如果这是一个问题,那么你需要更改上面循环中的赋值,只取出你想要的字段。

#2

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

或者一个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;
}

#1