Proper format for ON DUPLICATE KEY UPDATE

aspekt9

I have a query that inserts information about a news event into the database and I wish to incorporate the ON DUPLICATE KEY UPDATE clause into it. My question is where's the correct positioning for it since I use sprintf and mysql_real_escape_string?

Here's the statement:

	$query = sprintf("INSERT INTO news (title,author,topic,intro,body,date) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
			mysql_real_escape_string($title),mysql_real_escape_string($author),mysql_real_escape_string($topic),
			mysql_real_escape_string($intro),mysql_real_escape_string($body),mysql_real_escape_string($timestamp));
	$db->query($query);

HalfaBee

Read the manual 😉

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

aspekt9

Thats great and all... however it mentions nothing on how to deal with it when using sprintf?

HalfaBee

Nope, it explains mysql syntax.

I would take all the functions out of sprintf like this

$title = mysql_real_escape_string($title);

then it is easier to do, you don't need sprintf then.

$query = "INSERT INTO news (title,author,topic,intro,body,date) VALUES ('%s', '%s', '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE
title = '$title' , author = '$author'

etc.

you just don't want to do the PRIMARY col.

bradgrafelman

Well, they don't tell you about using mysql_query either, or storing the query in a variable. They tell you what the string should be... all you do is add that to your sprintf() string.

If you don't know how to add the words "ON DUPLICATE KEY UPDATE col = 'new value'" to an sprintf() command... read the PHP manual for [man]sprintf/man.