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 :)

you can try running a rowCount on SELECT http://php.net/manual/en/pdostatement.rowcount.php but isn't always reliable. Using `count()` would work better with PDO. See this Q&A about it http://stackoverflow.com/q/19109774/ — Funk Forty Niner, Jul 23 '15 at 06:34

score 1 · Answer 1 · answered Jul 23 '15 at 06:36

1

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 );

answered Jul 23 '15 at 06:36

Abhishek Ginani

4,511
4
23
35

1

OP states: *"I don't want to use id field in the query because it is AUTO INCREMENTAL and Primary Key."* – Funk Forty Niner Jul 23 '15 at 06:38
Then you can setup multiple field as primary key. Here is the way to do that : ALTER TABLE equipment ADD PRIMARY KEY (id, equipment); Make sure that you have dropped out old primary key before doing this. – Abhishek Ginani Jul 23 '15 at 06:39
1

MySQL only lets you have "one" primary key. – Funk Forty Niner Jul 23 '15 at 06:40
See this link : http://dba.stackexchange.com/questions/57548/how-to-set-up-multiple-fields-as-primary-key-in-mysql – Abhishek Ginani Jul 23 '15 at 06:42
Technically you were correct. You can have one primary key per table, but it can consist of more than one column (be a composite key) – Mchl Jul 23 '15 at 06:49

num8er · Accepted Answer · 2015-07-23T06:52:54.370

1

Create unique index on field: equipment

ALTER TABLE equipment ADD UNIQUE equipment_unique (equipment);

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

edited Jul 23 '15 at 06:52

answered Jul 23 '15 at 06:43

num8er

18,604
3
43
57

1

@fred-ii , Thank You – num8er Jul 23 '15 at 06:56
2

thanks a bunch :), that worked and I also found out the problem in my `not exists` query. I have posted the correct query below. Please check if it is correct. – Parag Jadhav Jul 23 '15 at 07:14
2

it's also solution but it's better to do it without query inside of query – num8er Jul 23 '15 at 07:20
1

what problem would it cause? – Parag Jadhav Jul 23 '15 at 07:27
no problem, just You're making select before inserting, so it may cause additional load if table will become big – num8er Jul 23 '15 at 07:30

score 0 · Answer 3 · edited Oct 27 '16 at 05:43

As @num8er suggested, that solution also worked for me. And another workaround for not exists query is (without creating index on equipment) as follows,

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 id is already present in table.

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

3 Answers3