Updating multiple MySQL table columns using arrays with PDO

Question

I'm trying to switch all my MySQL connections from the old mysql_query to PDOs. I'm trying to update multiple rows and columns of a MySQL table using different arrays and I'm receiving the following error:

[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(accnt, car, radio, misc) values ('admin', '300.00', '400.00', '10.00') WHERE ID' at line 1

From the following code:

$account = $_POST['account'];
$car_lease = $_POST['car_lease'];
$radio_lease = $_POST['radio_lease'];
$misc_lease = $_POST['misc_lease'];
$lease_ID = $_POST['lease_ID'];

//$data = array_map(null,$account,$car_lease,$radio_lease,$misc_lease);
$A = count($lease_ID);

try {
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $STH = $DBH->prepare('UPDATE lease (accnt, car, radio, misc) values (:account, :car_lease, :radio_lease, :misc_lease) WHERE ID = :lease_ID');
    $i = 0;
    while($i < $A) {
        $STH->bindParam(':account', $account[$i]);
        $STH->bindParam(':car_lease', $car_lease[$i]);
        $STH->bindParam(':radio_lease', $radio_lease[$i]);
        $STH->bindParam(':misc_lease', $misc_lease[$i]);
        $STH->bindParam(':lease_ID', $lease_ID[$i]);
        $STH->execute();
        $i++;
    }
}
catch(PDOException $e) {  
    echo "I'm sorry, but there was an error updating the database.";  
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
}

I believe this problem is arising from the way I'm calling the statement handle, but I'm not sure what part of my syntax is incorrect. Also, is this the best way of handling such situations? Or is there a better method to update multiple rows in a table?

Answer 1

You have confused the syntax between INSERT and UPDATE statements. Instead of a VALUES() list, you need a SET clause:

$STH = $DBH->prepare('
    UPDATE lease 
    SET 
      accnt = :account, 
      car = :car_lease, 
      radio = :radio_lease, 
      misc = :misc_lease 
    WHERE ID = :lease_ID
');

Review the MySQL UPDATE syntax reference for the full specification to use with UPDATE statements.

Answer 2

I think this would be the simplest and easiest solution, if you can trust your keys and values:

$update = 'SET ';
$fields = array_keys($_POST);
$values = array_values($_POST);
foreach ($fields as $field) {
    $update .= $field . '=?,';
}
$update = substr($update, 0, -1);
$db->query("update sub_projects ${update} where id=${_GET['id']}");
$db->execute($values);