asked on

stored procedure returning false on execute

HI,

I am trying to create a SQL procedure but i keep getting no results. I am using php pdo to call the procedure and on execute it returns false;

DELIMITER $$

CREATE PROCEDURE add_activity_result(
	IN var_stat VARCHAR(1),		
	IN var_squad INT(11),		
	IN var_e INT(11),		
	IN var_me INT(11),		
	IN var_res varchar(200)	

)
BEGIN

	SET @squad = NULL, @res = NULL;
	
	SELECT 
		@squad = r.squadId, @res = r.result
	FROM `events-results` AS r
	JOIN events AS a ON a.id = r.eventId AND a.status = var_stat
	JOIN group AS s ON a.eventMember
	WHERE eventId = var_act AND a.id IS NOT NULL;
	
	IF(@squad IS NULL) THEN
		
		INSERT INTO `events-results` ( eventId, creatorId, date, squadId, leagueId, result ) VALUES ( var_act, var_me, NOW(), var_squad, var_league, var_res );
		
		INSERT INTO users_activity_log ( date, playerId, table_name, table_id, details ) VALUES ( NOW(), var_me, "events-results", LAST_INSERT_ID(), "Added a result to an activity" );

	ELSEIF(@squad = var_squad) THEN
	
		UPDATE `events-results` SET score_home = var_home, score_away = var_away WHERE squadId = var_squad AND eventId = var_act;
		
		INSERT INTO users_activity_log ( date, playerId, table_name, table_id, details ) VALUES ( NOW(), var_me, "events-results", var_act, "Updated a result to an activity" );
	
	ELSEIF(@res != var_res) THEN
		
		UPDATE `events-results` SET contested = NOW() WHERE squadId = @squad AND eventId = var_act;
		
		INSERT INTO `events-resolutions` ( eventId, date, creatorId, result ) VALUES ( var_act, NOW(), var_me, var_res );
		
	ELSE
	
		SELECT "Activity results have already been added";
		
	END IF;

END $$

DELIMITER ;

Open in new window

if also tried changing this

	SET @squad = NULL, @res = NULL;
	
	SELECT 
		@squad = r.squadId, @res = r.result
...

Open in new window

to this

	SELECT 
		@squad := r.squadId, @res := r.result
...

Open in new window

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_8034623

ASKER

Hi,

Thanks!

I am using phpmyadmin as well as using try catch blocks in php.

My initial select statement was wrong. i have now amended it to fetch the event and join everything else to it

SELECT 
		@squad:= r.squadId, @res := r.result
	FROM  events AS a 
	left join`events-results` AS r ON r.eventId = a.id
	JOIN group AS s ON a.eventMember
	WHERE a.id = r.eventId AND a.status = var_stat

Open in new window

thanks for the help