0

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
  • 20,545
  • 20
  • 91
  • 102
Chaya Cooper
  • 2,566
  • 2
  • 38
  • 67
  • @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 Jul 25 '12 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 Jul 26 '12 at 01:04
  • You're absolutely right, and I just did that :-D – Chaya Cooper Jul 26 '12 at 02:34

2 Answers2

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;
nickb
  • 59,313
  • 13
  • 108
  • 143
  • 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 Jul 25 '12 at 18:09
  • 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()); } – Chaya Cooper Jul 25 '12 at 18:36
  • No, then you are suppressing errors. You should be using [`PDO`](http://www.php.net/PDO). There are plenty of examples on stackoverflow and the docs to get you started. – nickb Jul 25 '12 at 18:38
  • I'll look into that :-) But would you mind clarifying for me which part of my code is a problem due to being deprecated? – Chaya Cooper Jul 25 '12 at 18:41
  • `mysql_query()` is being deprecated, you can see the red box on [the doc page for it](http://www.php.net/mysql_query). 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). – nickb Jul 25 '12 at 18:42
  • Thanks for clarifying :-) And now I know that I can't rely on only checking w3schools :-( – Chaya Cooper Jul 25 '12 at 19:49
  • btw - any chance that you can help me with my revised question to get my code working with On Duplicate Key UPDATE instead? I'd love to fix that problem before diving into learning about PDO's – Chaya Cooper Jul 25 '12 at 19:54
  • Wow! Good to know! I've really relied on it because I find it so much easier to follow for novices like me than php.net, etc, but I'm going to check out some of those other sites they list immediately. – Chaya Cooper Jul 25 '12 at 20:00
  • You know I will ;-D But if it doesn't affect your ranking it might be helpful to create a separate answer so that other people can easily find it and benefit from it too. – Chaya Cooper Jul 25 '12 at 20:00
  • I've updated my answer with a solution for on duplicate key update – nickb Jul 25 '12 at 20:14
  • I'm getting an error message with it - 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 'WHERE user_id = 168 ON DUPLICATE KEY UPDATE... I also realized that I should have been more specific because I'm trying to do INSERT INTO on duplicate key UPDATE (my apologies, I didn't know that there was another on duplicate key UPDATE query), and I've updated my question to include that as well as the code that I was using previously to automatically INSERT every field. – Chaya Cooper Jul 25 '12 at 21:12
  • You can only do an `ON DUPLICATE KEY UPDATE` query when there is a unique key on the table, most likely the primary key. If this is not `user_id`, what is it? – nickb Jul 25 '12 at 21:35
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.

Alex Reynolds
  • 6,264
  • 4
  • 26
  • 42