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?

Answer 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.

Answer 2

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