[SOLVED] Writing an xml file with data from a mySQL table with php?

[LuciBKen]

My previous post must have been too complex, here's what I need:

 

I need a PHP script that will remove the data from a mySQL table named jos_video_player and write the information to an xml file named playlist.xml

 

the fields are:

 

title

description

file

thumb

date

 

the xml file syntaxt should look like this:

 

<?xml version="1.0" encoding="utf-8"?>
<FlvPlayer>	<playlist repeat="true" shuffle="true" autoplay="true" distance="5">
	<item>
                                   <file>http://file/path/from/mysql/table/file.flv</file>
	          <thumbnail>http://file/path/from/mysql/table/file.jpg</thumbnail>
                                    <description></description>
                                     <date></date>
		</item>
</playlist>
</FlvPlayer>

 

Can anyone help me, even a link would help, I've tried several different methods to no avail.

 

Please could someone help?

 

[soycharliente]

Just use for loop to write each element yourself and then save it to an XML file.

 

Something like this:

<?php
// connect to db
$query = "SELECT * FROM jos_video_player";
$result = mysql_query($query) OR DIE("Error!!!");
// close connection to db
if ($result) {
$xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>";
$xml .= "<FlvPlayer>";
$xml .= '<playlist repeat="true" shuffle="true" autoplay="true" distance="5">';
while ($r = mysql_fetch_array($result)) {
	$title = $r["title"];
	$desc = $r["description"];
	$file = $r["file"];
	$thumb = $r["thumb"];
	$date = $r["date"];
	$xml .= '<item>';
	$xml .= "<file>$file</file>";
	$xml .= "<thumbnail>$thumb</thumbnail>";
	$xml .= "<description>$desc</description>";
	$xml .= "<date>$date</date>";
	$xml .= '</item>';
}
$xml .= '</playlist>';
$xml .= '</FlvPlayer>';
} else {
echo "Nothing in the db!!!";
}
?>

 

Then write $xml to playlist.xml (i don't know how to write to files, never done it before).

[LuciBKen]

Will that grab every recordset in the table though? For instance, what if I have several different files that I want to load into the playlist? Basically it's for a video/picture slideshow ... I need to set this up as a cronjob to run every five minutes in case of updates.

[LuciBKen]

I guess what I would be looking for to use with that code that you gave me would be to select every field from every column (is that right?) and then enter the data to the variables that you set in the script? ???

[soycharliente]

That script will make your XML file. You said how the db table was set up and I just wrote it based on that.

[LuciBKen]

Hey Man!

 

That code worked perfectly after I added some fopen and fwrite functions to it.

 

Thanks for the code, I really appreciate the help!

 

[soycharliente]

I feel special.