MX Kollection Forum :: Batch processing forms

I recieve an XML file from an outside (MSSQL) database with updates for multiple records in my MYSQL datbase. Using a SPRY dataset I can update a single foem with the data from the XML file but I want to update or replace all the records. So my plan would be to run the form once for each "unit" in the xml file ( about a hundred).

Any idea as to how to do this?

Looking forward everyone's ideas...

Nick

Hi Nick,

There is no need to get all fancy with forms and stuff.  You can do it in pure php.

Here is an example that I am running as to populate a local database from a central database from elsewhere.

<?
//connect to local database
$hostname_vatsaf = "XXXXXXX";
$database_vatsaf = "XXXXXXX";
$username_vatsaf = "XXXXXXX";
$password_vatsaf = "XXXXXXX";
$vatsaf = mysql_connect($hostname_vatsaf, $username_vatsaf, $password_vatsaf) or die ('I cannot connect to the database because: ' . mysql_error());

// Clear Local Database
mysql_select_db($database_vatsaf, $vatsaf);
mysql_query("DELETE FROM vatmembers WHERE 1=1");

// Retrieve remote data
$data = file('http://remotedomain.com/divdb.php'); // The source file
    foreach ($data as $row_num => $dataRow)

// Scrape data
    {
        list($vid, $rating, $name, $surname, $email, $age, $state, $country, $experience, $endsuspend, $regidate, $div) = split(",", $dataRow);
//Print data on screen.  This is commented but used to check if the actual data was indeed scraped from the source                 
        //echo $vid, ' ', $name, ' ', $surname, ', ', $state, ', ', $country, "<br />";

// Insert into database   
        $sql = "INSERT INTO vatmembers (vid, name, surname, state, country) VALUES ('$vid', '$name', '$surname', '$state', '$country')";
        mysql_query($sql);
    }
?>

The file is included into another page and run as soon as the page is loaded.
Alternatively it can be run as a CRON to populate the db on a regular basis.

I hope this helps.

First of all, thanks for taking the time to respond. Unfortunately, We receive the data from an outside service who can not allow us access to their database. They can automatically ftp an XML file directly to our site which we then need to parse to our database. The XMl file has the equivalent of one hundred records. Using a SPRY dataset I was planning to populate a form with the data and repeat the process one hundred times by first building a recordset of just the "ids" and then passing them, one at a time, in the URL which would filter the SPRY dataset, then insert/update. I suppose I could drop the table record first then do an insert for all the records. The big question is how to automate the process of converting and XLM file to a MYSQL table? nick

Hi Nick,

I have never had the need to import XML datafile (yet!!) but I would approach it the same as in the above example.  The above example it a simple recordset displayed as a list with each record on a separate line and the data separated by a ","

In your case the data would be on your sever so you would open the file locally, no big deal...

In essence, what you want to do is take this;

and change it to this

Fred,MacDonald

From here you should be able to get it into a variable and then into your database using my example above.

The repeat and insert function would still be the same but you would need a function to strip the tags from the dataset.  Could probably be done with php strip_tags() and preg_replace() functions.

Get more info and good examples here... http://uk.php.net/strip-tags

Good luck. This is where the fun starts...

Let me know if you managed to get it working.