0

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.

Meer
  • 2,765
  • 2
  • 19
  • 28
  • The `Insert into`worked well before I inserted `ON DUPLICATE KEY`. –  Apr 13 '17 at 07:13
  • $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); This is my last code. –  Apr 13 '17 at 08:00

1 Answers1

1

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

Alexey
  • 2,388
  • 1
  • 16
  • 32
  • Okey, so i tried to adapt and wrote this: $sql = $conn->prepare("INSERT INTO test_favorites (What, OId, UserId, Value) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE Values(Value)=Values(Value)*-1;"); $sql->bind_param('sisi',$posttype, $postid,$sessionuserid,1); –  Apr 13 '17 at 07:23
  • Okay, you again wrote it incorrect. `ON DUPLICATE KEY UPDATE value = value * -1` – Alexey Apr 13 '17 at 07:24
  • Thank you. I seem to get closer, but it just adds one new row, not updates the exisiting one. –  Apr 13 '17 at 07:32
  • So I have once again changed the SQL with your help: $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); But still it just adds one new row instead of editing the existing one. –  Apr 13 '17 at 07:58
  • In that case check the indices. If `on duplicate key update ` wasn't triggered it means values you insert weren't present in table... – Alexey Apr 13 '17 at 08:01
  • You are absolutely right! I changed `What` to Char(3) and Unique and that solved it. BUT (!), now i got it to update, so it multiplies it with -1. But when the `Value` is -1 it will not go back to 1. –  Apr 13 '17 at 08:16
  • So let's clarify it - if you need to multiply value of `value` column that is currently in table it's `ON DUPLICATE KEY UPDATE value = value * -1`, if you want to multiply value of `value` column that you are __currently inserting__ - it's `ON DUPLICATE KEY UPDATE value = values(value) * -1` – Alexey Apr 13 '17 at 08:40
  • `$one = 1`. So if row dont exist it should insert value = 1. If it exist it should multiply it with (-1). In that way it will be either 1 or -1. So it is a kind of boolean. (-1) * (-1) = 1 and 1 * (-1) = -1. –  Apr 13 '17 at 08:46
  • I solved it with: $sql = $conn->prepare("INSERT INTO `test_favorites` (`What`, `OId`, `UserId`, `IntValue`) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE IntValue = CASE WHEN IntValue < 1 THEN 1 ELSE 0 END"); –  Apr 13 '17 at 09:02