CODEWITHSUNDEEP
phpmysqlsql
Chris
Chris

Reputation: 545

Updating a MySQL record with an auto-increment field as PK

I have a MySQL table with an auto-incrementing primary key (UID). The user can bring up a record in this table via a PHP form, where the UID is a $_GET variable. I would like the database to update the chosen record if it exists, but every time the user hits submit, a new record is created. This is what I've tried so far:

//Present the form
$form = $con->prepare("SELECT Name, Date, Time FROM forms WHERE UID = :uid");
$data = array('uid'=>$_GET['uid']);
$form->execute($data);
$row = $form->fetch(PDO::FETCH_ASSOC);    

//Write new record to database. If the UID exists, update the record.
INSERT INTO forms (Name, Date, Time) VALUES (:name, :date, :time)

ON DUPLICATE KEY UPDATE Name=VALUES(Name),Date=VALUES(Date),Time=VALUES(Time);

This doesn't work. Do I need another unique key in the table, or am I missing something obvious?

Upvotes: 1

Views: 1486

Answers (1)

user2742371
user2742371

Reputation: 

INSERT INTO forms (`Name`, `Date`, `Time`) VALUES (:name, :date, :time)
ON DUPLICATE KEY UPDATE `Name`=:name, `Date`=:date, `Time`=:time;

This should be the query; I removed VALUES() from your code and added backticks and corrected your parameters.

And I recommend you to use backticks around column names so it doesn't get confused with other similar SQL keywords as example.

Upvotes: 1

Related Questions