CODEWITHSUNDEEP
phpmysqlprimary-key
Iain Simpson
Iain Simpson

Reputation: 469

ON DUPLICATE KEY UPDATE creating new records

I am having problems with the following code, it seems to work and creates the records just fine, the problem is each time I hit submit, instead of it updating the record it just creates a new one. If I turn off auto incremental for the primary key it updates the record just fine but then doesn't create any new ones, it seems either one or the other :-S 

<?php
 $query = mysql_query("
  INSERT INTO hqfjt_chronoforms_data_emailform 
   (cf_id,cf_uid,emailformname,datesent)
  VALUES
   ('$_POST[cf_id]','$_POST[cf_uid]','$_POST[emailformname]','$_POST[datesent]')
  ON DUPLICATE KEY UPDATE
   datesent='$_POST[datesent]';
 ") or die(mysql_error());
 ?>

Upvotes: 0

Views: 301

Answers (2)

Kaii
Kaii

Reputation: 20550

did you already try to echo your query string? guess the variable replacement inside it is wrong. try something like that for debugging:

<?php
 $sql = "INSERT INTO hqfjt_chronoforms_data_emailform 
   (cf_id,cf_uid,emailformname,datesent)
  VALUES
   ('{$_POST['cf_id']}','{$_POST['cf_uid']}','{$_POST['emailformname']}','{$_POST['datesent']}')
  ON DUPLICATE KEY UPDATE
   datesent='{$_POST['datesent']}'";
 echo $sql; // for debugging
 $query = mysql_query($sql) or die(mysql_error());
 ?>

Note the corrected variable names above. (curly braces around it, quotes around the array index)

Upvotes: 2

jeroen
jeroen

Reputation: 91792

I can't imagine it's the problem, but does the same thing happen when you cast the ID to an int and leave out the quotes?

<?php
 $query = mysql_query("
  INSERT INTO hqfjt_chronoforms_data_emailform 
   (cf_id,cf_uid,emailformname,datesent)
  VALUES
   (" . (int) $_POST['cf_id'] . ",'$_POST[cf_uid]','$_POST[emailformname]','$_POST[datesent]')
  ON DUPLICATE KEY UPDATE
   datesent='$_POST[datesent]';
 ") or die(mysql_error());
 ?>

By the way, you really shouldn't use your $_POST variables in your query without mysql_real_escape_string or better yet, use prepared statements (PDO or mysqli).

Upvotes: 0

Related Questions