phppdoforeach

PDO query in loop


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?


Solution

  • 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.