我正在尝试将MySQLi代码转换为PDO代码，但没有成功

Question

I have working code with MySQLi, but I am making some new project and want to do with PDO, that shows multi-level category view. I'm new with PDO and still learning.

This is my working MySQLi:

function categoryTree($parent_id = 0, $sub_mark = ''){
    global $db;
    $query = $db->query("SELECT * FROM categories WHERE parent_id = $parent_id ORDER BY name ASC");

    if($query->num_rows > 0){
        while($row = $query->fetch_assoc()){
            echo '<tr><td>'.$sub_mark.''.$row['name'].'</td><td><button type="button" name="update" id="'.$row["id"].'" class="btn btn-info btn-sm update"><span class="glyphicon glyphicon-pencil"></span></button></td></tr>';
            categoryTree($row['id'], $sub_mark.'---');
        }
    }
}

Closed variant to PDO what I achieve but now working:

function categoryTree($parent_id = 0, $sub_mark = ''){

    $query = "SELECT * FROM ws_categories WHERE parent_id = $parent_id ORDER BY name ASC";
    $statement = $connection->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();

    if($result->num_rows > 0){
        while ($row = $statement->fetch()) {
            echo '<tr><td>'.$sub_mark.''.$row['name'].'</td><td><button type="button" name="update" id="'.$row["id"].'" class="btn btn-info btn-sm update"><span class="glyphicon glyphicon-pencil"></span></button></td></tr>';
            categoryTree($row['id'], $sub_mark.'---');
        }
    }
}

Error:

Fatal error: Uncaught Error: Call to a member function prepare() on null in

p.s. I have included config.php with pdo db connection that connect properly.

Please help me, I try several ways but no success.

Sorry for my English :)

Answer 1

I think you are trying to iterate over the wrong result set. And I am unsure if the attribute num_rows is set in a fetchAll.

You can replace the if and while part with a single while/foreach. If you want to use the fetchAll function you can simply foreach loop over the result set. However, if you do a big query this can take a lot of memory so it is not always preferred to do so.

For the fetchAll solution you would end up with the following:

$results = $query->fetchAll()
foreach ($results as $result) {
    var_dump($result); // Process data here.
}

If you want to use the fetch function which is more memory/resource friendly you can use a while loop like you used in MySQLi:

while ($data = $query->fetch()) {
    var_dump($data); // Process data here.
}

edit:

I just noticed you are directly inserting variables into a query, this is bad practice as it allows for sql-injection. If you are using PDO anyway take a look at prepared statements you can replace the variable with a placeholder eg. :parent_id then on your execute call you pass an array which contains the value of the parent_id and it does some sql injection prevention.

That would look like:

$query = 'select * from ws_categories where parent_id = :parent_id';
$statement = $connection->prepare($query);
$statement->execute(['parent_id' => $parent_id]);