尝试SELECT，减少1，然后更新php/mysql - Attempting to SELECT, reduce by 1 then UPDATE with php/mysql

Attempting to SELECT, reduce by 1 then UPDATE with php/mysql

只是试图找到一个表字段的值，然后通过PHP使用MySQL减少它?

是否有简单的方法来做到这一点?

下面的代码将不处理…

欢呼,J

<?php   
$conn = connectToDB();
/* DECLARE remaining AS INTEGER  */
settype($screenings, "integer"); 
/* SELECT CURRENT screenings VALUE AND ASSIGN TO remaining VARIABLE */
$findquery("SELECT screenings FROM screener_users WHERE user_id = '" . $userId . "';");
$screenings = $conn->query($findquery); 
/* REDUCE THAT VALUE BY ONE */
$screenings--;
/* UPDATE screener_users WITH THAT NEW VALUE */
$updatequery = "UPDATE screener_users SET screenings = '" . $screenings . "' WHERE user_id = '" . $userId . "';";
$result = $conn->query($updatequery);
?>

用一个命令就可以很容易地做到这一点:

"UPDATE screener_users SET screenings = screenings - 1 WHERE user_id = '$userId'"

顺便说一下，你打开SQL注入。

为user_id使用准备语句和参数绑定。

您在查询调用上犯了一个错误。正如您在文档中看到的，它返回一个PDOStatement，因此您应该调用一个结果集来获得您想要的值。像这样的代码应该可以工作:

<?php   
$conn = connectToDB();
settype($screenings, "integer"); 
$findquery("SELECT screenings FROM screener_users WHERE user_id = '" . $userId . "';");
foreach ($conn->query($findquery) as $row)
{
    $screenings = $row[0];
    $screenings--;        
    $updatequery = "UPDATE screener_users SET screenings = '" . $screenings . "' WHERE user_id = '" . $userId . "';";
    $result = $conn->query($updatequery);
}
?>

现在您正在调用要执行的查询，并说它应该获得哪一行的结果集，并在迭代时将其传递给$row。在这里，调用查询返回的第一列($row[0])，并完成其余的工作。