How to use 'INSERT INTO IGNORE' with an OR clause or 'NOT EXISTS' in mysql

Question

I have a table named as equipment and table schema is as follows

+----+-----------------+-------------------+--+----------------------+
| id |    equipment    | cat_id(Default:1) |  | is_active(Default:1) |
+----+-----------------+-------------------+--+----------------------+
|  1 | Air Compressor  |                 1 |  |                    1 |
|  2 | Plate Compactor |                 1 |  |                    1 |
|  3 | Hammer          |                 1 |  |                    1 |
|  4 | TNT             |                 1 |  |                    1 |
+----+-----------------+-------------------+--+----------------------+

I want to "insert records into table if the id and equipment does not exists in the table". And I have done it using following code

        /* equipment is an associative array 
            Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [name] => Air Compressor
                    )

                [1] => Array
                    (
                        [id] => 2
                        [name] => Plate Compactor
                    )

                [2] => Array
                    (
                        [id] => 3
                        [name] => Hammer
                    )
            )
        */
        foreach ($equipment as $key)
        {
            $name = $key['name'];
            $id = $key['id'];
            $equipment_query = $this->conn->prepare("INSERT IGNORE INTO equipment_master (id,equipment) VALUES (:equipment_id,:equipment_name)");
            $equipment_query->bindParam('equipment_name', $name);
            $equipment_query->bindParam('equipment_id', $id);
            $equipment_query->execute();
        }

But problem while inserting the rows is that, I don't want to use id field in the query because it is AUTO INCREMENTAL and Primary Key.

Problem would occur when, if I'm trying to insert id=>4 equipment=>Helmet. This record will not be inserted because id=>4 is already in the table.

Is there a way to check if id is present already in the table, if not then insert the row. I tries it using NOT EXISTS query but my code seems to insert each row repetitively (4 to be exact)

        foreach ($equipment as $key)
        {
            $name = $key['name'];
            $id = $key['id'];

            $equipment_query = $this->conn->prepare("INSERT INTO equipment_master (equipment) SELECT :equipment_name FROM equipment_master AS tmp WHERE NOT EXISTS (SELECT id FROM equipment_master WHERE id =:equipment_id OR equipment=:equipment_name)");
            $equipment_query->bindParam('equipment_name', $name);
            $equipment_query->bindParam('equipment_id', $id);
            $equipment_query->execute();
        }

Any help is much appreciated. Thanks :)

Answer 1

1. Create unique index on field: equipment

   ALTER TABLE equipment ADD UNIQUE equipment_unique (equipment);

2. do insert ignoring errors:

   foreach ($equipment as $key)
   {
       $name = $key['name'];
       $equipment_query = $this->conn->prepare("INSERT IGNORE INTO equipment_master (equipment) VALUES (:equipment_name)");
       $equipment_query->bindParam('equipment_name', $name);
       $equipment_query->execute();
   }
   

but if You want replace record that has same id so You can use:

foreach ($equipment as $key)
{
    $name = $key['name'];
    $id = $key['id'];

    $equipment_query = $this->conn->prepare("REPLACE INTO equipment_master (id, equipment) VALUES (:equipment_id, :equipment_name)");
    $equipment_query->bindParam('equipment_name', $name);
    $equipment_query->bindParam('equipment_id', $id);
    $equipment_query->execute();
}

Answer 2

You have to create unique index on id and equipment column to use the "INSERT IGNORE".

Here is the query to create index:

ALTER TABLE `equipment ` ADD UNIQUE `unique_index`(`id`, `equipment );