tclogo



HotelsCombined.com

Pages

Archives

Categories


Recent Postings

Recent Comments

Feeds

RSS2 feed

Links

Most Popular Posts

Creating an RSS Feed using Php and MySQL

Saturday, 10th February 2007 12:44am
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.




Ishmael wrote on Thursday, 1st March 2007 12:05am
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

Wes wrote on Thursday, 1st March 2007 9:02am
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

Dan wrote on Wednesday, 4th June 2008 1:44am
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

Wes wrote on Friday, 6th June 2008 8:53pm
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

Sander wrote on Wednesday, 17th June 2009 10:13pm
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

Busby SEO wrote on Friday, 9th January 2009 2:49pm
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

Wes wrote on Tuesday, 13th January 2009 10:59pm
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

Rhoov wrote on Friday, 24th July 2009 6:55pm
Have been looking for db include but couldn't find it
Could you point the exact location pls
Thx in advance
A noob ;)

reply

Rhoov wrote on Friday, 24th July 2009 10:28pm
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

Wes wrote on Friday, 24th July 2009 10:48pm
Sorry for that, the bug that caused the error has now been fixed.
reply

Rhoov wrote on Friday, 24th July 2009 6:56pm
Haven't found db include, could you point the exact location plz
tnx
A Noob ;)
reply

Wes wrote on Friday, 24th July 2009 10:21pm
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

Rhoov wrote on Saturday, 25th July 2009 1:28am
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

Wes wrote on Monday, 27th July 2009 9:53pm
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

Rhoov wrote on Monday, 27th July 2009 11:20pm
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...

Post a comment:

 

(required)

(required, but not published)

(optional)





Notify me of follow-up comments via e-mail

Wes wrote on Tuesday, 28th July 2009 12:22am
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 wrote on Tuesday, 28th July 2009 1:09am
I would greatly appreciate that wes...
reply

Wes wrote on Friday, 31st July 2009 11:25pm
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

rhoov wrote on Saturday, 1st August 2009 3:39am
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

Myke wrote on Friday, 14th August 2009 7:04am
This is a really nice script. Good for the arsenal!
reply

Ronald wrote on Wednesday, 30th September 2009 11:26pm
Thanks. Perfect script! I'm using a cronjob to run the php file every minut :-)
reply

ker wrote on Tuesday, 22nd December 2009 9:52am
nice script I was using webreference script, it get me an error, I struggled for 4 weeks ,but this was so smooth
reply

ker wrote on Tuesday, 22nd December 2009 2:45pm
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

Wes wrote on Tuesday, 22nd December 2009 9:01pm
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

ker wrote on Wednesday, 23rd December 2009 4:30am
I checked on feed.php it says table on database does not exist
reply

Wes wrote on Wednesday, 23rd December 2009 2:38pm
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

dheenathayalan.k wrote on Friday, 29th January 2010 9:20pm
im very happy to say my comments, the scripts looks very simple.... and a beginner can easily understand.... keep helping...thanks
reply

ker wrote on Thursday, 11th March 2010 2:33am
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

Wes wrote on Sunday, 14th March 2010 11:49pm
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

ker wrote on Monday, 15th March 2010 6:23am
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

ker wrote on Monday, 15th March 2010 6:28am
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

Wes wrote on Monday, 15th March 2010 11:57pm
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

ker wrote on Tuesday, 16th March 2010 1:19am
Thanks wes but i have a problem,can you put it in codes for me ,i'm not very good in it.
reply

ker wrote on Tuesday, 16th March 2010 1:36pm
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

Wes wrote on Tuesday, 16th March 2010 10:07pm
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

ker wrote on Wednesday, 17th March 2010 3:07am
now i got it,what i should do about and coz i have different url for each database example
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

nalaka wrote on Thursday, 6th January 2011 5:24am
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 "


Example
This is an example site http://localhost
en-us";

}
Function GetBody() {
$db = new Db();
$db->GetFeedData();
$strBody= "";
for($i=0; $iarData); $i++){
$desc=$db->arData["desc"][$i];
$time=$db->arData["time"][$i];
$id=$db->arData["id"][$i];
$heading = $db->arData["heading"][$i];
$strBody= $strBody . "

$heading
$desc
http://localhost/landcorp/news_item.php?id=$id $time 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

Wes wrote on Tuesday, 11th January 2011 11:46am
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