• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

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!
0
JimmyJack123
Asked:
JimmyJack123
  • 4
  • 3
1 Solution
 
GawaiCommented:
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
0
 
JimmyJack123Author Commented:
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)

0
 
JimmyJack123Author Commented:
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

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
GawaiCommented:
try
LIKE '%$v1%'
or

LIKE '%$v1'
0
 
JimmyJack123Author Commented:
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

0
 
GawaiCommented:
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>";
0
 
JimmyJack123Author Commented:
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!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now