StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdating multiple MySQL table columns using arrays with PDO
text
Body
copied!<p>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:</p> <p>[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</p> <p>From the following code:</p> <pre><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); } </code></pre> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload