MySql Condition not working in trigger

Question

I am developing a social networking website, where I have 3 tables - 1st for users(userid- pk,auto increment,username,etc.), 2nd for Album(albumid - auto increment,userid - of person who owns the album) and 3rd for photo(photoid - auto increment,albumid -where photo belongs to,path- to photo in disk). Now, Before inserting into photo, my trigger checks if the user owns the album given by album id. The trigger is created using MySql workbench which gives the following:

USE `mydb`;
DELIMITER $$
CREATE TRIGGER `photo_BINS` BEFORE INSERT ON `photo` FOR EACH ROW
BEGIN
 DECLARE found_it INT;
 SELECT 1 INTO found_it FROM album WHERE albumid= NEW.albumid and userid=@userid;
 IF found_it!=1 then
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'User does not own album';
END IF;
END

I am passing the value to @userid by php as follows:-

$con=NULL;$stmt=NULL;
$con=mysqli_connect('localhost','username','pswd','mydb');
$stmt= mysqli_prepare($con,'set @userid=?');
mysqli_stmt_bind_param($stmt,'i',$user);
mysqli_stmt_execute($stmt);
$stmt= mysqli_prepare($con,'insert into photo(albumid,path) values(?,?)');
mysqli_stmt_bind_param($stmt,'is',$albumid,$path);
mysqli_stmt_execute($stmt)'
$pid=mysqli_insert_id($con);

The problem is that everytime the value gets entered. Even when I oppose the condtion i.e. fount_it=1 , the insert always takes place. Other variant I've tried is:

if(SELECT 1 FROM album WHERE albumid= NEW.albumid and userid=@userid)!=1 THEN

... still the insert always takes place. Please help.

Answer 1

Found_it is null when declared. If no row matches the query that looks for album and userid found_it is not updated, i.e. it is still null. The test

found_it!=1 (use found_it <> 1 instead)

therefor evaluates to NULL ( NULL <> 1 => NULL ). You can prevent this by a default constraint in your declaration:

DECLARE found_it INT DEFAULT 0;

Now the test will evaluate to FALSE.

• Praveen R Keshri: Thanks a lot. I was really struggling. You solved it finally.
• Lennart - Slava Ukraini: Your welcome, if the answer was satisfactory don't forget to mark as a solution.
• Praveen R Keshri: Done.. :-) Well, I have another question. Please see if you can help. dba.stackexchange.com/questions/76476/… . Thanx in advance