Prepared Statement On Duplicate Help

[crwork]

I have a working prepared statement in PHP that does an INSERT.

 

However, I'd like to leverage ON DUPLICATE KEY to update one field in the event the record already exists.

 

I'm having trouble using the '?' in the ON DUPLICATE clause. Here's what I'd like to do, but isn't working:

 

INSERT INTO user_prefs

(user_id, user_name, filter_prefs, email)

VALUES

(?, ?, ?, ?)

ON DUPLICATE KEY

UPDATE filter_prefs = ?

 

$stmt->bind_param('isss', $user_id, $user_name, $filter_prefs, $email);

 

Without the ON DUPLICATE, this works, but PHP seems to expect another bind parameter if I use a '?' in the On DUPLICATE clause::

 

"mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement"

 

I also tried in the ON DUPLICATE clause updating all 4 fields, but I still got the bind_param() error.

 

I searched all over and couldn't find an example for this.

 

Anyone have thoughts? I could use mysqli_real_escape_string on filter_prefs, but that seems like a cluge...

[ManiacDan]

You have 5 question marks and only 4 variables. Not sure why this is confusing. Add the variable again.

[PFMaBiSmAd]

The VALUES(column_name) function (not be confused with the VALUES keyword) should work -

 

UPDATE filter_prefs = VALUES(filter_prefs)

Edited October 4, 2012 by PFMaBiSmAd

[crwork]

You have 5 question marks and only 4 variables. Not sure why this is confusing. Add the variable again.

 

I realize that. I was under the assumption that I wouldn't need to supply another parameter in the bind for the ON DUPLICATE clause '?' but it looks like I do .All set now, thanks.

 

$stmt->bind_param('issss', $user_id, $user_name, $filter_prefs, $email, $filter_prefs);

[ManiacDan]

How was the computer supposed to know which of your 4 variables was the right one? PHP can't read SQL, it's a completely separate language.

 

The VALUES() function is technically more correct, but either works.