Hunter Mitchell Hunter Mitchell - 6 days ago 5
MySQL Question

IF ELSE Not working in MySQL?

I have been stuck on this for about 30 minutes and I dont know if im overlooking anything, but i keep getting the following error (mostly meaning my SQL is invalid):


Fatal error: Call to a member function bind_param() on a non-object...


SQL:

IF (SELECT value FROM votes WHERE thread = ? AND owner = ?) THEN
UPDATE votes SET (value = ?) WHERE thread = ? AND owner = ?
ELSE
INSERT INTO votes (thread, owner, value) VALUES (?, ?, ?)
END IF


PHP:

$stmt = $this->database->prepare("IF (SELECT value FROM votes WHERE thread = ? AND owner = ?) THEN UPDATE votes SET (value = ?) WHERE thread = ? AND owner = ? ELSE INSERT INTO votes (thread, owner, value) VALUES (?, ?, ?) END IF");
$stmt->bind_param("dddddddd", $this->id, $userId, $value, $this->id, $userId, $this->id, $userId, $value);
$stmt->execute();


According to the MySQL documentation, I should be correct?

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

Answer

The IF statement only works in programming blocks -- stored procedures, functions, and triggers.

You can do what you want with on duplicate key update:

INSERT INTO votes (thread, owner, value)
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE value = VALUES(value);

For this to work, you should define a unique key/constraint on thread/owner:

create unique index unq_votes_thread_owner on votes(thread, owner);
Comments