How to Create XML file using php and a mySQL Database?

Hi,

I have a database 'xmldata'
***************
id         int(8)      auto_increment
title        varchar(250)
url        varchar(250)
description        text
date datetime
***************

What I need is an XML File that is populated with the 'xmldata' Table Data mentioned above.

Also, will need the 'unix date' to be generated somehow, too.

Thank You Very Much for Your Help!

FirstBorn

PS.  A Sample RSS File that I plan on using is below:

*****************************
<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
      <channel>
            <title><![CDATA[Channel Title Goes Here]]></title>
            <link><![CDATA[Link goes Here]]></link>
            <description><![CDATA[Channel Link goes Here]]></description>
            <language><![CDATA[en-us]]></language>
            <copyright><![CDATA[Copyright Info goes Here]]></copyright>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
            <item>
                  <title><![CDATA[Item Title from the database goes here]]></title>
                  <link><![CDATA[Item URL from the database goes here]]></link>
                  <description><![CDATA[Item description from the database goes here]]></description>
                  <unixdate><![CDATA[1142313995]]></unixdate>
            </item>
      </channel>
</rss>

*****************************
LVL 1
FirstBornAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAelbrechtCommented:
you can do this 2 ways:

the good way: use a PHP XML parser; this depends on your PHP version and i honestly only recommend this when using PHP5; imho the PHP4 XML parser isn't all that good.

the fast way: you can perfectly well write an XML file with strings yourself.
example:

$out = "<rss version=\"2.0\">
     <channel>
          <title><![CDATA[Channel Title Goes Here]]></title>
          <link><![CDATA[Link goes Here]]></link>
          <description><![CDATA[Channel Link goes Here]]></description>
          <language><![CDATA[en-us]]></language>
          <copyright><![CDATA[Copyright Info goes Here]]></copyright>
";

$sql = "SELECT title, url, description, UNIX_TIMESTAMP(`date`) as unixdate FROM xmldata ORDER BY `datetime` DESC LIMIT 0,10";
$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs)){
$out.= "          <item>
               <title><![CDATA[".$row["title"]."]]></title>
               <link><![CDATA[".$row["url"]."]]></link>
               <description><![CDATA[".$row["description"]."]]></description>
               <unixdate><![CDATA[".$row["unixdate"]."]]></unixdate>
          </item>";
}
$out.= "     </channel>
</rss>";

you should probably do some html_entities on the $row["title"] and/or $row["description"], depending on how you saved them in your database ofcoz.

hope this helps,

cheers,
Andy

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndyAelbrechtCommented:
ow right, forgot:

file_put_contents($filename, $out); // to write the xml (in $out) to file $filename
FirstBornAuthor Commented:
Hi Andy,

I'm using php 4.4.2
MySQL version        4.0.25-standard

k... I've created a test folder 'xmlout'
CHMODed the Folder to 777.
I've created a test file called xmlout.php

I've tested it with 1 Major Error:

Fatal error: Call to undefined function: file_put_contents() in /home/mydir/public_html/xmlout/xmlout.php on line 48

I will provide the code below.

Any Ideas?

Thanks.

FirstBorn

//*************************
//the Code Begins Below the Next set of asterisks
//*************************
<?

// **** Username ****
$DBUser='username';

// **** Password ****
$DBPass='password';

// **** Host ****
$DBHost='localhost';

// **** Database ****
$DBName='db_xml';

// Set the database access information as constants.
DEFINE ('DB_USER', $DBUser);
DEFINE ('DB_PASSWORD', $DBPass);
DEFINE ('DB_HOST', $DBHost);
DEFINE ('DB_NAME', $DBName);

// Make the connection.
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );
$db_selected = mysql_select_db(DB_NAME, $dbc);

$out = "<rss version=\"2.0\">
     <channel>
          <title><![CDATA[Channel Title Goes Here]]></title>
          <link><![CDATA[Link goes Here]]></link>
          <description><![CDATA[Channel Link goes Here]]></description>
          <language><![CDATA[en-us]]></language>
          <copyright><![CDATA[Copyright Info goes Here]]></copyright>
";

$sql = "SELECT title, url, description, UNIX_TIMESTAMP('edate') as unixdate FROM xmldata ORDER BY 'edate' DESC LIMIT 0,10";
$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs)){
$out.= "          <item>
               <title><![CDATA[".$row["title"]."]]></title>
               <link><![CDATA[".$row["url"]."]]></link>
               <description><![CDATA[".$row["description"]."]]></description>
               <unixdate><![CDATA[".$row["unixdate"]."]]></unixdate>
          </item>";
}
$out.= "     </channel>
</rss>";
if (!$filename) $filename = "xmlout.xml";

file_put_contents($filename, $out); // to write the xml (in $out) to file $filename

?>

The Line Number of the error is here:
file_put_contents($filename, $out); // to write the xml (in $out) to file $filename

Thanks...
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

AndyAelbrechtCommented:
ok, file_put_contents is a PHP5 function ... :-)

to do the same in PHP4:

$fp = fopen($filename, "w");
fwrite($fp, $out);
fclose($fp);

cheers,
Andy
FirstBornAuthor Commented:
Hi Andy,

Thanks for such a QUICK Response!

k... Everything worked out perfectly EXCEPT:

The File Output shows:
<unixdate><![CDATA[0]]></unixdate>
Instead of the unixdate being displayed where the 0 is shown above.

Any Ideas?

Thanks.

FirstBorn
AndyAelbrechtCommented:
for your last question:
how does the data in the "edate" column look ? it's a DATETIME, so it should look like "2006-04-27 00:00:00", right ?

if you have access to phpMyAdmin or the MySQL server itself, try this command:
SELECT `edate` FROM xmldata;
and check the output; it should be something like "2006-04-27 00:00:00" ?

if this is not the case, and you are getting an 11-digit number instead of the date, you already have a unix date
you can replace the UNIX_TIMESTAMP(`edate`) AS unixdate by just edate

$sql = "SELECT title, url, description, edate FROM xmldata ORDER BY 'edate' DESC LIMIT 0,10";

if this is the case, then this is prolly the problem:
notice that i'm using `` (backward single quotes) and not ' ' (normal single quotes) around tablefield names. this is an important thing to remember! so you would put UNIX_TIMESTAMP(`edate`) and not UNIX_TIMESTAMP('edate')

everything between quotes (single and double) is interpreted as a string; which would mean you are trying to get a UNIX_TIMESTAMP from the STRING "edate" and not from the VALUE in the FIELD edate.

so, in short:

first try replacing your ' ' with ` `
if still returning 0 for date, try checking the data in the database (but the more i'm typing here the more i'm sure the single quotes will be the problem)
AndyAelbrechtCommented:
addendum (just have to explain it more, sorry to bore you):

use no quotes around tablefield names if you are certain of their name
use `` around tablefield names if their name is also a mysql keyword.
use '' or "" around Strings

example field names:

edate -> no prob -> edate
date -> mysql keyword, so problem -> `date`
time -> mysql keyword, problem -> `time`

in your ORDER BY you also have a 'edate' (with the single quotes). replace those with the backward quotes `` or remove them alltogether.
FirstBornAuthor Commented:
Hi Andy,

Thanks for explaining about the 'back quotes'...

That did the Trick.

I'm going to award the points now.
I didn't know that the back quotes were right when I executing mySQL Queries,
so I changed them to regular quotes and I learned something NEW today...

Thanks!

FirstBorn

PS.  I used edate for the row name because 'date' is a mySQL keyword...
I've dealt with keywords in VB and SQL Server 7 when converting over OLD DB IV files and a clipper program that
used the MS keywords in their field names (used foxpro for the tables originally...)
So, That's why I used edate instead of just 'date'...
My original Q above had 'date' as the fieldname 'cause I was in the midst of working with the Table Defs and just typed it up quickly...
Just an FYI...
Thanks.
:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.