Solved

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

Posted on 2009-05-12
7
536 Views
Last Modified: 2013-12-12
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
Comment
Question by:JimmyJack123
  • 4
  • 3
7 Comments
 
LVL 20

Expert Comment

by:Gawai
ID: 24362684
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
 

Author Comment

by:JimmyJack123
ID: 24362893
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
 

Author Comment

by:JimmyJack123
ID: 24372455
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 20

Accepted Solution

by:
Gawai earned 500 total points
ID: 24372681
try
LIKE '%$v1%'
or

LIKE '%$v1'
0
 

Author Comment

by:JimmyJack123
ID: 24372814
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
 
LVL 20

Expert Comment

by:Gawai
ID: 24373399
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&amp;imagekind=P\"></a>";
0
 

Author Comment

by:JimmyJack123
ID: 24375527
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now