PHP PDO更新多行我无法解决它

Question

Hi guys I am trying to update my database via PHP using the PDO method, my script connects to my database and selects all the data required depending on the set clientID, it then works out the difference between today's date and the created date.

It then goes on to assign each result a weight based on age (a number between 1 and 6)

What I need it to do is update all records in the database where the clientID is the same, with the new weight.

this is where I go wrong ALL WORK's FINE UP UNTIL THIS POINT! my UPDATE statement only updates the last (highest) ID (if more than one record comes back with the same clientID)

$hostdb = '';
$namedb = '';
$userdb = '';
$passdb = '';
try{
    $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
    $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
    $sql = "UPDATE parked SET weight = :weight  
            WHERE ID = :ID";
$stmt = $conn->prepare($sql);                                  
$stmt->bindParam(':ID', $ID, PDO::PARAM_INT); 
$stmt->bindParam(':weight', $weight, PDO::PARAM_INT);  


$stmt->execute(); 
$conn = null;        // Disconnect
}

catch(PDOException $e) {
  echo $e->getMessage();
}

?>

As I have never updated more than one record at a time before and usually by $_POST not inside the script itself I am really struggling.

DB IMAGE

Things to NOTE: I am getting no err messages!

Answer 1

Your update query only updates one record because you pass only one record, the very last one in the previous select statement. To pass multiple records, you would need to create a $weight[] array, calculate each row of select statement, push value into array, and then loop each item iteratively through the update query.

However, all you do in PHP can be done in a MySQL Update SQL query as MySQL carries the DateDiff() and Now() functions, and nested logical CASE/WHEN statement (in fact, even IF/THEN). Hence, you only need to connect once to run the action query:

try{
    $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
    $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
    $sql = "UPDATE parked 
            SET weight =
              CASE 
                WHEN DATEDIFF(NOW(), datecreated) <= 21 THEN 1 
                WHEN DATEDIFF(NOW(), datecreated) >= 22 AND DATEDIFF(NOW(), datecreated) < 42 THEN 2 
                WHEN DATEDIFF(NOW(), datecreated) >= 43 AND DATEDIFF(NOW(), datecreated) < 63 THEN 3 
                WHEN DATEDIFF(NOW(), datecreated) >= 63 AND DATEDIFF(NOW(), datecreated) < 84 THEN 4 
                WHEN DATEDIFF(NOW(), datecreated) >= 84 AND DATEDIFF(NOW(), datecreated) < 105 THEN 5 
                WHEN DATEDIFF(NOW(), datecreated) >= 105 THEN 6 
              END;";
    $stmt = $conn->execute($sql);                                         
    $conn = null;  
}

 catch(PDOException $e) {
    echo $e->getMessage();
}