PHP和MySQL-有效处理多个一对多关系 [英] PHP and MySQL - efficiently handling multiple one to many relationships

问题描述

我正在寻求有关使用MySQL和PHP检索和显示数据的最佳方法的一些建议.

I am seeking some advice on the best way to retrieve and display my data using MySQL and PHP.

我有3个表，如下所示，都是1到许多关系:

I have 3 tables, all 1 to many relationships as follows:

每个 SCHEDULE 有许多 OVERRIDES ，并且每个替代都有许多 LOCATIONS .我想检索此数据，以便可以将其全部显示在单个PHP页面上，例如列出我的时间表.在每个明细表中都列出了OVERRIDES，并且在每个明细表中都列出了LOCATIONS.

Each SCHEDULE has many OVERRIDES and each override has many LOCATIONS. I would like to retrieve this data so that it can all be displayed on a single PHP page e.g. list out my SCHEDULES. Within each schedule list the OVERRIDES, and within each override list the LOCATIONS.

选项1 -这样做的最好方法是进行3个独立的SQL查询，然后将其写入PHP对象吗?然后，我可以遍历每个数组并检查父数组是否匹配.

Option1 - Is the best way to do this make 3 separate SQL queries and then write these to a PHP object? I could then iterate through each array and check for a match on the parent array.

选项2 -我已经对联接进行了很多思考，但是进行两个正确的联接将使我在所有3个表中的每个入口都返回一行.

Option 2 - I have thought quite a bit about joins however doing two right joins will return me a row for every entrance in all 3 tables.

任何想法和评论将不胜感激.

Any thoughts and comments would be appreciated.

最诚挚的问候，本.

推荐答案

如果您确实想要每条数据，无论您怎么做，都将检索相同数量的行.最好一次查询即可获得所有信息.

If you really want every piece of data, you're going to be retrieving the same number of rows, no matter how you do it. Best to get it all in one query.

SELECT schedule.id, overrides.id, locations.id, locations.name
FROM schedule
JOIN overrides ON overrides.schedule_id = schedule.id
JOIN locations ON locations.override_id = overrides.id
ORDER BY schedule.id, overrides.id, locations.id

通过像这样对结果进行排序，您可以遍历结果集，并在scheduleid更改时移至下一个进度表，而在locationid更改时移至下一个位置.

By ordering the results like this, you can iterate through the result set and move on to the next schedule whenever the scheduleid changes, and the next location when the locationid changes.

如何将这些数据转换为3维数组的可能示例-

a possible example of how to turn this data into a 3-dimensional array -

$last_schedule = 0;
$last_override = 0;
$schedules = array();

while ($row = mysql_fetch_array($query_result))
{
  $schedule_id = $row[0];
  $override_id = $row[1];
  $location_id = $row[2];
  $location_name = $row[3];
  if ($schedule_id != $last_schedule)
  {
    $schedules[$schedule_id] = array();
  }
  if ($override_id != $last_override)
  {
    $schedules[$schedule_id][$override_id] = array();
  }
  $schedules[$schedule_id][$override_id][$location_id] = $location_name;
  $last_schedule = $schedule_id;
  $last_override = $override_id;
}

很原始，我想您的代码看起来会有所不同，但希望它会有所道理.

Quite primitive, I imagine your code will look different, but hopefully it makes some sense.

这篇关于PHP和MySQL-有效处理多个一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！