mardi 26 décembre 2017

MYSQL complex IF NOT EXIST SELECT INSERT CLAUSE

I am making an attempt at writing a MySql conditional statement which then runs a set of commands, I keep coming up with error but enable to understand the issue. I have used recommendations from different topics here but still have no success, my last resort will be to run all queries individually which won't be efficient.

$SQL = "IF NOT EXISTS (SELECT id FROM members WHERE email = '$email' LIMIT 1) THEN

        INSERT INTO members (first, last, email )
        VALUES ('$first_name', '$last_name', '$email');
        SET @USER_ID = LAST_INSERT_ID();

        INSERT INTO babylon (user_id, crew )
        VALUES (@USER_ID, '$crew');

        INSERT INTO movement (badge, date)
        VALUES (@USER_ID, NOW())
        SET @BADGE_ROOM_CODE = LAST_INSERT_ID();

        INSERT INTO messages (thread_id, sender_id)
        VALUES (@BADGE_ROOM_CODE, '@USER_ID');

ELSE

        SELECT id AS USER_ID FROM members WHERE email = '$email' LIMIT 1
        SET @USER_ID = USER_ID;

        INSERT INTO movement (badge, date)
        VALUES (@USER_ID, NOW())
        SET @BADGE_ROOM_CODE = LAST_INSERT_ID();

        INSERT INTO messages (thread_id, sender_id)
        VALUES (@BADGE_ROOM_CODE, @USER_ID);

END IF";

mySQLQuery($SQL);

Aucun commentaire:

Enregistrer un commentaire