I want to check if two user supplied values match a row in the database, and if they do, change an enum value from 0 to 1.
I know I can do this in two queries, but I'd rather do it in one. The only thing I've been ale to find is how to update a row if it exists, and create it if it doesn't. However, I don't want this, as if the supplied values are incorrect the script should exit.
Can I use IF EXISTS (SELECT x FROM x where y = y AND z = z) UPDATE ...
Will this work? And if so, how can I check if it executed within PHP?
I think you would just use where with update:
update x
set enumcol = 1
where y = $y and z = $z;
This updates rows where values match. If no rows match, then no rows are updated.
Wouldnt that just be done with a standard condition? Like:
UPDATE my_table
SET enum_field=1
WHERE one_field=[first_value] AND another_field=[second_value]
Maybe you also would have to match against a user ID or something:
UPDATE my_table
SET enum_field=1
WHERE one_field=[first_value] AND another_field=[second_value] AND id=[user_id]
In more complex cases this answer might help: