MySQL to XML with PHP

MySQL to XML with PHP

November 30th, 2008 | written by

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


Category : MySQL &php

Leave a message/Contact me

Latest Blog Entries

Testimonials

"We used Simon to help us get our new website up and running. He is expert at what he does, and I am happy to recommend him without reservation." [More...]

Mike Cranny, Director Create Solutions Ltd

Search


Blog feed Blog Feed

SJC Digital Photograpy SJC Digital Photography