0

I managed to import a whole load of xml files into database table called "trans" with the help from here: Extracting XML data into MySQL

I wanted to create some additional checks, importing unique xml>database, also delete anything that is in the database that doesn't match from database>xml, keep a list of all non matching entries.

I have managed to get xml>database check to work, importing only unique new transactions.

But sometimes at month end I need to check if the database is still up to date with a new up to date monthly xml file. I need to list everything that does not match the new xml an again to delete them.

How can one achieve this using PHP?

Current Database
custID      Name        Product
2           john        P2
8           mark        P3
9           craig       P3

New XML
custID      Name        Product
2           john        P2
7           sarah       P4

Expected Updated Database
custID      Name        Product
2           john        P2
7           sarah       P4

Database does not match xml
custID      Name        Product
8           mark        P3
9           craig       P3

The only thing I can come up with is to get non matching entries in database is to do a left outer join and locate using null values.

Is this the right way or is there a simpler way or more elegant way to do it?

Here's the select statement

select a.custID, b.custID from trans a
left outer join transtemp b on b.custID=a.custID
where b.custID IS NULL
Community
  • 1
  • 1
David Smith
  • 147
  • 3
  • 10
  • 1
    The `left outer join` with a `where` clause checking for `NULL` is a perfectly reasonable solution. – Gordon Linoff Jul 04 '14 at 21:41
  • So there is no default matching function in sql between tables I'm missing? – David Smith Jul 04 '14 at 21:50
  • You could also use `not exists` or `not in` in the `where` clause. Typically, the `left outer join` solution performs well in most databases. – Gordon Linoff Jul 04 '14 at 21:51
  • Ok so select statement is fine to select a whole load of results, but how would I delete from the table in a where statement with multiple null values? – David Smith Jul 04 '14 at 22:05
  • . . If you edit your question with the code for the `select` statement, it will be much easier to specify the `delete` statement. – Gordon Linoff Jul 04 '14 at 22:06

0 Answers0