Clever Geek Handbook

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

I have a table named equipment

and the 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 a table if id

u equipment

do not exist in the table". And I did it using the 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 the problem with inserting rows is that I don't want to use the field id

in the query because that is AUTO INCREMENTAL

and Primary Key

.

The problem comes up if, if I try to insert id=>4

equipment=>Helmet

. This record will not be inserted because it is id=>4

already in the table.

Is there a way to check if the id

table is already present , if not then insert the row. I'm trying to use it with a query NOT EXISTS

, but my code seems to be iterating over every line (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 greatly appreciated. Thank:)

+3


source to share


3 answers


  • Create a unique index in the field: equipment

    ALTER TABLE equipment

    ADD UNIQUE equipment_unique

    ( equipment

    );

  • Paste to ignore 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 to replace an entry with the same id, 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();
}
+1


source


You need to create a unique index on id and hardware column in order to use "INSERT IGNORE".

Here is a query to create an index:



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


source


As @ num8er suggested, this solution worked for me as well. And another workaround for the query not exists

is (without creating an index on equipment

) like this:

INSERT INTO equipment_master (equipment) SELECT * FROM (SELECT 'Plate Compactor12') AS tmp WHERE NOT EXISTS ( SELECT equipment FROM equipment_master WHERE equipment = 'Plate Compactor12' or id=1)

this will not insert equipment=>Plate Compactor12

because it is id

already present in the table.

0


source






More articles:


All Articles