Updating multiple MySQL table columns using arrays with PDO
Solution 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.
Solution 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);
user1562781
Updated on June 21, 2022Comments
-
user1562781 almost 2 years
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?
-
user1562781 over 11 yearsPerfect, I see my mistake. Thank you. Is this method the best way of updating multiple rows or is there another more efficient way?
-
Michael Berkowski over 11 years@user1562781 If those values are all arrays as they appear to be, then you do need to update them in a loop as you are doing. Since the prepared statement is only compiled once before your loop, you are getting a performance benefit from it already.
-
Your Common Sense over 10 yearsYou really call it "simple"?
-
viktorino over 10 yearsMaybe not for beginners but its better than do 10 variables of each input field. With this example you can uodate any table and no metter how mutch fields