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?
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.
You need a
$stmt->closeCursor();
Otherwise the statement is stuck in the first run. You might want to check with a try...catch, echoing the error-message.
execute()calls in your code, one is in the loop. I guess I don't understand your question?$stmp. You also should prepare outside of the loop.UPDATE videos SET vkey = MD5(vkey);?