CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 6233

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

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!

Upvotes: 0

Views: 363

Answers (2)

Doug Miller
Doug Miller

Reputation: 1037

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

Upvotes: 3

user862010
user862010

Reputation:

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

Upvotes: 0

Related Questions