(ASP) ADO to XML to XML to MySQL (PHP)

[Matt_T_hat]

I am creating a solution whereby a system under ASP can share data with a system under PHP. The data is links and reviews of websites.

At ASP101.com they have a sample of code that allows one to export database records into an XML file.

What I want to do is use this XML from the windows server and allow access to it from another server in a different country running PHP.

This I think I can do.

What I want to do next is take the XML feed and add it's contents to another DB this time a MySQL database on the PHP hosting account.

From there I can use SQL to re-formatt the data into the correct tables.

The weakest link is that I have no idea how to get the XML into the database. Many because I suck at PHP haveing only been learning it for two weeks.

Can anyone help me.

[CornedBee]

PHP supports the same DOM as ASP.Net, the method names are only slightly different. Look at the DOM XML functions in the PHP reference.

Specifically, you need to somehow retrieve (via a HTTP request?) the data from the ASP server, then parse it into a DOM, get the stuff you need and fill it in the database.

On a completely different topic, wouldn't it be possible to directly access the database the ASP.Net server uses from PHP? This would make things a lot simpler, I think.

[Matt_T_hat]

I'm Sorry we seem to be talking at cross purposes. the server runs plain old ASP. The database is an Access 2000 hidden under the http root.

The Format of data is not the same as the format for the PHP account.

The full plan is this:

ASP
Take the key data and use the save as XML function (yep)

PHP
Read XML file (oh er...)
Bulk the XML into a database creating the format "on the fly" to match the XML file (help!)

SQL
Construct SQL statement to copy miss shapen but normalised (still normailised I hope) data into new MySQL in database already in palce converting etc so that the data fits in. (NO PROBS)

Cron Job
Set the go get to check for updates and changes and when found load relivant XML file to database and run the SQL converter and bingo async-update from one system to another. (?????HELP!!!!)

The MySQL database is live and carries user input in addition to the bulk load from another server.

...the real trick will be sending the user data back to the originator to close the loop.

The help I need is a large ammount of hand holding, explination of the blindingly obviouse and in some cases snippets of code written for me because I simply don't get it.

But mostly I just need a lot of help. Tutorials or samples that already exist and can be bent to my needs will do

[CornedBee]

Ok, let's see.

http://www.php.net/manual/en/ref.domxml.php
These are the PHP 4 DOM functions. They work like the MSXML DOM.

Should you be using PHP 5, those functions have been replaced by those here:
http://www.php.net/manual/en/ref.dom.php

By the next problem, do you mean that you have to create the table layout on the fly? That makes no sense to me, as you say that this process will happen repeatedly, and I can't imagine you want to create a new table every time. Could you clarify?

The cron job is supposed to check if the Access database has been modified, and if yes, somehow trigger transfer of the new data to the MySQL database, right?

[Matt_T_hat]

Clarify - sure.

The database that sends the XML is an old Access 2000 that was badly made and will soon reach it's limits. However the site remains popular and there is a lot of data that changes fairly regualrly. The whole thing was a bunch of hacks and lashes that were built as I learned stuff

The PHP site that takes the data runs XOOPS and has the default Links module.

I want to populate the XOOPS database but first I want a temp table that I can manipulate with SQL into another temp table (that I can check fits the pattern of the XOOPS one I will then transfer the data in marking it as to when and were it came from.

Later this another project will come online and will send data.

This one will use a different datastructure again.

because I am lazy I would like to mine XML feeds such as combined and agrigated ones for links back to the blogs (like pulling from weblogs.com and filling with data) again this data will have a life span and be a different format.

It sucks for me as I have little by way of PHP skill/knowledge and my XML (as you are noticing) stinks too.

While all taht is happening I am trying to do other things also with XML... life is difficult for me at the moment and it's all my own fault.

[CornedBee]

But wouldn't it make more sense to check the XML if it fits the XOOPS database instead of creating a temporary table that fits the XML and then checking if it is compatible with the XOOPS?

As for the other part, you need to decide whether it makes more sense to have the source database server initiate the transfer or the sink database server. Your design depends on this.

So either you can have something pull the data out of the Access database and initiate a HTTP POST request to the other server, where a PHP script will answer and put the data into the MySQL database.
Or you have a scheduled script of some kind on the target server, which sends a HTTP GET request to the source server, where an ASP script answers, takes the data from the Access DB and puts it into the answer. The script on the target server will take this data and put it into the MySQL database.

[Matt_T_hat]

There are a bunch of rather good ideas going on there.

I had thaught to do it passivly by creating the XML file and allowing the other to collect it whenever it was needed.

But initiate and send on demand has some benifits I had not previously considered.

I imagine that if I were to define an XML scheema that matched XOOPS I could use the system where my skills are greatest to manipulate the data and the system (PHP) where they are weakest to take and add...

I'm going to need some code snippits for the PHP to get me going - as I said I suck and have yet to write usefull PHP from scratch whilst ASP on the other hand...

Can you help me out in the code snippits department?