Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-05-12
7
540 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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&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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

829 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