mysqli_query while loop to PDO

Question

I am trying to upgrade my way to fetch data from sql from mysqli_query to fetchall.

$res = mysqli_query($db, "SELECT * FROM forum_index WHERE forum_over='yes'");
        while ($arr = mysqli_fetch_assoc($res)) {
        ......
    }

So when I use fetchAll() I'll get an array, Am I supposed to use foreach() then or is there a smarter way of doing this?

And to collect a single value from the DB this is the right way right?

$fid = (int)$_GET['id'];
$thread = $db->query("SELECT * FROM forum_threads WHERE f_id=".$fid)->fetch_array();
    echo $thread['id'];

Why do you think you need to switch to fetchAll when you switch to PDO? it has fetch(PDO::FETCH_ASSOC), which allows you to keep the same structure. — Barmar
– Barmar, Commented Jan 27, 2018 at 0:06
So how would you convert: $res = mysqli_query($db, "SELECT * FROM forum_index WHERE forum_over='yes'"); while ($arr = mysqli_fetch_assoc($res)) { ...... } ? Sorry, trying to get my head around it :) — LonniE
– LonniE, Commented Jan 27, 2018 at 0:11

Barmar · Accepted Answer · 2018-01-27 00:10:37Z

2

You don't need to use fetchAll() just because you're using PDO. If the query returns a large amount of data, this could slow things down because it has to collect it all into memory. You can use the same kind of loop as in your mysqli code:

$res = $pdo->query("SELECT * FROM forum_index WHERE forum_over='yes'");
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
    ...
}

As to your second question, you should use a parametrized query, not substitute variables.

$stmt = $pdo->prepare("SELECT * FROM forum_threads WHERE f_id= :id");
$stmt->bindParam(':id', $_GET['id']);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    ...
}

answered Jan 27, 2018 at 0:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

LonniE Over a year ago

Now I see how its works, thank you for your explanation Barmar. I need to read some more about pdo, but you solved my current problem. Thanks!

Collectives™ on Stack Overflow

mysqli_query while loop to PDO

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related