Mysql在嵌套循环中选择 [英] Mysql Selects in nested loops

问题描述

我有一个名为People的表:

id | name |parent_id
---+------+---------
1  | John | 0
2  | Jane | 1
3  | James| 1
4  | Jack | 0
5  | Jim  | 4
6  | Jenny| 4

因此，约翰是简和詹姆斯的父母.树就这样.

So john is parent of Jane and James. Tree goes like this.

John
-Jane
-James
Jack
-Jim
-Jenny

我想制作一张看起来像这样的桌子

I want to make a table that seems like

<table border="1">
    <tr>
        <th colspan="2">John</th>
    </tr>
    <tr>
        <td>-</td><td>Jane</td>
    </tr>
    <tr>
        <td>-</td><td>James</td>
    </tr>
    <tr>
        <th colspan="2">Jack</th>
    </tr>
    <tr>
        <td>-</td><td>Jim</td>
    </tr>
    <tr>
        <td>-</td><td>Jenny</td>
    </tr>
<table>

为此，我使用两个sql查询.这是伪代码:

To do this, I use two sql queries. Here is the pseudo-code:

<?php

$firstQuery = 'SELECT id, name FROM People WHERE parent_id = 0';

start creating the table

while ($rowP = $result_parent->fetch())
{
    //get the child rows using the second query in the loop:

    $secondQuery = 'SELECT id, name FROM People WHERE parent_id = $rowP["id"]';

    start creating table rows for child items.

    while ($rowC = $result_child->fetch())
    {
        add names into the table belonging the current parent person
    }
}

?>

所以问题在这里出现.

1. 就性能而言，这是非常糟糕的方法.正确的方法是什么.

1. This is very bad approach in the performance asppect. What is the correct way.

当我尝试使用父母的ID作为孩子查询的参数时，出现关于bind_param()函数的错误.

When I try to use the parent person's id as a parameter for the child people query, I get error about bind_param() function.

只能使用JOIN操作完成一个SQL查询.但是我不知道该怎么办.

This can be done only one SQL query with JOIN operation. But I don't know how to do.

推荐答案

我已经解决了这个问题:

I have solved the problem:

因此，基本思想是在while循环中使用fetch()方法.相反，我在循环之前获得了所有结果集，然后在foreach循环中使用它的新实例:

So the basic idea is about using fetch() method in a while loop. Instead, I get the all resultset before the loop then use a new instance of it in a foreach loop:

<?php   
    $firstQuery = 'SELECT id, name FROM People WHERE parent_id = 0';

    $resultP->setFetchMode(PDO::FETCH_ASSOC);

    $resultP = $db->exec($firstQuery);

    $rowP = $resultP->fetchAll();

    $foreach($rowP as $rp)
    {
        //get the child rows using the second query in the loop:

        $secondQuery = 'SELECT id, name FROM People WHERE parent_id = :p1';

        //start creating table rows for child items.

        $resultP = $db->prepare($secondQuery);

        $resultP->bindValue(':p1', $rp["id"], PDO::PARAM_INT);

        $resultP->setFetchMode(PDO::FETCH_ASSOC);

        $resultP->exeecute();

        $rowC = $resultC->fetchAll();

        $foreach($rowC as $rc)
        {
            //add names into the table belonging the current parent person
        }
    } 
?>

这篇关于Mysql在嵌套循环中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！