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.