?
Solved

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

Posted on 2006-04-11
8
Medium Priority
?
565 Views
Last Modified: 2006-11-18
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>

*****************************
0
Comment
Question by:FirstBorn
  • 5
  • 3
8 Comments
 
LVL 4

Accepted Solution

by:
AndyAelbrecht earned 2000 total points
ID: 16433743
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
0
 
LVL 4

Expert Comment

by:AndyAelbrecht
ID: 16433751
ow right, forgot:

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

Author Comment

by:FirstBorn
ID: 16444619
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...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:AndyAelbrecht
ID: 16444690
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
0
 
LVL 1

Author Comment

by:FirstBorn
ID: 16444876
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
0
 
LVL 4

Expert Comment

by:AndyAelbrecht
ID: 16445135
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)
0
 
LVL 4

Expert Comment

by:AndyAelbrecht
ID: 16445192
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.
0
 
LVL 1

Author Comment

by:FirstBorn
ID: 16445339
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.
:)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question