Converted PHP code to (My) SQL stored procedure

Question

I just converted the following PHP code to a MySQL stored procedure. There is no explicit syntax error since I could execute it using PHPMyAdmin. I see it with

SELECT routine_definition FROM information_schema.routines WHERE routine_schema = 'chess'; 

Since this is the first time I wrote a stored procedure, I would like to know

• Does the stored procedure do what I think? (See the Flowchart in “What Should It Do”)
• Is the stored procedure regular SQL (to some standard) or can I only use this with MySQL databases? What is MySQL? Can i get rid of this?
• Is there a way to improve this stored procedure? Are there any better methods that I violated?
• Do I need to sanitize input when using stored procedures?

Here is a quick overview of the database and all the code . But I hope there is no need to answer my questions.

What should he 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` AS startedGamePlayerUsername FROM chess_users WHERE `user_id` = challengedUserID AND `user_id` != currentUserID LIMIT 1; IF startedGamePlayerUsername IS NOT NULL THEN SELECT `id` FROM `chess_games` WHERE `whiteUserID` = currentUserID AND `blackUserID` = challengedUserID AND `outcome` = -1 LIMIT 1; IF id IS NULL THEN SELECT `softwareID` AS `whitePlayerSoftwareID` FROM chess_users WHERE `user_id`=currentUserID LIMIT 1; SELECT `softwareID` AS `blackPlayerSoftwareID` FROM chess_users WHERE `user_id`=challengedUserID LIMIT 1; INSERT INTO `chess_games` (`tournamentID`, `whiteUserID`, `blackUserID`, `whitePlayerSoftwareID`, `blackPlayerSoftwareID`, `moveList`) VALUES (NULL, currentUserID, challengedUserID, whitePlayerSoftwareID, blackPlayerSoftwareID, ""); /* Get the id of the just inserted tuple */ SELECT `id` AS startedGameID FROM chess_games WHERE `whiteUserID` = whitePlayerSoftwareID AND `blackUserID` = blackPlayerSoftwareID AND `whitePlayerSoftwareID` = whitePlayerSoftwareID AND `blackPlayerSoftwareID` = blackPlayerSoftwareID AND `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 does he print

 Return Value 1################ stmt PDOStatement Object ( [queryString] => CALL ChallengeUser(?,?,@startedGamePlayerUsername, @startedGameID,@incorrectID, @alreadyChallengedPlayer,@alreadyChallengedGameID) ) ################ row Array ( [startedGamePlayerUsername] => test ) 

What should he do

He had to create a new entry in the chess_games table. But there is no new entry and there is no value for incorrectID or alreadyChallengedPlayer . Therefore, I think I made a mistake.

Answer 1

Sorry for such dirty code - I'm at work = not much time, but should help you. You must add data to the tables: USERS and SOFTWARE. There was a problem processing NULL and passing the result of the request to a variable.

EDIT: fix for query "get id of just 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` INTO startedGamePlayerUsername FROM chess_users WHERE `user_id` = challengedUserID AND `user_id` != currentUserID LIMIT 1; IF startedGamePlayerUsername IS NOT NULL THEN SELECT `id` INTO TMP_ID FROM `chess_games` WHERE `whiteUserID` = currentUserID AND `blackUserID` = challengedUserID AND `outcome` = -1 LIMIT 1; -- here was bad NULL handling IF TMP_ID IS NULL OR TMP_ID='' THEN SELECT `softwareID` INTO TMP_W_PLAYER FROM chess_users WHERE `user_id`=currentUserID LIMIT 1; SELECT `softwareID` INTO TMP_B_PLAYER FROM chess_users WHERE `user_id`=challengedUserID LIMIT 1; INSERT INTO `chess_games` (`tournamentID`, `whiteUserID`,`blackUserID`, `whitePlayerSoftwareID`,`blackPlayerSoftwareID`, `moveList`) SELECT NULL, currentUserID, challengedUserID, TMP_W_PLAYER, TMP_B_PLAYER, ""; /* Get the id of the just inserted tuple */ SELECT max(`id`) INTO startedGameID FROM chess_games WHERE `whiteUserID` = currentUserID AND `blackUserID` = challengedUserID AND `whitePlayerSoftwareID` = TMP_W_PLAYER AND `blackPlayerSoftwareID` = TMP_B_PLAYER AND `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 ;