I'm using a stored procedure to (try to) write to 3 different tables in MYsql to track ping-pong data and show cool statistics.
So I'm a complete noob to MySQL (and StackOverflow) and haven't really done any sort of database language before so all of this is pretty new to me. I'm trying to make a stored procedure that writes ping-pong stats that come from Ignition(I'm fairly certain that Ignition isn't the problem. It's telling me the writes failed so I think it's a problem with my stored procedure).
I currently have one stored procedure that writes to the players table and can add wins, losses, and total games played when a button is pressed. My problem now is that I want to add statistics where I can track the score and who played against who so I could make graphs and stuff.
This stored procedure is supposed to search through the pingpong table to find if the names passed have played against each other before so I can find the corresponding MatchID. If the players haven't played before, then it should create a new row with a new MatchID(This is the key so it should be unique every time). Once I have the MatchID, I can then figure out how many games the players have played against each other before, what the score was, and who beat who and stuff like that.
Here's what I've written and MySQL says it's fine, but obviously it's not working. I know it's not completely finished but I really need some guidance since this is my second time doing anything with MySQL or and database language for that matter and I don't think this should be failing when I test any sort of write.
CREATE DEFINER=`root`@`localhost` PROCEDURE `Matchups`(
#these are passed from Ignition and should be working
IN L1Name VARCHAR(255), #Player 1 name on the left side
IN L2Name VARCHAR(255), #Player 2 name on the left side
IN R1Name VARCHAR(255), #Player 3 name on the right side
IN R2Name VARCHAR(255), #Player 4 name on the right side
IN TWOvTWO int, #If this is 1, then L1,L2,R1,R2 are playing instead of L1,R1
IN LeftScore int,
IN RightScore int)
BEGIN
DECLARE x int DEFAULT 0;
IF((
SELECT MatchupID
FROM pingpong
WHERE (PlayerL1 = L1Name AND PlayerR1 = R1Name) OR (PlayerL1 = R1Name AND PlayerR1 = L1Name)
)
IS NULL) THEN
INSERT INTO pingpong (PlayerL1, PlayerL2, PlayerR1, PlayerR2) VALUES (L1Name, L2Name, R1Name, R2Name);
INSERT INTO pingponggames (MatchupID, Lscore, Rscore) VALUES ((SELECT MatchupID
FROM pingpong
WHERE (PlayerL1 = L1Name AND PlayerR1 = R1Name) OR (PlayerL1 = R1Name AND PlayerR1 = L1Name)), LeftScore, RightScore);
END IF;
END
Here are what my tables currently look like:
pingpong
PlayerL1 | PlayerL2 | PlayerR1 | PlayerR2 | MatchupID
-----------------------------------------------------
L1 | NULL | R1 | NULL | 1
L1 | NULL | L2 | NULL | 3
L1 | NULL | R2 | NULL | 4
L1 | NULL | test2 | NULL | 5
pingponggames
GameID | MatchupID | LScore | RScore
------------------------------------------
1 | 1 | NULL | NULL
pingpongplayers
Name | TotalWins | TotalLosses | GamesPlayed
-----------------------------------------------------
L1 | 8 | 5 | NULL
L2 | 1 | 1 | NULL
R1 | 1 | 6 | 7
R2 | 1 | 1 | NULL
test2 | 1 | 0 | 1
test1 | 0 | 0 | 0
Explained some features, If needed more I need more info
CREATE DEFINER=`root`@`localhost` PROCEDURE `Matchups`(
#these are passed from Ignition and should be working
IN L1Name VARCHAR(255), #Player 1 name on the left side
IN L2Name VARCHAR(255), #Player 2 name on the left side
IN R1Name VARCHAR(255), #Player 3 name on the right side
IN R2Name VARCHAR(255), #Player 4 name on the right side
-- what will be the INPUT other than 1? It's to notice doubles or singles right? so taking 0 as single & 1 as doubles
IN TWOvTWO INT, #If this is 1, then L1,L2,R1,R2 are playing instead of L1,R1
IN LeftScore INT,
IN RightScore INT)
BEGIN
DECLARE x INT DEFAULT 0; # i guess you are using it in the sp
DECLARE v_matchupid INT; #used int --if data type is different, set as MatchupID column datatype
DECLARE inserted_matchupid INT; -- use data type based on your column MatchupID from pingpong tbl
IF(TWOvTWO=0) THEN -- for singles
#what is the need of this query? to check singles or doubles? Currently it search for only single from what you have written, will change according to that
SELECT MatchupID INTO v_matchupid
FROM pingpong
WHERE L1Name IN (PlayerL1, PlayerR1) AND R1Name IN (PlayerL1, PlayerR1); # avoid using direct name(string) have a master tbl for player name and use its id to compare or use to refer in another tbl
# the if part checks is it new between them and insert in both tbls
IF(v_matchupid IS NULL) THEN
INSERT INTO pingpong (PlayerL1, PlayerR1) VALUES (L1Name, R1Name);
SET inserted_matchupid=LAST_INSERT_ID();
INSERT INTO pingponggames (MatchupID, Lscore, Rscore) VALUES (inserted_matchupid, LeftScore, RightScore);
/*
Once I have the MatchID, I can then figure out how many games the players have played against each other before
A: this will not work for new matchup since matchupid is created now
*/
# so assuming if match found update pingponggames tbl with matched matchupid.. i leave it up to you
ELSE
UPDATE pingponggames SET Lscore=LeftScore, Rscore=RightScore WHERE MatchupID=v_matchupid;-- you can write your own
END IF;
-- for doubles
ELSE # assuming the possibilities of TWOvTWO will be either 0 or 1 if more use "elseif(TWOvTWO=1)" for this block as doubles
SELECT MatchupID INTO v_matchupid
FROM pingpong
# Note: If player name are same it will be difficult so better use a unique id as reference
WHERE L1Name IN (PlayerL1, PlayerL2, PlayerR1, PlayerR2) AND
L2Name IN (PlayerL1, PlayerL2, PlayerR1, PlayerR2) AND
R1Name IN (PlayerL1, PlayerL2, PlayerR1, PlayerR2) AND
R2Name IN (PlayerL1, PlayerL2, PlayerR1, PlayerR2);
IF(v_matchupid IS NULL) THEN
INSERT INTO pingpong (PlayerL1, PlayerL2, PlayerR1, PlayerR2) VALUES (L1Name, L2Name, R1Name, R2Name);
SET inserted_matchupid=LAST_INSERT_ID();
INSERT INTO pingponggames (MatchupID, Lscore, Rscore) VALUES (inserted_matchupid, LeftScore, RightScore);
ELSE
UPDATE pingponggames SET Lscore=LeftScore, Rscore=RightScore WHERE MatchupID=v_matchupid;-- you can write your own
END IF;
END IF;
END