as mysql with 嵌套_Mysql在嵌套循环中选择

I have a table named as 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

John
-	Jane
-	James
Jack
-	Jim
-	Jenny

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

$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

}

}

?>

So the problem rises here.

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

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.

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

解决方案

I have solved the problem:

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:

$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

}

}

?>