Php pdo：是否可以在递归中重用prepare语句

Question

trying to go through a tree structure data in DB

the table is kinda like this

main_id  sub_id
------------------------
1            2
1            3
2            4
3            5

my function is below:

$sql="Select * from info_map where main_id= ? ";
$stmt=$conn->prepare($sql);
if(!$stmt){ throw new Exception ( implode(' ',$conn->errorInfo()),0); }

search_child($stmt,215);

function search_child($stmt,$mom){   

   $res=$stmt->execute(array($mom));
   if(!$res){ throw new Exception( implode(' ',$stmt->errorInfo()),1); }
   $rc=$stmt->rowCount();

   if($rc>0){
        while($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)){
            $nextMom=$row['sub_id'];
            echo $mom.'->'.$nextMom.'<br>';
            search_child($stmt,$nextMom);

        }    
}

The $stmt=$conn->prepare() was inside the function and I got the result I want (return all the children and their grandchildren....etc)

but it is not reusing the prepare statement since we run the same sql.

So I move the $stmt=$conn->prepare() out of function like the code above, but in this way it only return one child and its grandchild.

Can I reuse statement object like this in recursive function? or I just did something wrong?

---

Update: @Barmar your reply help me realize why I got only one line of family tree, because when the loop go back to 2nd child of 2nd generation the statement obj has been gone since it has been used to other generation's query (descendants of the 1st child of the 2nd generation ) thank you.

Answer 1

As hinted by @Barmar, there may be some conflicting between php and the mysql server. I usually pass prepared statements for my recursive functions without problem, but get the results through a fetchAll() like this:

function search_child($stmt,$mom){   

    $res=$stmt->execute(array($mom));
    if(!$res){ throw new Exception( implode(' ',$stmt->errorInfo()),1); }

    foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){ 
    // PDO fetch orientation is next by default. no need to specify it.
        $nextMom=$row['sub_id'];
        echo $mom.'->'.$nextMom.'<br>';
       search_child($stmt,$nextMom);
    }   
}