Jump to content


Photo
* * * * * 2 votes

Creating A Php-sql Rss Feed


  • Please log in to reply
21 replies to this topic

#1 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 02 August 2006 - 12:23 PM

Please view the updated version here

__________________________________________________________________

This tutorial was on my site before It went down and I've decided to bring it here. Hope you like :P
__________________________________________________________________

In this tutorial you will learn how to create a table in phpMyAdmin and create an RSS feed that goes along with it!
Requirements: Know basics of php, phpMyAdmin, notepad, mouse and keyboard.

Now let’s start with our SQL Table.
CREATE TABLE `news` (
  `id` tinyint(11) NOT NULL auto_increment,
  `title` text NOT NULL,
  `author` text NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`id`)
);
That simply created a table called news. The table news contains 4 rows. Each row stands for something different. So in this case, we have 4 rows.

Posted Image
Click to enlarge

Now lets fill the table with some random text so we can see how this RSS works.
INSERT INTO `news` VALUES (1, ‘test1', 'BigDog', ' This is Test 1');
INSERT INTO `news` VALUES (2, ‘test2', 'BigDog', ' This is Test 2');
INSERT INTO `news` VALUES (3, ‘test3', 'BigDog', ' This is Test 3');
If you don’t understand what I did above, I just simply used INSERT TO and inserted values into each row.

Now that we have the table setup, we can start with our RSS Feed for it. Since this is going to be a .php file instead of .xml, we need to specify the server that this file will have xml content.
<? header('Content-type: text/xml'); ?>

Now lets connect to our database.
<?php
$dbhost = "localhost"; // almost always localhost.
$dbname = "news";	  // Database Name, In our case, its news
$dbuser = "UserName";  // Database Username
$dbpass = "Password";  // Databse Password

$connect = mysql_connect("$dbhost","$dbuser","$dbpass");// Connecting to Database
mysql_select_db($dbname) or die (mysql_error());		  //  Selecting Database 
?>

Now that we have specified our username, password, and table, we can start the main rss.

<rss version="2.0">
	<channel>
		<title>Dark Pixels Tutorials</title>
	 <description>Latest tutorial on Dark Pixels</description>
	  <link>http://darkpixels.net/</link>
This code above is like any other rss/xml feed. We are using rss version 2.0. You can change title, description, and link to your needs. Title can be anything but normally it’s your site name. Give a little information about your site in the description and your websites link in the <link>

<?
$sql = "SELECT * FROM news limit 5";  
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
?>
The above code is simply getting a query from our sql table. You can change
$sql = "SELECT * FROM news limit 5" to anything you want! Change the limit, table, or even add more information like WHERE author = 'BigDog'. That will only show the submissions by BigDog.

Hold on, we are almost done!
Now lets get the main rss.

<item>
<title><?=$row['title']; ?></title>
<author><?=$row['author']; ?></author>
<link>http://MYSITE.com/news.php?id=<?=$row['id']; ?></link>
</item>
Now lets explain.
the <item> tag defines an row the RSS/SQL feed. Since we have it so it would show 3 rows from our table, at the end you will have 3 <item> tags.

The <title> tag is what will show up in the rss. If you use Mozilla Firefox, it’s the name that shows up when you select the drop down menu.
The <author> tag shows the owner of the news or content. In our case, it would be BigDog.
The <link> tag will show the ID for us. But as you can, I've added a link before it. That link is for my news. So If I have a page called news.php and news.php?id=# will show the specified news, that will link it to the specified contact/news.

And that’s it. Now let’s end our tags and we are done!
<?
}
?>

</channel>
</rss>

Here is our final code!
<? header('Content-type: text/xml'); ?>

<?php
$dbhost = "localhost"; // almost always localhost.
$dbname = "news"; // Database Name, In our case, its news
$dbuser = "UserName"; // Database Username
$dbpass = "Password"; // Databse Password

$connect = mysql_connect("$dbhost","$dbuser","$dbpass");// Connecting to Database
mysql_select_db($dbname) or die (mysql_error()); // Selecting Database
?> 

<rss version="2.0">
<channel>
	<title>Test Title</title>
	<description>This is an example description</description>
	<link>http://pixel2life.com</link>

<?
$sql = "SELECT * FROM news limit 5";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
?>

<item>
	 <title><?=$row['title']; ?></title>
	 <author><?=$row['author']; ?></author>
	 <link>http://MYSITE.com/news.php?id=<?=$row['id']; ?></link>
</item>

<?
}
?>

</channel>
</rss>

Thank you for reading.

Edited by Faken, 12 February 2007 - 09:58 PM.


#2 _*-cherries-_*

_*-cherries-_*
  • Guests

Posted 02 August 2006 - 12:26 PM

why are you using tinyint?
it be just as easy using int.


CREATE TABLE news (
id int NOT NULL auto_increment,
title text NOT NULL,
author text NOT NULL,
content text NOT NULL,
PRIMARY KEY (id)
)

that would work just as well.

#3 Matthew.

Matthew.

    Official Spammer .Matt

  • Members
  • PipPipPipPip
  • 2,749 posts
  • Gender:Male
  • Location:England

Posted 02 August 2006 - 12:28 PM

It must be tinyint as if you use INT then the max number of entries you can have is 127. You will just get a duplicate key error when you reach that amount.


tinyint is much much higher.

Edited by .Matt, 02 August 2006 - 12:32 PM.


#4 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 02 August 2006 - 12:38 PM

:-) Ya, I did INT first then changed it to tinyint.

#5 Indigo

Indigo

    Official Alien

  • Members
  • PipPipPip
  • 617 posts
  • Gender:Male
  • Location:Trondheim, Norway
  • Interests:Computing in general, especially design and programming of all kinds.

Posted 03 August 2006 - 04:54 PM

Requirements: Know basics of php, phpMyAdmin, notepad, mouse and keyboard.


OH NO, NOT MOUSE AND KEYBOARD :P
Ok, seriously: This seems rather good. Thanks for sharing!

#6 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 03 August 2006 - 06:06 PM

I know I know, I rased the requirements to a mouse and keyboard. I am so sorry :P

And you're welcome.

#7 α∂αмяoss

α∂αмяoss

    P2L Jedi Master

  • Members
  • PipPipPipPip
  • 2,102 posts
  • Gender:Male
  • Location:$_SERVER['REMOTE_ADDR']
  • Interests:football, Manchester Utd., coding, web developement, business, girls and warcraft.

Posted 13 August 2006 - 03:53 PM

w00t, finally a tutorial that is explained and not another long piece of code snippet!

#8 ChrisGilmore

ChrisGilmore

    Young Padawan

  • Publishing Betazoids
  • Pip
  • 147 posts
  • Location:Nottinghamshire, East Midlands

Posted 15 August 2006 - 04:50 AM

Thanks, I spent the whole day once trying to find how to create RSS feeeds which explained it. I love it.

#9 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 16 August 2006 - 05:24 PM

Thanks for the feedback guys.

#10 α∂αмяoss

α∂αмяoss

    P2L Jedi Master

  • Members
  • PipPipPipPip
  • 2,102 posts
  • Gender:Male
  • Location:$_SERVER['REMOTE_ADDR']
  • Interests:football, Manchester Utd., coding, web developement, business, girls and warcraft.

Posted 17 August 2006 - 04:42 PM

BTW. Is this code RSS validated?

#11 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 17 August 2006 - 07:17 PM

No its not RSS validated and I am not worried about validation. If you are, you can go ahead and change the code to fit validation. This is simply suppose to teach people how to make one and I don't think any beginner wants to be perfect.

#12 α∂αмяoss

α∂αмяoss

    P2L Jedi Master

  • Members
  • PipPipPipPip
  • 2,102 posts
  • Gender:Male
  • Location:$_SERVER['REMOTE_ADDR']
  • Interests:football, Manchester Utd., coding, web developement, business, girls and warcraft.

Posted 18 August 2006 - 07:53 AM

Lol ok...

#13 Faken

Faken

    Pimpmaster G

  • Admin
  • 5,966 posts
  • Gender:Male
  • Location:Montreal, Canada

Posted 09 November 2006 - 11:16 AM

Thanks for posting this BigDog :laugh: Hope you will move this to the publishing system when we move :D

Dan

#14 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 09 November 2006 - 06:48 PM

I had not been at P2L for a bit and had to catch up on the reading.

Like the publishing system and I will definitely move this to the Publishing system section.

And you are welcome.

#15 bfreddyberg

bfreddyberg

    Young Padawan

  • Members
  • Pip
  • 2 posts

Posted 09 November 2006 - 10:55 PM

It must be tinyint as if you use INT then the max number of entries you can have is 127. You will just get a duplicate key error when you reach that amount.

tinyint is much much higher.


Ummm you have that a little backwards. tinyint(length) can store integers from -128 to 127. But as int(length)
can store integers from -2147483648 to +2147483647, but can be limited commonly as 11.

So to correct you int is much much higher, as tinyint is what you would expect it to be....tiny.

Sorry I just couldn't let this slip my mind letting people have false information.


Great tutorial there BigDog! Works like a charm just retrieving data from the news table i already had! :D

#16 BigDog

BigDog

    Young Padawan

  • Members
  • Pip
  • 277 posts
  • Gender:Male
  • Location:Orange County, California
  • Interests:Running, building computers, PC games and BMX and programming.

Posted 09 November 2006 - 11:06 PM

Thank you bfreddyberg, means a lot. Glad it works with your system. I tried to make it as easiest and it can get :-)

#17 Gravity

Gravity

    Young Padawan

  • Members
  • Pip
  • 2 posts

Posted 13 November 2006 - 12:35 PM

Thanks, This is very usefull.

#18 Figgy

Figgy

    Young Padawan

  • Members
  • Pip
  • 5 posts
  • Gender:Male

Posted 18 November 2006 - 06:42 AM

It must be tinyint as if you use INT then the max number of entries you can have is 127. You will just get a duplicate key error when you reach that amount.


tinyint is much much higher.


Erm, please look up these kind of things before you say something!
http://dev.mysql.com...eric-types.html

tinyint -> 127 entries
int -> 2147483647 entries
bigint -> 9223372036854775807 entries

Int is more than enough - tinyint is not.
Have a nice day!

Edit: Ok, I just noticed there was a post that corrected your misstake, but niether could I let you give false information.

Edited by Figgy, 18 November 2006 - 07:14 AM.


#19 Mahmood

Mahmood

    Young Padawan

  • Members
  • Pip
  • 2 posts

Posted 02 January 2007 - 01:38 PM

nice :huh:
Thanks for the tutorial... I was looking for this...

#20 Anobiz

Anobiz

    Young Padawan

  • Members
  • Pip
  • 16 posts

Posted 22 January 2007 - 11:19 AM

I was just wondering abt rss feeds :D thanks very much for your effort and yes I will remember I learnt it from BigDog whenever I'm going to use RSS feeds ;)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users