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

问题描述

来自

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".项目中的每个项目都属于一个比例尺，并与外键(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.

也许只是我头疼...

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

推荐答案

查询应如下所示:

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屋！