Search code examples
mysqlbindparam

MySQL using Bind_Param and ON DUPLICATE KEY

I have a table called test_favoriteswhere users are able to add something to favorites. I use this SQL-function, but get this error:

call to a member function bind_param() on boolean in line (bind_param-line)

$sql = $conn->prepare("INSERT INTO `test_favorites` (`What`, `OId`, `UserId`) VALUES (?,?,?) ON DUPLICATE KEY UPDATE Values(`Value`)=Values(`Value`)*(-1);");
$sql->bind_param('sis',$posttype, $postid  , $sessionuserid);

I dont know if solving the error solves my problem. Because in my table test_favorites the columns What, OIdand UserId are uniqe. In the column Value I have assigned the default value to 1. But I am trying to make a SQL which are checking if What, OIdand UserId already exists, and if it exist, then the script should multiply the value in value with (-1). So in that way Value switches between 1 and -1. If it dont exists then the default value should be printed in column value.

Am I writing the SQL sentence wrong?

EDIT After som starthelp I now use this code:

$sql = $conn->prepare("INSERT INTO `test_favorites` (`What`, `OId`, `UserId`, `Value`) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE Value=values(Value)*(-1);");
$sql->bind_param('sisi',$posttype, $postid,$sessionuserid,$one);

I seem to get closer, but it still does not update, just adds one new row to the table.

Solution

  • Well, valid syntax for ON DUPLICATE KEY UPDATE is

    INSERT INTO `test_favorites` (`What`, `OId`, `UserId`) VALUES (?,?,?) ON DUPLICATE KEY UPDATE userId = values(userId) * -1, anyotherfield = values(What) + values(OId);

    So in your case - you can update any field in the table, but can use values SQL function on the three ones you're actually inserting