PDO query in loop

Question

I have this code:

$sql = "SELECT id FROM videos";
$stmp = $db->prepare($sql);
$stmp->execute();
while ($row = $stmp->fetchAll(PDO::FETCH_ASSOC)) {

    $vkey = md5($row['id'] . "video");

    $sql = "UPDATE videos SET vkey = :vkey WHERE id = :id";
    $stmp = $db->prepare($sql);
    $stmp->execute(array(
        ":vkey" => $vkey,
        ":id"   => $row['id']
    ));

}

Why is execute only for the first id from the first select and not for all it's in the loop?

There are two `execute()` calls in your code, one is in the loop. I guess I don't understand your question? — KIKO Software, Sep 26 '18 at 18:20
first select get all id's from table videos and in loop i want to execute query for each id — user1964450, Sep 26 '18 at 18:22
It’s better to fetch all into array and then loop through it https://stackoverflow.com/a/15386703/3254405 — boateng, Sep 26 '18 at 18:26
You overwrite `$stmp`. You also should prepare outside of the loop. — user3783243, Sep 26 '18 at 18:32
Why not use MySQL's [MD5()](https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html) for a pure SQL call: `UPDATE videos SET vkey = MD5(vkey);`? — Parfait, Sep 26 '18 at 18:32

GrumpyCrouton · Accepted Answer · 2018-09-26T18:48:31.830

3

You could completely avoid all of that code by just doing this:

$db->query("UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video'))");

(Or you could do this query in your backend like PHPMyAdmin UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video')))

However, if you for some reason want to loop through your database, you could do this:

$sql = "SELECT id FROM videos";

//no reason to use prepare() because you aren't passing variables.
$stmp = $db->query($sql);
$stmp->execute();
$results = $stmp->fetchAll(PDO::FETCH_ASSOC);

//prepare UPDATE query outside of loop, this way you don't send 2 requests to your database for every row
$stmp = $db->prepare("UPDATE videos SET vkey = :vkey WHERE id = :id");

foreach($results as $result) {
    $vkey = md5($result['id']."video");
    $stmp->execute(
        array(
            ":vkey" => $vkey, 
            ":id" => $result['id']
        )
    );
}

Also, it's usually a good idea to check the return values inside the loop to make sure there were no errors, you could probably do this by using something like $stmp->rowCount() to check if there were any rows effected.

edited Sep 26 '18 at 18:48

answered Sep 26 '18 at 18:31

GrumpyCrouton

8,486
7
32
71

Is not working. Is updated only for the first id from select. – user1964450 Sep 26 '18 at 18:35
Always a good idea to check return values to ensure no errors occurred as well. – miken32 Sep 26 '18 at 18:36
@user1964450 Glad I could help! – GrumpyCrouton Sep 26 '18 at 18:37
@miken32 I'll add that to my answer, however I'm not gonna update the code because I'm not sure how OP wants to handle errors. – GrumpyCrouton Sep 26 '18 at 18:42

PDO query in loop

1 Answers1