Insert unique values from PHP array into SQL table [duplicate]

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
– TRD, Commented Aug 29, 2012 at 11:55
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
– edorian, Commented Aug 29, 2012 at 12:00

Mahmoud Gamal · Accepted Answer · 2012-08-29 12:05:53Z

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, 2012 at 12:05

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

answered Aug 29, 2012 at 11:55

Krešimir Lukin

3991 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hjpotter92 · Accepted Answer · 2012-08-29 11:59:26Z

1

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

answered Aug 29, 2012 at 11:59

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Comments

Itay Gal · Accepted Answer · 2012-08-29 12:00:28Z

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, 2012 at 12:00

Itay Gal

10.8k6 gold badges43 silver badges78 bronze badges

Collectives™ on Stack Overflow

Insert unique values from PHP array into SQL table [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related