How to delete all elements from a mysql database which are not in an array?

Question

I have a xml file which contains data I want to insert into a mysql database. Now I allready have entries in my database, so these should be updated, not existing should be added and - now comes the problem - the entries which are in the database but not in the xml should be deleted. So what I tried is creating 2 arrays, one containing some comparison values of the xml data, so for example $array_new["some1"] = "test"; $array_new["some2] = "test2";

and the other one containing the old data like $array_old["some1"] = "something"; $array_old["some2"] = "test2";

Now I would like to compare these 2 arrays and add all the values which are NOT in both to a new array. I tried using array_diff but this will only add the one value which is not in both arrays to a new array. But I need to have all the values which have the same key in a new array so I can delete them.

Anyone has any idea how to accomplish this? Thanks!

Doug Miller · Accepted Answer · 2011-12-22 16:03:27Z

2

Assuming that your table has a unique index (primary key) and that your xml data has the same key data available, follow these steps:

1) Delete all records from the table that aren't in your array:

DELETE FROM tablename WHERE unique_field NOT IN (<comma seperated list or keys>)

2) Run a INSERT ... ON DUPLICATE KEY UPDATE type query:

INSERT INTO tablename VALUES (a, b, c) ON DUPLICATE KEY UPDATE field1 = a, ...

answered Dec 22, 2011 at 16:03

Doug Miller

1,0272 gold badges7 silver badges19 bronze badges

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

2 Comments

Chris Over a year ago

Thank you, I will try that! I could then add implode(",", $array) after the NOT IN, couldnt I?

Doug Miller Over a year ago

Yes, I was only showing the SQL statements that you would need to use. I left out the specifics about how to get the comma separated list of unique keys because it depends on your data source. If you have an array containing only the unique key values, then you can implode(",", $array) for the internals of NOT IN ()

user862010 · Accepted Answer · 2011-12-22 15:58:11Z

0

creates a where clause using 'not in' there is only you create identifiers, something like implode(",", $array)

answered Dec 22, 2011 at 15:58

user862010

Collectives™ on Stack Overflow

How to delete all elements from a mysql database which are not in an array?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related