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!
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!
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)
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)
ASKER
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?
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();
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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>";
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>";
ASKER
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!
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!
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