Solved

Retrieve data from MySQLl database in xml file

Posted on 2011-02-27
14
183 Views
Last Modified: 2013-11-30
I have a rss xml file which works fine please see below code. I will replace title, link, description and date with data from MySQL database.

<?xml version="1.0"?>
<rss version="2.0" xmlns:atom="http://-.info/rss/Atom">
   <channel>
      <title>TITLE</title>
      <link>http://-.info/</link>
      <description>Whatever Company  News</description>
      <language>en-us</language>
      <pubDate>Mon, 4 Feb 2008 04:00:00 GMT</pubDate>

      <lastBuildDate>Mon, 4 Feb 2008 04:00:00 GMT</lastBuildDate>
      <docs>http://127.0.0.1/rss</docs>
      <generator>Weblog Editor 2.0</generator>
       
        <item>
         <title>News Article #1</title>
         <link>http://www.--.htm</link>
         <description>Whatever new article</description>
         <pubDate>Mon, 4 Feb 2008 04:00:00 GMT</pubDate>
         <guid>http://www.--.htm</guid>
      </item>
       
        <item>
         <title>News Article #2</title>
         <link>http://www.--.htm</link>
         <description>Whatever new article</description>
         <pubDate>Mon, 4 Feb 2008 04:00:00 GMT</pubDate>
         <guid>http://www.--.htm</guid>
      </item>
         
      </channel>
</rss>
0
Comment
Question by:sam20
  • 6
  • 6
14 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 34991610
This looks like RSS, not ATOM, so you might want to check the doctype declaration.

Here is how I would do it... Use HEREDOC syntax to create three strings.  The first string would contain the top of the XML and the channel info.  The second string would contain the XML that defines the item(s).  The third string would contain the closing tags for the channel and the rss.  Concatenate the strings as you retrieve information from the data base.
http://www.php.net/manual/en/language.types.string.php

Something like the (obviously untested and incomplete) code snippet might be used to create one of the item strings.
$sql = "SELECT my_title, my_link, my_description, my_date FROM my_table";
$res = mysql_query($sql) or die( mysql_error() );
while ($row = mysql_fetch_assoc($res))
{
    $t = htmlentities($row["my_title"]);
    $l = $row["my_link"];
    $d = htmlentities($row["my_description"]);
    $m = date('r', strtotime($row["my_date"]));

    $item = <<<ENDITEM
      <item>
         <title>$t</title>
         <link>$l</link>
         <description>$d</description>
         <pubDate>$m</pubDate>
         <guid>$l</guid>
      </item>
ENDITEM;

    echo $item;
}

Open in new window

0
 

Author Comment

by:sam20
ID: 34992036
Hi Ray, thanks for your reply. I'm not good at xml, I try display MySQL data in other sites and found the rss xml file. can you please tell me more abou the code. Should I run the code as a php file?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34992066
No, you should not run the code (obviously untested and incomplete).  You would need to adapt it to your specific application needs.  You can learn more about RSS here:
http://cyber.law.harvard.edu/rss/rss.html

If you already have the data base set up, and you can post the CREATE TABLE statements for the relevant table(s) we may be able to show you a better code example.  But it may take a long time since we do not have your test data!
0
 

Author Comment

by:sam20
ID: 34992128
CREATE TABLE `lastnews` (
  `id` int(12) unsigned NOT NULL auto_increment,
  `title` varchar(1500) character set utf8 collate utf8_persian_ci NOT NULL,
  `code` varchar(100) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=496 ;
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 34992300
Thanks.  Looks like we are missing some information that would be needed for RSS.  Description and Link would be very useful.  You can probably omit them or provide an empty placeholder.

Important Question: Is the title really Persian and UTF-8?  If so you may want to allow some extra debugging time for character set issues.
0
 

Author Comment

by:sam20
ID: 34992370
Code is actuelly url and title is insted of description here. Title is in persian languge.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34992414
OK, if the title column is actually the description, you would probably want to add a column to this table to hold the title.  Read over the Harvard link carefully -- it's not perfect, but it is one of the best descriptions of RSS that you can get.
0
 

Author Comment

by:sam20
ID: 34992473
I have added the column description (collate utf8_persian_ci) to the table.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34993111
I think you might want two tables and a junction table.  One would be a channels table.  Each channel table would have a one-to-many relationship with the items table.  The column definitions would be similar because both channels and items have many characteristics in common, but there is a hierarchy - items belong to channels.

The junction table would contain two columns - the key of the channel row and the key of the item row.  Using this table you would be able to associate your channels and items in a "relational" way.

If you're new to web site development using PHP and MySQL, you would probably enjoy this book:
http://www.sitepoint.com/books/phpmysql4/
0
 

Author Comment

by:sam20
ID: 34995153
Hi, I retrieve data from mysql as shows in this way here http://khatesabz.info/, I Will rss to be able to show titles and their links in other websites.  
I will make those tables you have mentioned above.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34998174
Sounds good.  Best of luck with your project, ~Ray
0
 

Author Comment

by:sam20
ID: 34999022
The question is what should I do after I make those new tables?
0
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 39686716
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now