StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POConverted PHP-Code to (My)SQL stored procedure
text
Body
copied!<p>I've just converted the following PHP code to a MySQL stored procedure. There is no obvious syntax error as I could execute it with PHPMyAdmin. I can see it with</p> <pre><code>SELECT routine_definition FROM information_schema.routines WHERE routine_schema = 'chess'; </code></pre> <p>As this is the first time I've written a stored procedure, I would like to know </p> <ul> <li>Does the stored procedure do what I think it does? (See flowchart in "What it should do")</li> <li>Is the stored procedure plain SQL (to some standard) or will I only be able to use this with MySQL databases? What is MySQL specific? Could I get rid of that?</li> <li>Is there any way I can improve this stored procedure? Are there best practices that I broke?</li> <li>Do I have to sanitize input data when I use stored procedures?</li> </ul> <p>Here is a short <a href="https://github.com/MartinThoma/community-chess/blob/master/documentation/chess-database-schema.png" rel="nofollow noreferrer">overview over the database</a> and <a href="https://github.com/MartinThoma/community-chess" rel="nofollow noreferrer">all code</a>. But I hope this is not necessary to answer my questions.</p> <h2>What it should do</h2> <p><img src="https://i.stack.imgur.com/9rtNQ.png" alt="enter image description here"></p> <h2>New stored procedure</h2> <pre><code>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 ; </code></pre> <h2>New PHP code</h2> <pre><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); } </code></pre> <h2>What it prints out</h2> <pre><code>Return Value 1################ stmt PDOStatement Object ( [queryString] => CALL ChallengeUser(?,?,@startedGamePlayerUsername, @startedGameID,@incorrectID, @alreadyChallengedPlayer,@alreadyChallengedGameID) ) ################ row Array ( [startedGamePlayerUsername] => test ) </code></pre> <h2>What it should do</h2> <p>It should have created a new entry in the table <code>chess_games</code>. But there is no new entry and there is no value for <code>incorrectID</code> or <code>alreadyChallengedPlayer</code>. So I think I made a mistake.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload