MySQL to XML with PHP
November 30th, 2008 | written by Simon
This tutorial is based on a bit of code I created to extend Flash Nifties XML slideshow to use an MySQL database as its datasource.
It assumes you have a MySQL database where you store your images.
[Download sql2xml.zip for XML Slide Show]
Step 1: Make the database connection
<?php $con = mysql_connect("localhost","username","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } ?>
Step 2: Query your database
<?php mysql_select_db($database, $con); $query_images = "SELECT * FROM images ORDER BY RAND()"; $images = mysql_query($query_images, $con) or die(mysql_error()); $row_images = mysql_fetch_assoc($images); ?>
ORDER BY RAND() – this randomizes the results of the query.
Step 3: Generate the XML markup
<?php //start the xml dataset $xml = '<?xml version="1.0" encoding="iso-8859-1"?> <slideshow> '; // create a loop that outputs each row from the sql query while ($row_images = mysql_fetch_assoc($images)) { $xml .=' <image img="images/'.$row_images['image_file'].'" /> '; } //close the xml dataset $xml .='</slideshow>'; ?>
Step 4: Write the output to file
<?php $fp = fopen('slideshow.xml', 'w'); fwrite($fp, $xml); fclose($fp); ?>
This will look for the file slideshow.xml and will overwrite its contents with the output above. If the fiels does not exist it will attempt to create it.
The result – slideshow.xml
<?xml version="1.0" encoding="iso-8859-1"?> <slideshow> <image img="images/image2.jpg /> <image img="images/image5.jpg /> <image img="images/image1.jpg /> <image img="images/image3.jpg /> <image img="images/image7.jpg /> <image img="images/image4jpg /> <image img="images/image6.jpg /> </slideshow>
And there you have your dataset ready for consumption by your application.
In order to use this to technique to extend the Flash Nifties XML Slideshow simply download sql2xml.zip and follow the instructions contained within.
You can see an example of the databse-driven XML Flash Slideshow at St Andrew’s Primary School website
