CODEWITHSUNDEEP
phpmysqlon-duplicate-key
Alper
Alper

Reputation: 1105

What is the correct syntax for INSERT INTO ... ON DUPLICATE KEY UPDATE in MySQL?


my table(s) structure (MySQL / each one is same as below)

+-------+--------------+------+------+-------------------+
| Field | Type         | Null | Key  | Default           |
+-------+--------------+------+------+-------------------+
| id    | int(11)      | NO   | PRI  | AUTO INCREMENT    | 
| lesson| varchar(255) | NO   |      | LESSON_NAME       | 
| exam  | char(50)     | NO   |UNIQUE| NO DEFAULT        |
| quest | text         | NO   |      | NO DEFAULT        |
| answer| text         | NO   |      | NO DEFAULT        |
| note  | text         | NO   |      | NO DEFAULT        |
+-------+--------------+------+------+-------------------+

and i'am posting some values to add this table via ajax ($post) - PHP 5.0
in database.php there is a function to get posted data and add to table

function update_table ($proper_table, $name, $question, $answer, $note) {
$sql = "INSERT INTO $proper_table (id, lesson, exam, quest, answer, note) VALUES ('', '', $name, $question,$answer,$note) ON DUPLICATE KEY UPDATE exam = $name, quest = $question, answer = $answer, note = $note";
$result= mysql_query($sql)or die(mysql_error());
}

$proper_table variable is taken by another variable to add this record to correct table.
(NOTE: Original table fields and variables are different (Turkish), to be more understandable i traslated to english but the syntax is the same as you see.)
Question : I want to check that if there is a record that exam field is same then all these variables will be used for updating this record, otherwise let function put this record to proper table as a new record.
But i'am getting error like below

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

is there any faulty coding? and what can be the solution?
Thanks right now...

Upvotes: 2

Views: 8491

Answers (5)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

function update_table ($proper_table, $name, $question, $answer, $note) {
    $sql = "INSERT INTO $proper_table (lesson, exam, quest, answer, note) VALUES ('', '$name', '$question','$answer','$note') ON DUPLICATE KEY UPDATE quest = VALUES(quest), answer = VALUES(answer), note = VALUES(note)";
   $result= mysql_query($sql)or die(mysql_error());
}

Just breaking this out I'll detail the changes

$sql = "INSERT INTO $proper_table 

// Removed the PK (primary key) AI (auto increment) field - don't need to specify this
(lesson, exam, quest, answer, note)     

// Likewise removed PK field, and added quotes around the text fields
VALUES ('', '$name', '$question','$answer','$note')    
ON DUPLICATE KEY UPDATE 

// If you specify VALUES(fieldName) it will update with the value you specified for the field in the conflicting row
// Also removed the exam update, as exam is the UNIQUE key which could cause conflicts so updating that would have no effect
quest = VALUES(quest), answer = VALUES(answer), note = VALUES(note)";

Upvotes: 6

Adeel Mughal
Adeel Mughal

Reputation: 736

You have to make it like this

<?php
function update_table($proper_table, $name, $question, $answer, $note, $id) {

    $sqlQuery = "INSERT INTO '".$proper_table."' SET
                    name        =       '".$name."',
                    question        =   '".$question."',
                    answer      =       '".$answer."',
                    note        =       '".$note."' WHERE id = '".$id."'";

    $result= mysql_query($sqlQuery)or die(mysql_error());

}
?>

Upvotes: 0

SimonMayer
SimonMayer

Reputation: 4916

id auto-increments, so presumably you don't want to set an empty string as id.

Try:

$sql = "INSERT INTO $proper_table (lesson, exam, quest, answer, note) VALUES ('', $name, $question,$answer,$note) ON DUPLICATE KEY UPDATE exam = $name, quest = $question, answer = $answer, note = $note";

Upvotes: 0

Daniel
Daniel

Reputation: 110

With that query, when you add ON DUPLICATE KEY UPDATE... it will update when the id es the same than the id that you are sending, in this case you are not sending an id as parameter so it will never update because you have the id with auto-increment.

A solution could be that you read the table where exam equals the parameter you are sending, something like this:

    SELECT id FROM $proper_table;

If it is null the you execute an insert, if it is not null the you update taking as parameter the id that you are getting from the select

Upvotes: 0

Brian Glaz
Brian Glaz

Reputation: 15666

You need to wrap you string variables in single quotes in your SQL '$name' for example. Otherwise mysql thinks you are referencing column names.

Upvotes: 0

Related Questions