MySQL -> XML -> MySQL

I'm trying to go back and forth between a db and XML pages that filter
db data.
I have a multidimensional array (parsed from XML) like this
(simplified):

Array ( [name] => TABLE1 [attributes] =>
Array ( [ID] => ID1
[FIELD1] => Value1
[FIELD2] => Value2
[FIELD3] => Value3
[FIELD4] => Value4
[FIELD5] => Value5
[FIELD6] => Value6 )
[children] =>
Array ( [0] =>
Array ( [name] => TABLE2 [attributes] =>
Array ( [ID] => ID1
[FIELD1] => Value1
[FIELD2] => Value2
[FIELD3] => Value3
[FIELD4] => Value4 ) )

[1] => Array ( [name] => TABLE1 [attributes] =>
Array ( [ID] => ID2
[FIELD1] => Value1
[FIELD2] => Value2
[FIELD3] => Value3
[FIELD4] => Value4
[FIELD5] => Value5
[FIELD6] => Value6))

[children] => TABLE2 [attributes] =>
Array ( [ID] => ID2
[FIELD1] => Value1
[FIELD2] => Value2
[FIELD3] => Value3
[FIELD4] => Value4 ) )
)

So now I'd like to walk through the array and build MySQL statements of
the type:

"INSERT INTO TABLE1 (ID,PARENT,FIELD1,FIELD2,etc.) VALUES
(ID1,Parent,Value1,Value2,etc.)"

or

"UPDATE TABLE1 SET (PARENT,FIELD1,FIELD2,etc.) =
(Parent,Value1,Value2,etc) WHERE ID = ID1"

depending on whether or not the ID exists.

Using array_walk_recursive() I can successfully traverse the array, but
I'm still struggling to build the appropriate queries.
Also, I haven't thought through what happens when items originally
selected from the database are subsequently deleted from the XML prior
to constructing the array.
Because (at present) not all of the db data is contained within the XML
page, I can't simply drop the tables and recreate them each time -
well, not unless I rethink the XML page a little.

I'd appreciate any help on any aspects of this problem - correctly
traversing the array, building the statements and maintaining
integrity.

Thanks

Re: MySQL -> XML -> MySQL

strawberry wrote:
> I'm trying to go back and forth between a db and XML pages that filter
> db data.
> I have a multidimensional array (parsed from XML) like this
> (simplified):
blah, blah, blah snipped
> So now I'd like to walk through the array and build MySQL statements of
> the type:
>
> "INSERT INTO TABLE1 (ID,PARENT,FIELD1,FIELD2,etc.) VALUES
> (ID1,Parent,Value1,Value2,etc.)"
>
> or
>
> "UPDATE TABLE1 SET (PARENT,FIELD1,FIELD2,etc.) =
> (Parent,Value1,Value2,etc) WHERE ID = ID1"
>
> depending on whether or not the ID exists.

Use INSERT ... ON DUPLICATE KEY UPDATE to solve this in one hit (or
possibly REPLACE INTO)

> Using array_walk_recursive() I can successfully traverse the array, but
> I'm still struggling to build the appropriate queries.
> Also, I haven't thought through what happens when items originally
> selected from the database are subsequently deleted from the XML prior
> to constructing the array.

Instead of completely deleting from the XML, can you either:

1) leave the ID in and delete everything else
or
2) leave the ID in and add a DELETE instruction

In either case, examining the node should give you the hint required to
create a DELETE FROM query instead of one of the other ones.

Regards
Paul