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
    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
    29,523
    Thanks
    89
    Thanked 4,931 Times in 4,892 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".
    Be yourself. No one else is as qualified.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,523
    Thanks
    89
    Thanked 4,931 Times in 4,892 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.)
    Be yourself. No one else is as qualified.


  •  

    Posting Permissions

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