Creating an RSS Feed using Php and MySQL
In this post, I will illustrate Php code that can be used to create an RSS feed containing the required channel elements plus pubDate and guid using a MySQL database. This code assumes Php 5.* .
I have a used a table called post where all the posts are stored. This is a simple table with fields postid, heading, description and timestamp where heading is effectively the title and timestamp is the time of inserting the post in the database.
The class Db is a small class that handles the database side and connects to the MySQL database through the class constructor. The GetFeedData function retrieves feed data and store the data in an array. The FormatDate function formats the date to a format which includes the GMT time difference.
The Feed Class is involved with the creation of the feed.xml file. The CreateFeed function calls three functions which create the head, the body and the footer of the RSS feed. I think putting in three functions makes it easy if you want to add exception handling around the file creation process.
The last file or class not shown will just call the CreateFeed function to start the process. The CreateFeed can be called after inserting data into the post database, say using a form to create the RSS feed.
The code and the table can be expanded upon to include other optional channel elements like copyright, image etc.
I find this tutorial very useful. Is it possible for one to update the feeds using my mobile device (Cell phone)? If so, can you please provide such a tutorial as this excellent one you have.
reply
Thanks Ishamael for the compliment. Unfortunately I haven't figured out yet how to update a feed using a mobile device but once I do figure it out I will surely post it here. You may be interested to know though I will soon be posting a tutorial on how get an SMS message whenever you get a comment on say a blog or forum using PHP.
reply
Hi - I love how simple your script looks. I wonder though if you might have any advice on my installation. I have created the feed.php and Db.php, reworked the fields for my table and modified the connection details. Everything appears correct, and when I run the feed.php script I receive no errors. In fact, I receive nothing - just a blank screen. (I have inserted an error reporting script at the top of both files to display all errors and warnings in the script). It appears as though everything is running correctly but the xml file is not being updated. Any help would be appreciated!
reply
Hi Dan, all I can think of at the moment is that it could be something to do with file permissions possibly but I have never had to do anything special with permissions myself.
Another thing to do is to put some echo debug statements to see whether its actually running the methods because with a blank screen you really cant tell what has happened or hasn't. I also think a debug echo statement before and after the fclose($file) might also be helpful and work upwards from there.
I hope you will successfully resolve the problem. Thanks.
reply
For the people who can't get it to work and get a blank screen, you still have to call the function. Add this under the db include at the feed.php: $Feed = new Feed(); $Feed->CreateFeed(); Now also create a file in the same directory called feed.xml and amke sure it has 775 chmod rights.
reply
very useful tutorial. How to make this feed more simple. I have title, date and search count number in my DB. I only want to show Title and date in my RSS is it possible ?
reply
Yes Busby its possible to just show just the title and the date only in your feed. Infact in this example I have omitted things like author etc which could be part of the RSS. Just include the title and date only in the query when you retrieve the data from the database and then in the string $strBody above just leave the tags title and pubdate. Hope this helps.
reply
Have been looking for db include but couldn't find it
Could you point the exact location pls
Thx in advance
A noob ;)
reply
That's was funny, got a db error when posting, so posted again, DB error again, and now 3 hours later my post's appear
reply
Sorry for that, the bug that caused the error has now been fixed.
reply
Haven't found db include, could you point the exact location plz
tnx
A Noob ;)
reply
Rhoov, the include file Db.php referred to in the script is in the lower half of the script. It starts after the comment '//class Db, Db.php'. After that comment that code is for the include file. I hope its clear now. Thanks.
reply
Wes, I need to put this in $Feed = new Feed(); $Feed->CreateFeed(); i have put in the feed.php under require_once('Db.php'); , thought that was was meant with under the include db.php So what i mean is that i have a blanco page, and i read the comment from sander.. need i put in the feed.php or the db.php and where exactly.. I feeling such a nob right now Rhoov
reply
Rhoov, what Sander refers to above works. You add those statements just below the require_once statement. That will mean that the script will straight away the moment you hit the file feed.xml if that's how you want to run the script.
As for the files themselves you can put them in the same folder or in separate ones and if in separate ones that must be reflected in the require_once statement.
After running the script check to see that the file feed.xml gets populated with the data coming from your database.
If you would like to get some output, you can also add some echo statements to see where the script is and see where its failing if you are still having problems. Feel free to modify as required.
reply
Wes,
I had a look at the feed.xml and it get's populated, only thing is that when i want to view the xml in my browser i get the following error: syntax error, unexpected T_STRING in/path_to _webdir/feed.xml on line 1
So i guess that is why i get an empty page even when i put the $Feed = new Feed(); $Feed->CreateFeed(); below the require_once.
The xml first line is:
Sorry to be such a pain in the B...
In that case I would suggest you open the xml file in a text editor like Notepad and check and see why the xml file is giving you an error. In that case you may need to correct the script as appropriate. Meanwhile, if I get some time this week I will get a script and send it to you and you can check and see if it works in your environment. Thanks, cheers.
reply
Rhoov, I have created three files to show what Sander was referring to above and you can get them here. You can put the three files in the same folder. In the file Db.php change the db settings to reflect your db settings and also the table. By going to feed.php in a browser the file feed.xml is populated with the feed data and you get the message "Feed created!". Not elegant but I hope it helps.
reply
Wes, thanx for your work. i got the same error.
The feed is created but nothing shows. When i open the xml i see it's nicely populated, but when running it in the browser i get Parse error: syntax error, unexpected T_STRING in ... on line 1 ?
could i be something with apache not reading this right ?
reply
This is a really nice script. Good for the arsenal!
reply
Thanks. Perfect script! I'm using a cronjob to run the php file every minut :-)
reply
nice script I was using webreference script, it get me an error, I struggled for 4 weeks ,but this was so smooth
reply
I have put this code but it shows feed only on firefox ,if i try on chrome or IE i does not show,it say This feed contains code errors.on ie and error on line 7 at column 27: Extra content at the end of the document on chrome.
whats wrong?
reply
Ker, I am not so sure what the problem could be in this case but there could an extra character that maybe Firefox is ignoring but IE and Chrome are throwing an error. I would suggest that you use the W3C feed validator to cheek the feed and see what errors or warnings it gives you and then take it from there.
I hope you will be able to resolve this, cheers.
reply
I checked on feed.php it says table on database does not exist
reply
Good to see that you are making some progress on trying to find what the problem is. You could also try to query the database directly to make sure that you are able to retrieve all the data you want for your feed. Cheers.
reply
im very happy to say my comments, the scripts looks very simple.... and a beginner can easily understand.... keep helping...thanks
reply
hi i used your script to pull out data for feed from different database but now what i want to do is, i have scripts for three database ,what i mean is each scripts pulling out feed from it's own database but now all i want is all of these three scripts to write on the same feed.xml file. how do i do that ?.coz i tried right now but only one script write to feed.xml file ,the rest does not.
reply
Ker, if I understand what you mean, what I think you could do is write a query, possibly using a union that will get results from your three databases and then get the the top n results according to your ordering to be written to the feed.xml
So in the example above the union query will be in the GetFeedData function. Hope this helps. Cheers
reply
I was thinking of some thing like that but what about if i wanna connect to three different databases then pulling data from tables of these databases,i think union query if you connect to single database then pulling data from different tables on the same database.in my case is pulling data from three different databases then pulling data from tables on these three databases
reply
This is what i mean I was wondering how it may be possible to query 2 MySQL databases using one query statement from PHP. For instance: SELECT database1.tableA.field1 UNION database2.tableB.field2. My concern is, when connecting to MySQL (or sending a query), I only specify 1 database connection resource ID. How does that play out when connecting to 2 databases?
reply
I see what you mean. You can actually query across two or more MySql databases in a PHP script. The trick is to connect to both databases before you execute the query.
So in this case you will need to make all the connections to the databases in the constructor. It doesn't matter whether the databases are on the same server or not and if on different servers use the IP addresses.
So your union query will be like the normal union query but with full database and table names like:
SELECT database1.tableA.field1 as field1,database1.tableA.field2 as field2
UNION
SELECT database2.tableB.field1 as field1,database2.tableB.field2 as field2
and to order you can wrap another query on top like:
select field1, field2 FROM
(SELECT database1.tableA.field1 as field1,database1.tableA.field2 as field2
UNION
SELECT database2.tableB.field1 as field1,database2.tableB.field2 as field2)
as U1
ORDER BY field1
Hope this helps. I have used two databases as an example but you can have more than that. Cheers.
reply
Thanks wes but i have a problem,can you put it in codes for me ,i'm not very good in it.
reply
Another problem is all these database has a different urls on the feed that i want to make, it would be better if can make all these scripts write on the same feed.xml
reply
I think to meet your requirements you only need to change just two functions I will quickly list the changes that need to be done here. I haven't tested this but I think it should work, may need some fine adjustments.
Add the three connections to the class Db's constructor:
function __construct()
{
mysql_connect('host1', 'user1', 'password1');
mysql_select_db('dbname1');
mysql_connect('host2', 'user2', 'password2');
mysql_select_db('dbname2');
mysql_connect('host3', 'user3', 'password3');
mysql_select_db('dbname3');
}
That will provide the three connections required to connect to the three databases.
The second change will be required in the GetFeedData function. The change required there is just to the query. The rest of the function stays as it is.
A union query could be a good idea if one needs just one query as explained earlier. So better modify the union query as mentioned in an earlier comment above.
The rest of the script should stay as is because it deals with the data that the query spits out and therefore it doesn't matter where it has come from it will just save it all in one feed.xml file.
Cheers.
reply
now i got it,what i should do about and
database1 = http://www.owolog.com/muziki/muzik.htm?pid=$id
database2= http://www.owolog.com/blog/blog.htm?pid=$id
database3= http://www.owolog.com/sports/index.htm?pid=$id
reply
hi thanx for the codes, i put the coding but it will not show anything on my browser..
feed.php
require_once('db.php');
Class Feed {
Function GetHeader(){
return "
}
Function GetBody() {
$db = new Db();
$db->GetFeedData();
$strBody= "";
for($i=0; $i
$desc=$db->arData["desc"][$i];
$time=$db->arData["time"][$i];
$id=$db->arData["id"][$i];
$heading = $db->arData["heading"][$i];
$strBody= $strBody . "
http://localhost/landcorp/news_item.php?id=$id
}
return $strBody;
}
Function GetFooter(){
return "
}
Function CreateFeed(){
$file = fopen("feed.xml", "w");
fwrite($file, $this->GetHeader());
fwrite($file, $this->GetBody());
fwrite($file, $this->GetFooter());
fclose($file);
}
}
?>
db.php
class Db {
public $arData;
function __construct()
{
mysql_connect('localhost', 'admin_landcorp', 'adminlandcorp');
mysql_select_db('landcorp');
}
Public function GetFeedData(){
$select_sql = "SELECT NEWS_ID, NEWS_TITLE, NEWS_CONTENT,
NEWS_TIME_UPDATED from news order by NEWS_ID DESC limit 5";
$result = mysql_query($select_sql);
$num_rows = mysql_num_rows($result);
if(!$result || ($num_rows < 0)) {
die("error : " . mysql_error() );
}
if($num_rows == 0){
echo "Database table empty";
exit;
return;
}
for($i=0; $i<$num_rows; $i++){
$this->arData["id"]
[$i]=mysql_result($result,$i,"NEWS_ID");
$this->arData["heading"][$i]=mysql_result($result,$i,"NEWS_TITLE");
$this->arData["desc"][$i]=mysql_result($result,$i,"NEWS_CONTENT");
$this->arData["time"][$i] = $this->FormatDate(
mysql_result($result,$i,"NEWS_TIME_UPDATED"),"D, j M Y H:i:00 O");
}
}
function FormatDate($dtDbDate, $strDateFormat){
//leave numbers only in date
$dtDbDate = ereg_replace('[^0-9]', '', $dtDbDate);
//extract date and time components
$strSecond = substr($dtDbDate,12,2);
$strMinute = substr($dtDbDate,10,2);
$strHour = substr($dtDbDate,8,2);
$strDay = substr($dtDbDate,6,2);
$strMonth = substr($dtDbDate,4,2);
$strYear = substr($dtDbDate,0,4);
//Get date format for feed
$dtNewFormat = date($strDateFormat, mktime($strHour,
$strMinute, $strSecond, $strMonth, $strDay, $strYear));
return $dtNewFormat;
}
}
?>
reply
Nalaka, maybe try to enter some echo debug statements and see where the code is failing and why you are getting a blank screen.
reply