CODEWITHSUNDEEP
mysqlon-duplicate-key
Iain Simpson
Iain Simpson

Reputation: 469

ON DUPLICATE KEY not working correctly

I am running the following query, but getting an error. I think it is coming from the ON DUPLICATE KEY part, but I'm not 100% sure what the correct syntax is to use there. 

<?php
 $form_id = $form->data['form_id'];
 $cfid = $form->data['cf_id'];
 $datesent = $form->data['datesent'];
 $query = mysql_query("
INSERT INTO `email_history` (
`cf_id` ,
`$form_id`
)
VALUES (
'$cfid', '$datesent'
)
ON DUPLICATE KEY 
UPDATE INTO
`email_history` (
`$form_id`
)
VALUES (
'$datesent'
);
") or die(mysql_error());
?>

EDIT

Using the following I am getting this error :

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 ')' at line 10

    <?php
 $form_id = $form->data['form_id'];
 $cfid = $form->data['cf_id'];
 $datesent = $form->data['datesent'];
 $query = mysql_query("
    INSERT INTO `email_history` (
    `cf_id` ,
    `$form_id`
   )
     VALUES (
    '$cfid', '$datesent'
   )
   ON DUPLICATE KEY 
  UPDATE `$form_id` = `$datesent`
  );
   ") or die(mysql_error());
    ?>

Upvotes: 0

Views: 1690

Answers (1)

Filip Ros&#233;en
Filip Ros&#233;en

Reputation: 63797

The correct syntax of ON DUPLICATE KEY is something a long the lines described below.

Please note that it's just an example snippet, though it should be clear why your provided snippet fails to execute.

INSERT INTO tbl (
 col1, col2, ... , colN
) VALUES (
  @val1, @val2, ..., @valN
) ON DUPLICATE KEY UPDATE
  col3 = VALUES(col3), col4 = @val4

Documentation

How would that look in my code?

$form_id  = $form->data['form_id'];
$cfid     = $form->data['cf_id'];
$datesent = $form->data['datesent'];

$query = mysql_query (<<<EOT
  INSERT INTO `email_history` (`cf_id`, `$form_id`)
  VALUES ('$cfid', '$datesent')
  ON DUPLICATE KEY UPDATE `$form_id` = VALUES(`$form_id`)
EOT
) or die (mysql_error ());

What does VALUES($form_id) do?

It will yield the value of what was originally passed as the value for column named $form_id.

What is the use of <<<EOT?

In the previous snippet we are using a HEREDOC to have a string span multiple lines in an easy manner. You can read more about it in the documentation for heredocs.

Upvotes: 4

Related Questions