TalkPHP - Generating XML from a Mysql DB with PHP's DOM functions (part one)

TalkPHP (http://www.talkphp.com/forums.php)

- XML, XSLT, XPath, XQuery (http://www.talkphp.com/xml-xslt-xpath-xquery/)

- - Generating XML from a Mysql DB with PHP's DOM functions (part one) (http://www.talkphp.com/xml-xslt-xpath-xquery/1552-generating-xml-mysql-db-phps-dom-functions-part-one.html)

Generating XML from a Mysql DB with PHP's DOM functions (part one)

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

Good job m8, another good article. Especially looking forward to part two :)

Nice little article, I was going to write something similar but without using the DOM. One thing to point out, to save us tired fingers, is an easier/quicker method of attaching simple element structures (ie, a node with just some text) to the document.

sketchMedia's example (copied from above)

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);

}

Salathe's example
Everything will work the same, honest.
It's just a personal preference but I like this approach better for simple elements.

PHP Code:


		
			
foreach($row as $fieldname => $fieldvalue)

{

    $car->appendChild($doc->createElement($fieldname, $fieldvalue));

}

Yea thats defiantly a more elegant way of typing it m8 thanks.
Part 2 almost done :)

At least you're not doing it at 6am this time :-) 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.

indeed, i love this new fangled xml/xslt stuff in php, i love xpath too its all good :-).

Just about to post part 2 8-)

hey, i just completed your tut because i need to get some data from php into flash, and so far i was doing it directly....php to flash and everything was fine but because i got a paging system in php the php changes all the time (i.e blabla.php?x=y) and in flash i've got to load the page which cannot happen if the page changes.....i saw your tut about making an xml and i know you can load an xml into flash so im going to give it a go.....but i get the following error when previewing the page.....

Warning: Invalid argument supplied for foreach() in C:\wamp\www\Web Root\Test\TMP9mcbxywrkg.php on line 23
Wrote: 34 bytes

my code is as follows:

PHP Code:


		
			
<?php

require_once("Connections/Connection.php"); // Database Connection

include("Function/Dateswapper.php"); // Dateswapper function



/////

$query = sprintf("SELECT * FROM clients limit $rowstart,$limit");

$result = @mysql_query($query);

$row = @mysql_fetch_array($result);

/////



$doc = new DomDocument('1.0');

$root = $doc->createElement('profiles');

$root = $doc->appendChild($root); 



 

while($row = @mysql_fetch_array($result))

{

    // add node for each record

    $clients= $doc->createElement('clients');

    $clients = $root->appendChild($clients);

}  



foreach($row as $Username => $fieldvalue) 

{ 

    $clients->appendChild($doc->createElement($fieldname, $fieldvalue)); 

}

echo 'Wrote: ' . $doc->save("clients.xml") . '  bytes';



?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

</head>



<body>

</body>

</html>

and thnx in advance.....gr8 tut btw...

hi m8, glad you liked it.

the problem (as far as i can see) as that the 'foreach' loop is outside of the 'while'.

fixed:

PHP Code:


		
			
<?php
require_once("Connections/Connection.php"); // Database Connection
include("Function/Dateswapper.php"); // Dateswapper function

/////
$query = sprintf("SELECT * FROM `clients` LIMIT %d, %d", $rowstart, $limit);
$result = @mysql_query($query);
/////

$doc = new DomDocument('1.0');
$root = $doc->appendChild($doc->createElement('profiles'));  
while($row = @mysql_fetch_array($result))
{
    // add node for each record
    $clients = $root->appendChild($doc->createElement('clients'));
    foreach($row as $fieldname => $fieldvalue)  
    {  
        $clients->appendChild($doc->createElement($fieldname, $fieldvalue));  
    }  
}  
echo 'Wrote: ' . $doc->save("clients.xml") . '  bytes';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
<title>Untitled Document</title> 
</head> 

<body> 
</body> 
</html>

that should work, I fixed your sprintf and shortened the script abit.

Need some help, if possible.

i have some data from the DB and need to output it into an new xml doc like this

Code:

<thumbnails>

<thumbnail filename="flashmo_084_intro.jpg" url="http://www.flashmo.com/preview/flashmo_084_intro" target="_blank"

                title="Item Six Title" 

                description="Fusce vulputate tellus ac felis. Praesent mauris. Quisque gravida faucibus ligula. Aliquam magna. Phasellus id felis. " />

</thumbnails>

where filename, url, target, title, and description come as data from the DB

any idea, the output has to be identical, since it goes to an flash, and i´ve no idea, how to modify Action script

good job like this topic

[http://www.nflcnshop.net],Nike NFL Jerseys,Wholesale NFL Jerseys, Cheap nba jerseys,Wholesale 2013 NBA All Star ,Nike NFL Jerseys,Football Jerseys Cheap,Cheap NFL jerseys, nfl jerseys from china,Cheap Nike NFL Jerseys,NFL Jerseys By Nike,Cheap Jerseys Wholesale,Buy Cheap Nike Jerseys,Wholesale MLB Jerseys,Cheap NFL Hats,jersey store, jersey wholesale,steelers jersey,packers jersey, jersey wholesale,new nike nfl jerseys,nfl jerseys for sale