Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts

    Multi Variables Returned From Stored Procedure

    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:

    Code:
    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?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    The easiest way is to just SELECT them. But in a single SELECT.

    So change
    Code:
        SELECT @v_playerId;
        SELECT v_gamerName;
    to something like
    Code:
        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
    Code:
        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".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    In other words, your PHP code will be something like:
    Code:
    $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.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •