Parag Jadhav Parag Jadhav - 1 month ago 5
MySQL Question

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

I have a table name as

equipment
and table structure 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 I can 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 appreciated. Thanks :)

Answer
  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();
}
Comments