Insert unique values from PHP array into SQL table

Question

Possible Duplicate:
How to 'insert if not exists' in MySQL?

There is SQL table:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `MyVar` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

And there are two PHP arrays arr1[]=array("abc","bcd") and arr2[]=array("abc","cde").

Let's say I have saved arr1[] values to SQL table. Now let's suppose I need to save arr2[] values to the same SQL table. Which SQL INSERT query do I need to write in order to avoid duplicate saving of "abc" entry? The result must be:

MyTable:
1  |  abc
2  |  bcd
3  |  cde

but NOT:

MyTable:
1  |  abc
2  |  bcd
3  |  abc
4  |  cde

UPDATE: Maybe the MyTable should be created/defined in such a way that duplicate entries are ignored?

Set a unique constraint for your MyVar column. So trying to insert a value which already exists will not work. — TRD, Aug 29 '12 at 11:55
http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — matino, Aug 29 '12 at 11:56
Which duplicate (merging / intersecting arrays) or (inserting stuff that doesn't exist already) or both depends on the details but I think there's enough material there to get you to a good solution :) — edorian, Aug 29 '12 at 12:00

score 6 · Answer 1 · edited Aug 29 '12 at 12:05

6

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT ... ON DUPLICATE KEY UPDATE. Check this article.

You can also in memory intersect array-s and then insert just unique values if that solution fits for you.

edited Aug 29 '12 at 12:05

Mahmoud Gamal

72,639
16
129
156

answered Aug 29 '12 at 11:55

Krešimir Lukin

399
1
7

score 1 · Accepted Answer · answered Aug 29 '12 at 11:59

Make the MyVar as UNIQUE in your table.

Like this:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `MyVar` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `myvaridx` (`MyVar`)
);

or, if you're unable to recreate the table, alter it using

ALTER TABLE `request`
ADD UNIQUE INDEX `myvaridx` (`MyVar`)

score 0 · Answer 3 · answered Aug 29 '12 at 12:00

0

Using SQL for such task would be wasteful.

I suggest to use using the "array_merge" function to merge the arrays and then "array_unique" function to handle the duplication.

Then insert the unique values to the database.

answered Aug 29 '12 at 12:00

Itay Gal

10,075
6
30
68

Insert unique values from PHP array into SQL table

3 Answers3