Hello all,
This is gonna be (hopefully) part one of two about PHP and XML/XSL.
I was inspired to do this tutorial after seeing Wildhoney's post about XML and XML's family of technologies and decided I'd have a crack at writing a tutorial on what i know.
I was going to do a full article on how to generate an XML file then perform an XSL 'transformation' with PHP's XSL functions, but that would have taken too long to write all at once, and there is alot for you guys to take in.
So i decided to just post this first, then when i get some time ill write the rest in part 2 (unless someone feels like they wanna do it )
In this article im going to briefly show you how to generate an XML file from a MySQL database using PHP's DOM functions.
Im assuming that you know what XML is and how it works, read another good tutorial on Tizag if your not sure Just to note, im by no means an expert on this, but i do know enough to use it, and i wanted to show the power and flexibility it provides.
Ok, enough of the waffling, ON WITH THE XML!!!
The first thing we need is a database (for this example anyway, you dont 'need' a DB for this technology), heres how my mysql database table looks:
mysql Code:
--
-- Table structure for table `cars`
--
CREATE TABLE `cars` (
`id` tinyint(8) unsigned NOT NULL auto_increment,
`name` char(30) collate utf8_unicode_ci NOT NULL,
`bhp` mediumint(4) unsigned NOT NULL,
`year_released` mediumint(4) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `cars`
--
INSERT INTO `cars` ( `name` , `bhp` , `year_released` )
VALUES (
'Aston Martin DB9', 450, 2004
), (
'Pagani Zonda C12 F', 602, 2005
), (
'Lamborghini Murcielago LP640', 633, 2006
), (
'Koenigsegg CCX', 806, 2006
), (
'Bugatti Veyron EB 16.4', 1001, 2006
);
ok, i have some default values in there so we can query them.
Now that we have our DB sorted we can now look at creating an XML file from it.
Im not going to bother with fancy PHP DB classes or security, as this is mainly about XML/XSLT in PHP, you should be able to tailor it to your needs.
First off we need to connect to the DB:
PHP Code:
$link = mysql_connect('localhost','user','pass');
mysql_select_db('database',$link);
Then im going to query the database:
PHP Code:
$result = mysql_query('SELECT `name`,`bhp`,`year_released` FROM `cars`');
Ok next im going to instaciate PHP's DomDocument() object:
PHP Code:
$doc = new DomDocument('1.0');
As you can see, i have created a new instance of DomDocument, this returns the object reference for the new XML document
Notice i have passed '1.0' in to the construct, this means its going to create an XML version 1.0 file, there is
another optional parameter that takes the charset i.e. UTF-8 etc but i cant be botherd :)
Next im going to create a 'Root' element for our new XML file:
PHP Code:
$root = $doc->createElement('carlist');
$root = $doc->appendChild($root);
I have named the root element 'carlist', as this XML is going to contain a list of cars.
So basicly we have created a new element called 'carlist' then we called the appendChild() function, this functions appends a child to an existing list of children or creates a new list of children, in our case its our root node
(once again you need to know XML to understand that)
Next I'm going to fetch an array from the db, and do some more stuff that ill explain:
PHP Code:
while($row = mysql_fetch_assoc($result))
{
// add node for each record
$car= $doc->createElement('car');
$car = $root->appendChild($car);
}
Right as you should know what this is doing, if not we are fetching an associative array for each row.
Ok the next two lines this look familiar dont they? thats because effectively they are, but this time we aren't creating a root, so appendChild() will append the new element to our root and it will become a child of the root, which in our case is 'carlist', so our XML structure will look like this (NOTE a XML document can only have ONE root):
xml Code:
<carList>
<car>
</car>
</carList>
So for each iteration of the while loop, a new child of 'carlist' (root) called 'car' will be created, because our XML will contain more than one car, this will be created more than once, so for every car in the DB we will have a new 'car' element which will contain data about that car.
Next we need to add the rest of the elements, this time the data of each car, we do this by creating children of car.
We can do this by nesting a foreach within the while and looping through the associative array produced by mysql_fetch_assoc(), like so:
PHP Code:
foreach($row as $fieldname => $fieldvalue)
{
$child = $doc->createElement($fieldname);
$child = $car->appendChild($child);
//add data to the new element
$value = $doc->createTextNode($fieldvalue);
$value = $child->appendChild($value);
}
Ok here we have a foreach, once again you hopefully should be familiar with this.
We next create a new element and name it by the fieldname (from the array, which is from the field name in the database), then append it as a child of 'car', we use the reference to 'car' that was created in the while loop in the $car variable. Then we add some data from the database into the newly created node. So our xml file will look something like this:
xml Code:
<carList>
<car>
<name>car name from DB</name>
<bhp>break horse power from DB</bhp>
<year_released>year of car's release from DB></year_released>
</car>
<carList>
So to put it in English, the while loops once and creates the 'car' node, then the foreach fires and creates 'name' then makes it a child of 'car', then puts data into it, and does the same for 'bhp' and 'year_released', then the while loop will loop again, and the process is repeated until there are no rows left in the result from the database.
After that script has run, we end up with something that looks like this:
xml Code:
<?xml version="1.0" ?>
<carlist>
<car>
<name>Aston Martin DB9</name>
<bhp>450</bhp>
<year_released>2004</year_released>
</car>
<car>
<name>Pagani Zonda C12 F</name>
<bhp>602</bhp>
<year_released>2005</year_released>
</car>
<car>
<name>Lamborghini Murcielago LP640</name>
<bhp>633</bhp>
<year_released>2006</year_released>
</car>
<car>
<name>Koenigsegg CCX</name>
<bhp>806</bhp>
<year_released>2006</year_released>
</car>
<car>
<name>Bugatti Veyron EB 16.4</name>
<bhp>1001</bhp>
<year_released>2006</year_released>
</car>
</carlist>
Next we gotta get the XML document (thats held in memory as shown above) and save it to a file:
PHP Code:
echo 'Wrote: ' . $doc->save("cars.xml") . ' bytes';
You don't have to echo here but it just shows that the file has been written to cars.xml
So there you have it, we have just created an XML file using PHP's DOM functions.
There are some more advanced things we could have done, i.e. instead of having a separate node for 'name' we perhaps could have created an 'attribute' of the 'car' node with this:
PHP Code:
$node = $doc->createElement('car');
$node = $root->appendChild($node);
$node->setAttribute('name', 'car name from DB');
so our XML would look like this:
xml Code:
<car name="car name from DB"></car>
as opposed to :
xml Code:
<car>
<name>car name from DB</name>
</car>
but that would have complicated the rest of the script and made it longer, plus it gives you guys something extra to mess with.
Hopefully i explained enough there, if not just ask me and ill be only too happy to clarify something, or if i have got something horrifically wrong then I'm open to criticism.
In the next part I'll show you how to perform a 'transformation' with a XSL file with PHP.
Until then have fun
EDIT: Part 2 and attachment with files:
http://www.talkphp.com/showthread.php?p=4904
Generating XML from a Mysql DB with PHP's DOM functions (part one)
Crazy time, if I might say so myself! I love how all DOM stuff is standardised - one of the very few areas where PHP is, perhaps? Makes it so much easier after years of Javascript DOM to actually get your head round.
Linear Mode
