Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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