...

View Full Version : Multi Variables Returned From Stored Procedure



ubh
04-21-2013, 11:02 AM
Hey guys, I have a need to make a stored procedure that on one page, will return be back two variable that I can then use later on in my php code.

Here is what I have so far:


CREATE PROCEDURE registerPlayer(IN v_clientType INT(11), IN v_email VARCHAR(120), IN v_gamerName VARCHAR(120), IN v_password CHAR(120), IN v_isGuest INT(1))
BEGIN

INSERT INTO players (portalOrGuest, email, gamerName, password)
VALUES(v_clientType,v_email,v_gamerName,v_password);
SET @v_playerId = last_insert_id();

IF v_isGuest = '1' THEN SET v_gamerName = 'Guest'+@v_playerId;
UPDATE players SET gamerName=v_gamerName WHERE id=@v_playerId;
SELECT @v_playerId;
SELECT v_gamerName;
END IF;

END


I've heard that if I need this to be a procedure and not a function, I can use SELECT in place of RETURN, but I'm just not seeing any examples how I then utilize the SELECT variables back out in my php after calling the procedure.

Any examples how I get @v_playerId; and v_gamerName; back out in the my php as php variables?

Old Pedant
04-22-2013, 07:27 AM
The easiest way is to just SELECT them. But in a single SELECT.

So change

SELECT @v_playerId;
SELECT v_gamerName;

to something like


SELECT @v_playerId AS playerid, v_gamerName AS gamename;

And then in your PHP code you can just treat those the same as from any SQL SELECT.

However, it would be a good idea to *always* return two values.

So you might want to do

ELSE
SELECT -1 AS playerid, '' AS gamename;
END IF;
so that your PHP code can easily detect whether you are getting back a real playerid or, essentially, a message that says "nope, no playerid here".

Old Pedant
04-22-2013, 08:13 AM
In other words, your PHP code will be something like:


$sql = "CALL registerPlayer($clienttype,$email,$game,$pwd,$isguest)";
$result = mysql_query($sql);
if ( $record = mysql_fetch_assoc($result) )
{
$playerid = $record["playerid"];
if ( $playerid < 0 )
{
... no playerid assigned ...
} else {
.... playerid assigned ...
}
}

Or something along those lines. (I don't use PHP, so that's an approximation.)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum