将xml产品加载到mysql db中，更新productid所在的位置，插入新的地方并删除db中的productid而不是xml

Question

I have a dynamic products xml file that i need to load from an external source and save in a db table. Now i want to keep the xml file which contains current products in synch with what is in the table. so I want to make sure I update products with IDs in both xml and db table, insert new products and delete those products that are obsolete in db table (they are not in xml file) see illustrations below
XML file mapping

Productid->1 and product-name->book,
Productid->2 and Prouct-name->pen

Now table below

ID Name
1 book
3 pencil

Now I want to update product ID 1 with info from xml, insert product ID 2 from xml into table and delete product ID 3 from table since it's not found in xml

Please any help is appreciated.

Answer 1

Since you already have the flowchart of the desired process, it should be pretty straightforward.

I am not doing any data sanitization / escaping, as you do it as per your requirements.

XML FILE: (save it as testFeed.xml)

<?xml version="1.0" encoding="UTF-8"?>
<products>
    <product>
        <id>1</id>
        <name>book</name>
    </product>
    <product>
        <id>2</id>
        <name>pen</name>
    </product>
</products>

PHP Code:

<?php
$xml = simplexml_load_file( 'testFeed.xml' );
$xpath = $xml->xpath( '//product' );

$feed = array();
foreach( $xpath as $node ) {
    $feed[(string)$node->id] = (string)$node->name;  // id is key, name is value
}

// delete all entries from table where the table entries are not in the xml
$sql = 'DELETE FROM `table` WHERE id NOT IN 
          (' . implode( ',', array_keys( $feed ) ) . ')';
$run = mysql_query( $sql, $link );

//Now, insert/update - USE MySQL's "INSERT ... ON DUPLICATE UPDATE" feature
foreach( $xpath as $key => $node ) {
    $sql = 'INSERT INTO `table` (id,name) VALUES ("'.$key.'","'.$node.'") 
              ON DUPLICATE KEY UPDATE name="'.$node.'";'
    $run = mysql_query( $sql, $link );
}
?>

MySQL INSERT ... ON DUPLICATE UPDATE example here.

Hope this helps.