Mysql Selects in nested loops

Question

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

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

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
    }
}

?>

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.

Your Common Sense · Accepted Answer · 2013-07-25 11:22:22Z

1

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

it is actually not.
A few primary key lookups will never do any harm.

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.

First of all, you have not just mention the error message but read and comprehend it, as well as provide it here, full and uncut.

Next, for this one it is quite easy to guess though. Use store_result()

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

A canonical text which even has been a part of mysql official docs once: Managing Hierarchical Data in MySQL (first result on google, btw)

edited Jul 25, 2013 at 11:22

answered Jul 25, 2013 at 11:12

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zkanoca Over a year ago

I know that page but did not figure out something for my problem.

zkanoca · Accepted Answer · 2013-07-30 08:13:20Z

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:

<?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
        }
    } 
?>

Collectives™ on Stack Overflow

Mysql Selects in nested loops

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related