Results 1 to 5 of 5
February 8th, 2008, 08:20 PM #1
Using xml to UPDATE my mySQL database. tricky!
- Join Date
- February 8th, 2008
Right, I am a novice with datafeeds (Aren't we all who create these topics?)
I have a question.
I already have a functioning website using php and mysql. over 700 mydql database entries refer to products available through merchant affiliates.
as the information for these products can change frequently it's important i am able to run a script that automatically does this. now here's the thing...
until now i have simply used regex scripting. now i need to begin using xml datafeeds as afiliates have complained about unwanted noise traffic generated from my frequent regex script updates.
i am a novice. i have no idea where to begin or what to do. i do know every online topic i've read about this relates to taking all info from datafeed and creating a whole new database with the info. i don't want to do that.
I have existing tables with existing pre-set columns. from the datafeeds, i don't need to lift info from every column, just some of them relating to existing mysql database entries and then "UPDATE" them back into my database.
where do i begin?
i'm not asking for someone to write the code on my behalf of course, but i'm asking for someone to explain the process and the method in very VERY basic english. or does a website exist that explains this in a simple basic tutorial? beyond basic php - i am lost!
i really hope someone takes pity on me cos it really will be appreciated!
February 9th, 2008, 12:52 AM #2
I can point you in the right direction halifaxer, if you could clarify a few points for me.
1. Do you want to import an xml file into a mySQL table?
2. If so, you want to read this file and then update your existing rows with the changes found on some of the products?
3. Within this xml datafile is there a unique value associated with each item, that you also have stored as a column in your database table?
If all of those points are correct then let me know and I'll give you some pseudo code and links to the proper references to code a basic working script that can do this for you.
February 9th, 2008, 08:48 AM #3
- Join Date
- February 8th, 2008
that would be fantastic. to clarify your points...
1. Yes, I want to import an xml document into mysql table
2. Yes, I want to read this file and then update my existing rows with the changes found on some of the products.
And 3. Yes, there is a common datarow between the xml and mysql, "sku" works as an identifier, so while other rows like "price" and "description" can change, "sku" will always remain. eg.
xml:- (up to date)
description=A citrusy aftershave
mysql table:- (out of date)
I can't PM you to get in direct contact, otherwise I would, but this is an extremely kind gesture to get me started and it's truly appreciated.
February 9th, 2008, 12:51 PM #4
Great! This is not too hard at all, though it may take some experimenting on your part to get comfortable with xml handling.
First of all you need to be running PHP 5. If not, then upgrade to it either yourself or have your webhost do this for you. PHP 4's xml functions are awful, while 5's are very powerful and easy to use.
So basically you want to download the file to your server, load it into an xml object, walk through the object's tree structure and for every record you come across, check to see if that record is in your database, and if it is, update that database record.
You will need a few php built in functions to accomplish this.
Starting with: ftp family of functions: http://us2.php.net/ftp
you use ftp_login() to establish a connection to the ftp server that the feed is coming from then you use ftp_get() to download the file to a directory on your webserver.
You can also use ftp_mdtm() before you download to check to see if the file has been updated since you last ran the script, and storing this value in a database, if you want to automate the whole process.
So now you have the new file on your server. And you need to read the file in order to process it. To do this you use the simplexml family of functions, documented here http://us2.php.net/simplexml
simplexml_load_file() will take your file and load it into memory as an xml object, where you can then use other simplexml functions to walk the document tree. The functions you'll most likely need to use are attributes(), children(), and getName()
You will need to study the structure of your data feed's xml in order to loop through, and determine what parts of the xml file constitute a record.
So as you are walking the document tree, you will then make a select statement on the db to try to find that record. It will probably be best to use a temporary associative array to load each xml record into as you come to it, say $tArray["sku"] = $xmlObject->record->sku;, $tArray["description"] = $xmlObject->record->sku; etc, etc You then make the select query to be something like
"SELECT * FROM tablename WHERE sku = '".$tArray["sku"]."";
If there's a match, then you will do an update query on that record: "UPDATE tablename SET id = '".$tArray["id"]."', name = '".$tArray["name"]."', description = '".$tArray["description"]."' WHERE sku = '".$tArray["sku"]."' LIMIT 1"
So that's the meat of it. You just loop through each record and check to see if it's in your db and if so update it, if not, move on to the next, until you've gone through all of the records.
The trickiest part of this is getting used to walking the document using simplexml. So I'd recommend just opening the file directly at first, and play around with it using simplexml to get the feel of it. Once you've loaded it using the $xmlDoc = simplexml_load_file(), do a print_r($xmlDoc); Also use a bunch of test echo statements to echo various parts of the xml object, until you see what you are expecting to see. This will be invaluable as you debug your script as it shows the entire content structure of the xml data.
The rest of it is simple database queries, which you can use mysql drivers, or preferably, PDO to connect and run queries.
Here's some basic pseudo code:
connect to the ftp server
check the last update time of the xml file
if file is new
download file to your webserver
open the file with simplexml
foreach record in the file using the simplexml tree walking functions load the record into temp array
lookup record in database
if record is in db
update record using temp array's values
You will need to study the php docs that I linked to. Check out the examples provided in the php docs, and read all of the information for each of the various methods.
Being able to write a script like this will expose you to the beginnings of xml integration. This is a very handy skill to have with certain affiliate programs that use real time xml, or even better, SOAP, to integrate your website with their inventory. Programs that use this method allow you to create websites that can sell products without users ever leaving your site. So you can then track them from landing to sale, develop your own custom content, build newsletter lists, etc, etc.
February 20th, 2008, 09:20 PM #5
- Join Date
- February 20th, 2008
That's great I got rough idea from this thread on how to update let say price if there're any changes from the website mySQL database.
By chillini in forum Programming / Datafeeds / ToolsReplies: 10Last Post: December 2nd, 2008, 01:06 PM
By Snib in forum Programming / Datafeeds / ToolsReplies: 10Last Post: September 8th, 2006, 12:24 AM
By dflsports in forum Programming / Datafeeds / ToolsReplies: 7Last Post: December 21st, 2005, 12:14 PM
By Amavisca in forum Programming / Datafeeds / ToolsReplies: 0Last Post: July 6th, 2005, 05:43 AM
By ~Michelle in forum Programming / Datafeeds / ToolsReplies: 2Last Post: March 26th, 2004, 12:59 PM