Link to home
Start Free TrialLog in
Avatar of JimmyJack123
JimmyJack123

asked on

Create an RSS feed from a SQL query with a variable being passed

Hi There!

Wondering if someone would be able to help me...

I have this MYSQL select statement that I would like to be able to use to generate a RSS from that has a variable at the end of the URL that would be used as a part of the statement.

Example of what I mean here is;

I would like the end user to be able to put in a URL like this
http://www.yoursite.com/RSS.php?id=% 

with % being the obvious variable.. Now when the user enters that, I would like this script to generate the RSS and then display the generated results from this select statement.

SELECT b.bossname, b.instancename,  k.dateentered FROM kills k LEFT JOIN bosses b ON k.bossid = b.bossid LEFT JOIN guilds g on k.guildid = g.userid
WHERE g.guildname = '%'
AND b.activeind = 1
ORDER BY dateentered DESC LIMIT 10

Is anyone able to help me out here? point me in the right direction or show me how its done? - I am very new when it comes to RSS and XML =)

Many Thanks!
Avatar of Emad Gawai
Emad Gawai
Flag of United Arab Emirates image

i dont understand why are u using only % sign ?

try using LIKE operator instead. not sure if it works
WHERE g.guildname LIKE '%'


let me see the structure and few records of the tables
Avatar of JimmyJack123
JimmyJack123

ASKER

I think your misunderstanding me there.. I'm not really fussed about the SQL in itself, if I use LIKE or = or whatever.. Its more after how I could turn the result set of said SQL into an RSS feed...

So in short, turn the SQL above into something like this;

http://www.abc.net.au/news/indexes/justin/rss.xml

just being able to use where the % is as a variable one can put into the URL. (if at all possible)

Ok, I think im getting close after much much consumption of coffee..

Okies, im using this code attached, as you can see its incorporating the SQL from above but I am not sure where i am going wrong. I think its with the passing the variable from the URL using

$v1 = $_GET['guildname']

So it would be something like http://mysite.com/rss/rss.php?guildname=blah

Because when I cange the WHERE part to a static like WHERE g.guildname LIKE '%blah' it I seem to be pulling information back.

Any ideas where im going wrong?

<?php $db = new mysqli("<removed>"); ?>
<?php header('Content-type: text/xml'); ?>
<?php echo "<?";?>xml version="1.0" encoding="iso-8859-1"<?php echo "?>";?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
<title>Your Website Title</title>
<description>A brief description about your website.</description>
<link>http://yourdomain.com/</link>
<copyright>Your Copyright Information</copyright>
<atom:link href="http://yourdomain.com/feed/" rel="self" type="application/rss+xml" />
 
<?php
$v1 = $_GET['guildname']; 
$query = "SELECT b.bossname as boss, b.instancename, k.killid, k.dateentered FROM kills k LEFT JOIN bosses b ON k.bossid = b.bossid LEFT JOIN guilds g on k.guildid = g.userid
WHERE g.guildname like '%".$v1."'
AND b.activeind = 1
ORDER BY dateentered DESC LIMIT 10";
$results = $db->query($query);
$number = $results->num_rows;
 
for ($i = 1; $i <= $number; $i++) {
 
$row = $results->fetch_assoc();
 
$boss = htmlentities($row['boss']);
$instancename = $row['instancename'];
$killid = $row['killid'];
$dateentered = $row['dateentered'];
 
?>
<item>
<title><?php echo $boss; ?></title>
<description><?php echo $instancename; ?></description>
<link><?php echo $killid; ?></link>
<pubDate><?php echo $dateentered; ?></pubDate>
<guid><?php echo $killid; ?></guid>
</item>
<?php
 
}
 
?>
</channel>
</rss>
<?php
 
$db->close();
 
?>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Emad Gawai
Emad Gawai
Flag of United Arab Emirates image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Wow that worked a treat, I just have one more problem in line with the same subject, but that is if it is possible to put a variable into the link based upon the data returned from the sql result.

eg, with this line,
<link><?php echo $killid; ?></link>

I would like it linked to http://yoursite.com/getfile.php?id=$killid&imagekind=P

that should work.

echo "<a href=\"http://yoursite.com/getfile.php?id=$killid&imagekind=P\"></a>";

or try with
echo "<a href=\"http://yoursite.com/getfile.php?id=$killid&imagekind=P\"></a>";
It was close, but not quite.

I found I had to do it this way.

<link>http://yoursite.com/getfile.php?id=<?php echo $killid; ?></link>

Thanks for your help gawai!