在MySQL中一对多的关系，选择“子".表信息 [英] One to many relationship in MySQL, Selecting "child" table information

问题描述

我编写了一些PHP脚本，以使从一系列MySQL表中提取信息更加容易，但是仍然存在严重的性能问题.结果，我试图减少查询数量.

I have written some PHP scripts to make pulling information out of a series of MySQL tables easier, however there are severe performance problems. As a result I'm trying to reduce the number of queries.

这些表具有一对多关系: 父表(parent_id，第一个，最后一个电话) 子表(child_id，parent_id，日期，请求，详细信息)

The tables have a one to many relationship: Parent Table (parent_id, first, last, phone) Child Table (child_id, parent_id, date, request, details)

因此，对于父表中的任何条目，子表中可能有多个关联的行.如何在父表中选择一行并使用parent_id(主键)从子表中拉出所有关联的行?

So, for any entry in the parent table there may be multiple associated rows in the child table. How, can I select a row in the parent table and use the parent_id (primary key) to pull all the associated rows from the child table?

SELECT * FROM `Parent Table` WHERE `parent_id` = 5 

...然后...

SELECT * FROM `Child Table` WHERE `parent_id` = '5'

然后我想将结果放入一个关联数组中，然后将json_encode作为JSON返回.

I want to then take the result and put it into an associative array, and json_encode to return as JSON.

为澄清起见，我已经进行了这项工作，但是它对所选的每一行都执行了一个附加查询，因此它代替了一个查询而对100行进行了101个查询.

To clarify, I already have this working, but it's doing an additional query for each row selected, so instead of one query it is doing 101 for 100 rows.

非常感谢您的帮助.

更新:建议使用联接，但是会出现新问题

UPDATE: It has been suggested to use Joins, however a new problem arises

样本数据: 父表(1，"albert"，"smith"，"12345") 子表(1、2010年10月5日，测试"，等")，(2月1日，2010年10月6日，再次"，例如eg")

Sample data: Parent table (1, 'albert', 'smith', '12345') Child table (1, 1, 2010-10-5, 'test', 'etc etc'), (2, 1, 2010-10-6, 'again', 'eg eg')

进行联接会导致两行

1，阿尔伯特，史密斯，12345、1、1、2010-10-5，测试等

1, albert, smith, 12345, 1, 1, 2010-10-5, test, etc etc

1，albert，smith，12345，2，1，2010-10-6，例如

1, albert, smith, 12345, 2, 1, 2010-10-6, again, eg eg

因此，我有两行重复了父表信息.要使用联接，我需要某种方法来清理它，并将其置于分层形式.

So, I have two rows with the parent table information duplicated. To use joins I need some way of cleaning this up, and putting it into a hierarchical form.

结果应该是 {parent_id:1, first:albert, last:smith, phone:12345, child_table:[{child_id:1, date:2010-10-5, request:test, details:'etc etc'},{child_id:2, date:2010-10-6, request:again, details:'eg eg'}]}

The result should be {parent_id:1, first:albert, last:smith, phone:12345, child_table:[{child_id:1, date:2010-10-5, request:test, details:'etc etc'},{child_id:2, date:2010-10-6, request:again, details:'eg eg'}]}

解决方案:对我来说，答案是使用联接，并编写一个函数将返回的行转换为关联数组

SOLUTION: So, the answer for me is to use joins, and write a function to convert the returned rows into an associative array

mysqlResult 是来自mysql_query调用的关联数组， parent_key 是父表的主键名称， child_key 是子表的主键名称， child_table 是子表的名称， child_fields 是子表中所有字段名称的关联数组

mysqlResult is an associative array from a mysql_query call, parent_key is the name of the primary key of the parent table, child_key is the name of the primary key of the child table, child_table is the name of the child table, child_fields is an associative array of the names of all the fields in the child table

   function cleanJoin($mysqlResult, $parent_key, $child_key, $child_table, $child_fields)
    {
    $last_parent = 0;
    $last_child = 0;
    $ch_ctr = 0;

    for ($i = 0; $i < count($mysqlResult); $i++)
    {
        if ($mysqlResult[$i][$child_key] != $last_child)
        {
            echo "new child!";
            $pr_ctr = count($answer[$i]);
            foreach ($child_fields as $field => $type)
            {
               $answer[$pr_ctr][$child_table][$ch_ctr][$field] = $mysqlResult[$i][$field];
               unset($mysqlResult[$field]);
            }
            $ch_ctr++;
        }
        if ($mysqlResult[$i][$parent_key] != $last_parent)
        {
            foreach($mysqlResult[$i] as $field => $value)
            {
                $answer[$i][$field] = $value;
            }
        }
        $last_parent = $mysqlResult[$i][$parent_key];
        $last_child = $mysqlResult[$i][$child_key];
    }

    return $answer;
}

推荐答案

也许我读错了问题，但是您看过联接吗?

maybe i am reading the question wrong, but have you looked at joins?

SELECT * FROM `Parent Table` 
join `Child Table` ON `Child Table`.`Parent ID`=`Parent Table`.`ID`
WHERE `parent_id` = 5 

关联数据的assoc数组示例

example of assoc array of consilidated data

 $res = mysql_query($sql);

 $output = array();

 while ($row = mysql_fetch_assoc($res)) {

    $parent_name = $row['parent_name'];  
    $child_name = $row['child_name'];

    $output[$parent_name][] = $child_name; 
}

var_dump($output);

这篇关于在MySQL中一对多的关系，选择“子".表信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！