Using INSERT INTO On Duplicate Key UPDATE for form input

Question

How do you use INSERT INTO On Duplicate Key UPDATE for form input? All the examples I've found online are with counters or predetermined values.

I've been able to get my code to work (thanks to some really helpful members) with the standard UPDATE and SET method, but my tables really call for using INSERT INTO On Duplicate Key UPDATE.

'user_id' is unique primary key in all of the tables and is a foreign key in all but the account table.

<?php
session_start();  
require_once('config.php'); 
require_once('open_db.php');     

$setlist='';
foreach ($_POST as $key=>$value) {
  $setlist.=$key .'=\''.$value.'\',';
}

$setlist=substr($setlist, 0, -1);
$user_id=$_SESSION['SESS_USER_ID'];  
$sql='UPDATE style_test SET '.$setlist.' WHERE user_id='.$user_id;

if (!mysql_query($sql,$con)) {
  die('Error: ' . mysql_error());
}         
?>

The code that I was using previously to automatically INSERT every field is:

$fieldlist=$vallist='';
foreach ($_POST as $key => $value) {
  $fieldlist.=$key.',';
  $vallist.='\''.urlencode($value).'\',';
}
$fieldlist=substr($fieldlist, 0, -1);
$vallist=substr($vallist, 0, -1);

$user_id=$_SESSION['SESS_USER_ID'];
$fieldlist.=', user_id';
$vallist.=','.$user_id;

$qry='INSERT INTO style_test1 ('.$fieldlist.') VALUES ('.$vallist.')';

@Nathaniel Ford - I will definitely do that as soon as I am able to get this to work :-) One of my previous questions was answered by someone else in the comments - should I accept that even if the actual answer was incorrect? — Chaya Cooper
– Chaya Cooper, Commented Jul 25, 2012 at 22:54
If no one answered after a reasonable time, and you found another solution, you should post your own, correct solution and accept that. This makes sure future searchers find the answer they need! — Nathaniel Ford
– Nathaniel Ford, Commented Jul 26, 2012 at 1:04

nickb · Accepted Answer · 2012-07-25 21:31:19Z

2

The syntax highlighter shows you where your problem is:

$sql='UPDATE style_test SET ;.$setlist.' WHERE user_id='.$user_id;
                            ^
                            Here

This needs to be a single quote:

$sql='UPDATE style_test SET '.$setlist.' WHERE user_id='.$user_id;

You should also note that the mysql_* functions are deprecated, and you should not be using them. Also, your original code is wide open to SQL injection.

For on duplicate key update, you add that to your SQL query, followed by all the column = value fields you want to update:

$sql='INSERT INTO style_test SET ' . $setlist.' WHERE user_id = ' . $user_id. ' ON DUPLICATE KEY UPDATE ' . $setlist;

edited Jul 25, 2012 at 21:31

answered Jul 25, 2012 at 16:32

nickb

59.7k13 gold badges115 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Chaya Cooper Over a year ago

That did it :-D Thanks for catching that :-) Now that it's working it made me realize that I really need to be using 'On Duplicate Key UPDATE' and I edited the question accordingly.

Chaya Cooper Over a year ago

I also appreciate your pointing out the other issues, and I'm going to look into how to modify the code to prevent injection asap. I wasn't sure what you meant though about the mysql_* functions being deprecated since the code I've used is based on W3schools suggested code and I couldn't find anything that seemed to be an issue in their list of deprecated functions. Would it be better to use: $result = @mysql_query($qry); if($result) { exit(); }else { die('Error: ' . mysql_error()); } Instead of: if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); }

nickb Over a year ago

No, then you are suppressing errors. You should be using PDO. There are plenty of examples on stackoverflow and the docs to get you started.

Chaya Cooper Over a year ago

I'll look into that :-) But would you mind clarifying for me which part of my code is a problem due to being deprecated?

nickb Over a year ago

mysql_query() is being deprecated, you can see the red box on the doc page for it. Also, the other part of your problem is SQL injection, which PDO can fix if you used prepared statements (all things you can search for examples).

|

Alex Reynolds · Accepted Answer · 2012-07-25 17:47:05Z

0

Make sure you are escaping your form inputs. Right now you are open to SQL injection. Use something like mysql_real_escape_string at least or use is_numeric to make sure numeric values are numbers not SQL. Very dangerous code you have because it is taking anything a user types and exposing your DB to it.

answered Jul 25, 2012 at 17:47

Alex Reynolds

6,3524 gold badges30 silver badges43 bronze badges

1 Comment

Chaya Cooper Over a year ago

Thanks for the suggestion. I'm going to look into how to modify the code to prevent injection asap :-)

Collectives™ on Stack Overflow

Using INSERT INTO On Duplicate Key UPDATE for form input

2 Answers 2

12 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related