Is it possible to use IF NOT EXIST with an INSERT statement, where you are inserting variable number of rows?

Question

I have this method which takes in an array of integers and creates a row in the DB for each one of them. The array can be variable length, can have 1 element, or 100. I only want to insert if the record does not exist already, for each of the integer in $data array. So if $data = [1,2,3] and in the database record for 2 already exists, it will not add another one, but it will add new ones for 1 and 3. So if you were to run this same query again, nothing will be inserted.

This is the current syntax:

public function addUserJobAttributes($account, $data)
{

    $data = [1,2,3,4,5,6,7];

    try {

        $qstring = '';
        $qarray = [];
        $i = 1;

        foreach ($data as $item) {

            $qstring .= '(:userId_' .$i. ', :jobAttributrValueId_'.$i.'),';

            $qarray[':userId_'.$i] = $account->id;
            $qarray[':jobAttributrValueId_'.$i] = $item;
            $i++;

        }

        $qstring = rtrim($qstring, ',');

        $db = new \Db();

        $query = $db->conn->prepare("INSERT INTO userjobattributes (userId, jobAttributeValueId)
        VALUES $qstring");

        $query->execute($qarray);

        $db = null;

    } catch (\Exception $e) {

        $this->logger->log($e->getMessage());
        $db = null;
        throw new \ELException('Query could not be executed', 500, null, 'Dodgy SQL');

    }

}

Create a unique key on your table, then perform an INSERT IGNORE INTO — Scuzzy, May 17 '18 at 13:32
you must simply put an compound unique constraint on both columns: `userId` and `jobAttributeValueId` — Mihai Matei, May 17 '18 at 13:35
Possible duplicate of [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — , May 17 '18 at 13:37
@Scuzzy the problem with that is `IGNORE` will ignore *other* errors, too... like data too long for a column. — Michael - sqlbot, May 18 '18 at 01:16
Perhaps then a `REPLACE INTO` or `ON DUPLICATE KEY UPDATE` would be recommended? — Scuzzy, May 18 '18 at 01:44

Mike · Answer 1 · 2018-05-17T14:08:52.183

It's not clear what data you are trying to not duplicate. I'm assuming it's $account->id. So here is embedded PHP code that does not require modification of your SQL database. Note it will perform as many queries as sizeof($data). I suppose you could modify the SELECT statement to match the data you don't want to duplicate.

public function addUserJobAttributes($account, $data){

   $data = [1,2,3,4,5,6,7];

   try {

   $qstring = '';
   $qarray = [];
   $i = 1;

   $db = new \Db();

   foreach ($data as $item) {

       // check for existing row
       $sql = "SELECT COUNT(*) FROM userjobattributes WHERE userId = ".$account->id;
       $stmt = $db->con->prepare($sql);
       $stmt->execute();

       // if this id exists, skip it
       if ($stmt->fetchColumn() > 0) {
           continue;
       }

       $qstring .= '(:userId_' .$i. ', :jobAttributrValueId_'.$i.'),';

       $qarray[':userId_'.$i] = $account->id;
       $qarray[':jobAttributrValueId_'.$i] = $item;
       $i++;

   }

   $qstring = rtrim($qstring, ',');

   $query = $db->conn->prepare("INSERT INTO userjobattributes (userId, jobAttributeValueId) VALUES $qstring");

   $query->execute($qarray);

   $db = null;

   } catch (\Exception $e) {

       $this->logger->log($e->getMessage());
       $db = null;
       throw new \ELException('Query could not be executed', 500, null, 'Dodgy SQL');

   }
}

Is it possible to use IF NOT EXIST with an INSERT statement, where you are inserting variable number of rows?

1 Answers1