php - MYSQL Trigger - How to join three tables correctly?

Question

I need help making a MYSQL query for a trigger. What I want to do is whenever "Result" is updated in the Game table I want to check if the result is equal to the "Prediction" for those bets that the game is linked to (they're linked with gameID, a bet has username and gameID as primary key). If the result and prediction is equal then those users who has the same prediction as the result for that particular bet should get an updated "Balance" like this: (Balance + (Odds * BetAmount))

Here is the code i'm trying:

   BEGIN
 IF NEW.result <> OLD.result THEN
    UPDATE User u1
    SET u1.balance = b1.odds * b1.betAmount + u1.balance
    WHERE (SELECT u1.username, b1.username
    FROM User u1, Bet b1, Game g1
    WHERE g1.gameID = b1.gameID
    AND b1.username = u1.username
    AND g1.result = b1.prediction
    END IF;
END

However when running this I get syntax error on line 10 near END IF;END (running it through phpmyadmin) Any advice on how I should format my code?

Here is my database code:

    $table1 = "CREATE TABLE User (
username VARCHAR(30) PRIMARY KEY,
password VARCHAR(30) NOT NULL,
balance DOUBLE
);";
$table2 = "CREATE TABLE Game (
    gameID VARCHAR(30) PRIMARY KEY,
    description VARCHAR(300),
    time VARCHAR (30),
    result VARCHAR(10)
    );";
$table3 = "CREATE TABLE Bet (
    username VARCHAR(30),
    gameID VARCHAR(30),
    prediction VARCHAR(10),
    odds DOUBLE,
    betAmount DOUBLE,
    PRIMARY KEY (username, gameID),
    FOREIGN KEY (username) REFERENCES User(username)
    ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (gameID) REFERENCES Game(gameID)
    ON DELETE CASCADE ON UPDATE CASCADE
    );";

question from:https://stackoverflow.com/questions/65648606/mysql-trigger-how-to-join-three-tables-correctly

Answer 1

Waitting for answers