CODEWITHSUNDEEP
phpforeachpdo
Eamonn
Eamonn

Reputation: 1320

PHP PDO Querying within a foreach loop

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();
    }
}
}

Upvotes: 1

Views: 4766

Answers (2)

gview
gview

Reputation: 15361

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

Upvotes: 1

nickb
nickb

Reputation: 59699

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.

Upvotes: 2

Related Questions