PHP PDO Querying within a foreach loop

Question

I'm trying to make the jump from procedural code to the PDO class, and have run into problems when querying the DB based on the results of an initial query.

In this instance, I have a menu (id = $parent) of submenus, the details of which I query successfully and are stored in an array for me to access. I then try to form a foreach loop to cycle through that initial array and query the DB again to find the pages belonging to each submenu. It is here the process fails me.

Just as a note, the connection is fine, and the queries also work without a hitch procedurally. I'm merely failing (I think) with the new format.

If someone could point out where I'm going wrong, I'd be grateful. I had been running queries run inside while loops, but I figured foreach was the way to go this time.

The Code

function navigation($parent) {

$conn = ConnManager::get('DB');

    //initial query to get list of submenus belonging to $parent
try {       
    $qNAV= $conn->prepare('SELECT ID, Name FROM prm_menu WHERE Parent = :parent');
    $qNAV->execute(array('parent' => $parent));
    $qNAV->setFetchMode(PDO::FETCH_ASSOC);  
    $n=$qNAV->fetchAll();
} 
catch(PDOException $e) {
    echo $e->getMessage();
}

    //now I try to cycle through the array and run another query each time.
foreach($n as $menu) {
    $id = $menu['ID'];
    try{
        $qPAGE=$conn->prepare('SELECT ID, Title, URLName, MenuOrder FROM posts 
                    WHERE MenuID = $id AND Status = 1 ORDER BY MenuOrder ASC, ID ASC');
        $qPAGE->execute(); 
        $qPAGE->setFetchMode(PDO::FETCH_ASSOC);  
        while($m= $qPAGE->fetchAll()) {
            $menu_item = '<li id="navPage'.$m['ID'].'" class="menu-nav">';
            $menu_item.= '<a href="'.$m['URLName'].'">'.$m['Title'].'</a>';
            $menu_item.= '</li>';

            echo '<div class="menu-nav-wrap">';
            echo '<span class="menu-title">'.$menu['Name'].'</span>';
            echo '<ul class="menu-list">';
            echo $menu_item;
            echo '</ul>';
            echo '</div>';
        }

    }
    catch(PDOException $e) {
        echo $e -> getMessage();
    }
}
}

nickb · Accepted Answer · 2012-08-03 16:11:08Z

2

This is not what you want to do:

while($m= $qPAGE->fetchAll()) {

Since fetchAll() will return all of the columns, the while statement isn't iterating over them. What you're looking for is this:

$subrows = $qPAGE->fetchAll();
foreach( $subrows as $m) {
    ....

Also, you should look into SQL JOIN statements, as you may be able to consolidate these nested queries into a single one.

answered Aug 3, 2012 at 16:11

nickb

59.7k13 gold badges115 silver badges149 bronze badges

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

3 Comments

Eamonn Over a year ago

Thanks nickb - and in procedural I would attempt a JOIN, but I just want to get going with PDO's syntax before I start elaborating the queries themselves. Unfortunately the above just returns empty arrays for each submenu (although $id is getting filled with a value).

gview Over a year ago

The use of PDO's OOP interface has nothing at all to do with the type of query you are using.

Eamonn Over a year ago

I appreciate that, I just mean I wish to keep the general simplicity level high while learning a new, albeit seperate, syntax. Thanks though.

gview · Accepted Answer · 2012-08-03 16:29:54Z

Nickb has given you the solution to your specific problem. The only thing I can add, is that you should be consistent in your use of named parameters. You used them in the initial query and should use them in the nested query as well.

With that said, his advice in regards to doing a join rather than N queries, is the same advice I would give.

It appears that you already have to perform a set of queries to resolve a hierarchy that requires you to recurse through all the children, to there's even more reason that you should rewrite this as one query.

All that requires is learning the simple inner join syntax for mysql where you include JOIN table t ON (a.id = t.id). You then get one result set to loop through. For example, it appears this is what you would need:

SELECT pm.ID, Name, p.ID, Title, URLName, MenuOrder
FROM prm_menu pm 
JOIN posts p ON (p.MenuID = pm.ID AND pm.Parent = :parent' AND p.Status = 1)
ORDER BY p.MenuOrder ASC, p.ID ASC

Collectives™ on Stack Overflow

PHP PDO Querying within a foreach loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related