CODEWITHSUNDEEP
phpmysql
ahmedbashaa
ahmedbashaa

Reputation: 1

php mysql - insert items if not exist

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?

Upvotes: 0

Views: 56

Answers (2)

Uğur Taşdildiren
Uğur Taşdildiren

Reputation: 418

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Related Questions