PHP/mySQL - 如何将嵌套行提取到多维数组中 [英] PHP/mySQL - how to fetch nested rows into multidimensinal array

问题描述

来自 我的另一个问题 在那里我学会了永远不要在循环中使用 db 查询，因此我必须学习如何在循环之前以方便的方式获取所有数据.

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.

假设我有两个表scales"和items".items 中的每个 item 都属于 scales 中的一个 scale，并与一个外键 (scaleID) 相关联.我想在一个查询中将所有数据提取到一个数组结构中，这样第一维是所有列的所有比例并嵌套在其中，一个比例的所有项目都对所有列进行缩放.

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

稍后我想用嵌套的 foreach 循环遍历它.

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

也许只是我头疼...

推荐答案

查询应如下所示:

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

然后你可以循环:

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

注意:这有点幼稚，因为 $scale 和 $item 都将包含来自两个表的字段...如果这是一个问题，那么您需要更改上面循环中的分配以仅提取您想要的字段.

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.

这篇关于PHP/mySQL - 如何将嵌套行提取到多维数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！