PHP/MySQL nested fetch?

Question

I am in the process of making a quick PHP based forum, and each post in a forum will appear under its "parent" post, but slightly more indented.

To get all the posts in that order, I have the following function:

    private function getData($pid, $offset)
    {
            $sql = 'SELECT id, subject, date 
                    FROM post 
                    WHERE forum_id = ? AND parent_id = ?';
            $sth = $this->db->prepare($sql);
            $sth->bind_param("ii", $this->id, $pid);
            $sth->bind_result($id, $subject, $date);
            $sth->execute();

            $data = array();

            while ( $sth->fetch() )
            {
                    $row['id'] = $id;
                    $row['subject'] = $subject;
                    $row['date'] = $date;
                    $row['offset'] = $offset;

                    //Add this 'parent' post to the data array
                    $data[] = $row;

                    //Before moving on to next post, get all its children
                    $data[] = $this->getData($id, $offset+1);
            }

            $sth->close();

            return $data;
    }

This isn't working because I am executing another query before closing and fetching all the data from my current statement handler.

Is there a way to maybe separate the queries so they don't conflict with each other? Or any other way to by-pass this? Or will I simply have to restructure how I get my data?

When you saythat this isn't working because you're not closing one query before starting a new one, can you be more specific? What results are you seeing, or what error message? — Gustav Bertram
– Gustav Bertram, Commented Dec 5, 2011 at 4:48
I had a warning saying: "All data must be fetched before a new statement prepare takes place", which caused my prepare() to return false and give me a non-object error. — Benjamin
– Benjamin, Commented Dec 5, 2011 at 5:05

goat · Accepted Answer · 2011-12-05 04:47:33Z

1

Fetch all the rows into an array, then loop over them.

$rows = array();
while ( $sth->fetch() ) {
    $row['id'] = $id;
    $row['subject'] = $subject;
    $row['date'] = $date;
    $row['offset'] = $offset;
    // the cool way is $rows[] = compact('id', 'subject', 'date', 'offset');
    $rows[] = $row;
}
$sth->close();

foreach ($rows as $row) {
                //Add this 'parent' post to the data array
                $data[] = $row;

                //Before moving on to next post, get all its children
                $data[] = $this->getData($id, $row['offset'] + 1);
}

edited Dec 5, 2011 at 4:47

answered Dec 5, 2011 at 4:41

goat

31.9k7 gold badges76 silver badges98 bronze badges

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

Comments

Paulo H. · Accepted Answer · 2011-12-05 04:44:55Z

I will give you a example to show how to do this, this is the table i will use for the example (after just add the forum_id):

CREATE TABLE msgs (
    id INT NOT NULL AUTO_INCREMENT,
    date DATETIME,
    name VARCHAR(100),
    message TEXT,
    parent_id INT NOT NULL DEFAULT 0
);

Then, with one query do:

$query = mysql_query("SELECT * FROM msgs ORDER BY id");

Some arrays to build the "posts tree", all parent_id = 0 will be root posts:

$all_messages = array(); // Will store all messages
$root_messages = array(); // Will store only the root (or more than one if you allow)

while($row=mysql_fetch_assoc($query)){ // Main loop
        $all_messages[$row['id']] = array(
                'inner_messages'=>array(),
                'date'=> $row['date'],
                'name'=> $row['name'],
                'message'=>$row['message'],
                'id'=>$row['id']
                );
        if($row['parent_id']=='0'){ // If is a root post
        $root_messages[] = &$all_messages[$row['id']];
        }else{ // If not a root post, places within parent message
        $all_messages[$row['parent_id']]['inner_messages'][] = &$all_messages[$row['id']];
        }
}

Now to print, use a recursion:

function writeTree($msgs){
    foreach($msgs as $m){
        echo '<div>';
        echo '<h2>'.$m['name'].' ('.$m['date'].')</h2>';
        echo '<div class="text">'.$m['message'].'</div>';
        writeTree($m['inner_messages']);
        echo '</div>';
    }
}
writeTree($root_messages);

Collectives™ on Stack Overflow

PHP/MySQL nested fetch?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related