mysql - Converted PHP-Code to (My)SQL stored procedure -

i've converted following php code mysql stored procedure. there no obvious syntax error execute phpmyadmin. can see with

select routine_definition information_schema.routines routine_schema = 'chess'; 

as first time i've written stored procedure, know

• does stored procedure think does? (see flowchart in "what should do")
• is stored procedure plain sql (to standard) or able use mysql databases? mysql specific? rid of that?
• is there way can improve stored procedure? there best practices broke?
• do have sanitize input data when use stored procedures?

here short overview on database , all code. hope not necessary answer questions.

what should do

new stored procedure

delimiter // create procedure challengeuser(    in challengeduserid int,    in currentuserid int,    out startedgameplayerusername varchar(255),    out startedgameid int,    out incorrectid bit,    out alreadychallengedplayer bit,    out alreadychallengedgameid int   )     begin         select `username` startedgameplayerusername chess_users           `user_id` = challengeduserid                 , `user_id` != currentuserid limit 1;          if startedgameplayerusername not null             select `id` `chess_games`                `whiteuserid` = currentuserid                  , `blackuserid` = challengeduserid                  , `outcome` = -1 limit 1;             if id null                 select `softwareid` `whiteplayersoftwareid`                    chess_users                  `user_id`=currentuserid limit 1;                 select `softwareid` `blackplayersoftwareid`                    chess_users                    `user_id`=challengeduserid limit 1;                 insert `chess_games` (`tournamentid`, `whiteuserid`,                                    `blackuserid`, `whiteplayersoftwareid`,                                       `blackplayersoftwareid`, `movelist`)                      values (null, currentuserid, challengeduserid,                              whiteplayersoftwareid,                              blackplayersoftwareid, "");                  /* id of inserted tuple */                 select `id` startedgameid chess_games                    `whiteuserid` = whiteplayersoftwareid                      , `blackuserid` = blackplayersoftwareid                      , `whiteplayersoftwareid` = whiteplayersoftwareid                      , `blackplayersoftwareid` = blackplayersoftwareid                      , `movelist` = "" limit 1;             else                 set alreadychallengedplayer = 1;                 set alreadychallengedgameid = id;             end if;         else             set incorrectid = 1;         end if;     end // delimiter ; 

new php code

function challengeuser2($user_id, $t) {     global $conn;     $stmt = $conn->prepare("call challengeuser(?,?,@startedgameplayerusername,".                        ."@startedgameid,@incorrectid,"                        ."@alreadychallengedplayer,@alreadychallengedgameid)");     $test = user_id;     $stmt->bindparam(1, $user_id);     $stmt->bindparam(2, $test);     $returnvalue = $stmt->execute();      echo "return value\n";     print_r($returnvalue);     echo "################\n\nstmt\n";     print_r($stmt);      echo "################\n\nrow\n";     $row = $stmt->fetch(pdo::fetch_assoc);     print_r($row); } 

what prints out

return value 1################  stmt pdostatement object (     [querystring] => call challengeuser(?,?,@startedgameplayerusername,        @startedgameid,@incorrectid,        @alreadychallengedplayer,@alreadychallengedgameid) ) ################  row array (     [startedgameplayerusername] => test ) 

what should do

it should have created new entry in table chess_games. there no new entry , there no value incorrectid or alreadychallengedplayer. think made mistake.

sorry such messy code - i'm @ work = no time, should you. have add data tables: users , software. there problem null handling, , passing query result variable.

edit: fix query "get id of inserted tuple"

delimiter $$  drop procedure if exists `challengeuser`$$  create definer=`root`@`localhost` procedure `challengeuser`( challengeduserid int, currentuserid int, startedgameplayerusername  varchar(255), startedgameid int, incorrectid int, alreadychallengedplayer int, alreadychallengedgameid int ) begin  declare tmp_id int default 0; declare tmp_w_player int default 0;     declare tmp_b_player int default 0;       select `username` startedgameplayerusername        chess_users     `user_id` = challengeduserid        , `user_id` != currentuserid limit 1;      if startedgameplayerusername not null          select `id` tmp_id            `chess_games`          `whiteuserid` = currentuserid              , `blackuserid` = challengeduserid              , `outcome` = -1          limit 1;          -- here bad null handling             if tmp_id null or tmp_id=''              select `softwareid` tmp_w_player               chess_users              `user_id`=currentuserid              limit 1;              select `softwareid` tmp_b_player                chess_users              `user_id`=challengeduserid              limit 1;              insert `chess_games`          (`tournamentid`, `whiteuserid`,`blackuserid`, `whiteplayersoftwareid`,`blackplayersoftwareid`, `movelist`)                      select null, currentuserid, challengeduserid, tmp_w_player, tmp_b_player, "";                          /* id of inserted tuple */             select max(`id`) startedgameid chess_games                `whiteuserid` = currentuserid                  , `blackuserid` = challengeduserid                  , `whiteplayersoftwareid` = tmp_w_player                  , `blackplayersoftwareid` = tmp_b_player                 , `movelist` = "";         else             set alreadychallengedplayer = 1;             set alreadychallengedgameid = tmp_id;         end if;     else         set incorrectid = 1;     end if;      select startedgameplayerusername,startedgameid,   incorrectid ,   alreadychallengedplayer ,   alreadychallengedgameid;  end$$  delimiter ;