CODEWITHSUNDEEP
mysqlinsert
Arcadian
Arcadian

Reputation: 1373

MySQL conditional INSERT with parameters

I'm creating a stored procedure to insert values into four tables. The point being to take the SQL out of the PHP to prevent SQL injection and better define the permissions a user has, i.e. rather than being allowed unlimited access to INSERT, SELECT, ALTER and DELETE statements, they are simply only allowed to run the procedure they need to insert the passed variables.

However, if a player already exists, then it is unnecessary to add it again (and impossible considering I've made it unique). The problem arises because there is a one to many relationship between the player table and the coordinates table.

So what I'd like is a conditional INSERT to test if the value already exists and if it does to move on to the next INSERT statement.

Here's the stored procedure:

CREATE PROCEDURE `acdb_extended`.`addAlliedMember` (IN accountNumber VARCHAR(255),
    IN userName VARCHAR(255), IN serverInitial CHAR(1), IN galaxy TINYINT(2),
    IN region TINYINT(2), IN system TINYINT(2), IN astro TINYINT(2), IN level TINYINT(2),
    IN allianceName VARCHAR(255))
BEGIN
    INSERT INTO player (account_number, username)
    VALUES (accountNumber, userName);

    INSERT INTO coordinates (player_ID, server_initial, galaxy, region, system, astro)
    VALUES ((SELECT player_ID FROM player WHERE username = userName), serverInitial,
        galaxy, region, system, astro);

    INSERT INTO jumpgate (player_ID, coordinates_ID, level, usable)
    VALUES ((SELECT player_ID FROM player WHERE username = userName),
        (SELECT c.coordinates_ID FROM coordinates c WHERE c.server_initial = serverInitial
            AND c.galaxy = galaxy AND c.region = region AND c.system = system AND c.astro = astro),
        level, FALSE);

    INSERT INTO relationship (player_ID, ally, alliance_name)
    VALUES ((SELECT player_ID FROM player WHERE username = userName),
        TRUE, allianceName);
END

I think I need ON DUPLICATE KEY but I can't quite figure out its usage.

Thanks in advance for all the help.

Upvotes: 1

Views: 6394

Answers (2)

nosid
nosid

Reputation: 50074

There are several ways to do this with MySQL. A simple solution is to use INSERT IGNORE instead of INSERT. The former does basically nothing if the new row duplicates an existing UNIQUE INDEX or PRIMARY KEY value in the table. See the MySQL documentation on INSERT Syntax for more information.

You can also use the syntax INSERT INTO ... SELECT ... which offers more flexibility. Let's make a simple example. The following statements basically do the same thing:

INSERT INTO foobar (foobar_id,display_name) VALUES (1,'one');

INSERT INTO foobar (foobar_id,display_name)
    SELECT 1,'one' FROM dual;

If you change the SELECT query to detect duplicate entries in foobar, you get the desired behavior. See the MySQL documentation on INSERT ... SELECT Syntax for more details.

Edit: This is works within stored procedures and with the parameters of the stored procedure:

CREATE PROCEDURE sp_foobar(IN _foobar_id int, IN _display_name varchar(255))
BEGIN
    INSERT INTO foobar (foobar_id, display_name)
        SELECT _foobar_id, _display_name
        FROM dual;
END;

Upvotes: 1

Arion
Arion

Reputation: 31249

Can't you do it like this maybe:

IF NOT EXISTS(SELECT NULL FROM player WHERE account_number=accountNumber AND username=userName)
BEGIN
  INSERT INTO player (account_number, username)
  VALUES (accountNumber, userName);

  INSERT INTO coordinates (player_ID, server_initial, galaxy, region, system, astro)
  VALUES ((SELECT player_ID FROM player WHERE username = userName), serverInitial,
      galaxy, region, system, astro);
END

Upvotes: 0

Related Questions