SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting records from Stored Procedures?

    I have made an stored procedure like this:
    Code:
    DELIMITER //
    DROP PROCEDURE IF EXISTS `get_games_menu` //
    
    CREATE PROCEDURE `get_games_menu`
    (
    IN currplayer INT,
    OUT gameid INT,
    OUT lastdraw INT,
    OUT playertiles TEXT,
    OUT oppnation INT,
    OUT oppname VARCHAR(100),
    OUT fdraw INT,
    OUT playerpoints INT,
    OUT infopop INT,
    OUT oppTiles TEXT,
    OUT tabletiles TEXT,
    OUT playerturn INT,
    OUT newtabletiles TEXT,
    OUT profileimg VARCHAR(255)
    )
    BEGIN
    SELECT 
        gp.fk_game_id
      , gp.player_tiles
      , gp.infoPop
      , gp.first_draw
      , gp.player_points
      , g.table_tiles
      , g.new_tiles
      , gp2.last_draw_type
      , gp2.player_turn
      , gp2.player_tiles
      , u.country
      , u.username
      , u.profileimg
    INTO 
        gameid
      , playertiles
      , infopop
      , fdraw
      , playerpoints
      , tabletiles
      , newtabletiles
      , lastdraw
      , playerturn
      , oppTiles
      , oppnation
      , oppname
      , profileimg
    FROM nf_gameplayer gp
    INNER JOIN nf_games g 
    ON gp.fk_game_id = g.game_id
    INNER JOIN nf_gameplayer gp2
    ON  gp.fk_game_id = gp2.fk_game_id
    AND gp.fk_player_id != gp2.fk_player_id
    INNER JOIN nf_users u
    ON gp2.fk_player_id = u.id
    WHERE gp.fk_player_id = currplayer 
    AND   g.invite=0 
    AND   g.deleteby != currplayer 
    ORDER BY g.lastdraw;
    END //
    DELIMITER ;
    And I am trying to get records from it like this:
    PHP Code:
    $mysqli = new mysqli('localhost''root''''mytest');

    $rs $mysqli->query'CALL get_games_menu(1, @gameid,@playertiles,@infopop,@fdraw,@playerpoints,@tabletiles,@newtabletiles,@lastdraw,@playerturn,@opptiles,@oppnation,@oppname,@profileimg)' );
    $rs $mysqli->query'SELECT @gameid,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
    while(
    $row $rs->fetch_object())
    {
        echo 
    $row['gameid']-'<br>';

    But I am not getting any records. When doing a normal mysql I get 9 records...

    What am I doing wrong. (New with stored procedures).

    Thanks in advance ;-)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    your query might return 9 rows, but the way you've designed your stored proc, it can only return one set of OUT variable values for each CALL

    why did you choose to use a stored proc here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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