Solved

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

Posted on 2009-05-12
7
541 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
[X]
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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

749 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