php mysql - insert items if not exist

Question

I have a PHP array and I want to insert from this array the items that are not already in MySQL table. After searching i could do this item by item using the following code

INSERT INTO `rtusers` (`Status`, `MSISDN`) 
SELECT * FROM (SELECT 0,  '966111111111') AS tmp 
WHERE NOT EXISTS (
SELECT `MSISDN` FROM rtusers 
WHERE MSISDN = '966111111111' ) LIMIT 1;

but the problem is i have hundreds of items in the array. if i used this code as a loop this will make hundreds of hits to the database. Does anybody have an easier solution?

Gordon Linoff · Accepted Answer · 2016-01-22 00:07:00Z

1

You should probably load the array directly into a temporary table of some sort. You can do this with individual insert statements or using load data infile or some other bulk load mechanism.

Then to ignore the record, define a unique index on rtusers(mission) and use:

insert into rtusers(status, mission)
    select status, mission
    from rtusers_staging
    on duplicate key update mission = values(mission);

The on duplicate key part doesn't do anything. It just ignores any duplicate records.

answered Jan 22, 2016 at 0:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Uğur Taşdildiren · Accepted Answer · 2016-01-22 00:24:42Z

0

Create a table with new numbers names new_users then execute this query:

INSERT INTO `rtusers` (`Status`, `MSISDN`)
SELECT Status, MSISDN from new_users
WHERE MSISDN NOT IN (SELECT MSISDN FROM rtusers);

answered Jan 22, 2016 at 0:24

Uğur Taşdildiren

4183 silver badges12 bronze badges

Collectives™ on Stack Overflow

php mysql - insert items if not exist

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related