RSS Feed only returns one result

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

RSS Feed only returns one result

Post by ourmaninparis »

Hello,

I've adapted some code to create an RSS feed from my database. It seems to work fine except for the fact that it only returns 1 result when there are many in the database. I'm not very technical (marketing person!) but I'm just trying to build a proof of concept site. Any help would be appreciated. Thanks. Shaun.

Code: Select all

<?php

$username="user";
$password="pass";
$database="dbase";


$rss_title= "This is the title";
$rss_site= "mysite.com" ;
$rss_description= "News from Thiolia";
$rss_language="en";
$rss_logo="http://www.mysite.com/something.png";
$emailadmin="admin@mysite.com";
header("Content-Type: text/xml;charset=iso-8859-1"); 

mysql_connect ("localhost", $username, $password) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db($database) or die(" Unable to select db: ".mysql_error());
$query = "SELECT Notice_Title, Notice_Text, Notice_Link, Notice_Link_Text, Publication_Date, Expiry_Date_Time FROM sgj_notices";
$result = mysql_query($query) or die("Query failed") ; 

echo
'<?xml version="1.0" encoding="ISO-8859-1" ?>
<rss version="2.0">
<channel>
<title>'.$rss_title.'</title>
<link>http://www.'.$rss_site.'</link>
<description>'.$rss_description.'</description>
<language>en-en</language>
<image>
<url>'.$rss_logo.'</url>
<title>'.$rss_site.'</title>
<link>http://www.'.$rss_site.'</link>
</image>'; 

$subject = mysql_result($result,$i,'Notice_Title');
$description = mysql_result($result,$i,'Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'Notice_Link');
$pubdate = mysql_result($result,$i,'Publication_Date');

echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>'.$pubdate.'</pubDate>
</item>
';

mysql_close(); //close the DB
echo '
</channel>
</rss>
';
?>

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: RSS Feed only returns one result

Post by Celauran »

That's because you're using mysql_result and no loop. You need to iterate over each row in the result set.
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: RSS Feed only returns one result

Post by ourmaninparis »

OK. With a For Each I suppose? Sorry for the lack of knowledge!
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: RSS Feed only returns one result

Post by ourmaninparis »

I opted for this but it restricts the number of articles called.

Code: Select all

for($i=0;$i<5; $i++) {

$subject = mysql_result($result,$i,'Notice_Title');
$description = mysql_result($result,$i,'Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'Notice_Link');
$pubdate = mysql_result($result,$i,'Publication_Date');



echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>'.$pubdate.'</pubDate>
</item>
';

} //end of the for-loop

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: RSS Feed only returns one result

Post by Celauran »

Code: Select all

while ($row = mysql_fetch_assoc($result))
{
    // do stuff here
}
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: RSS Feed only returns one result

Post by ourmaninparis »

Thanks but that seems to create a never ending loop.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: RSS Feed only returns one result

Post by Celauran »

It shouldn't.

Code: Select all

<?php

$username="user";
$password="pass";
$database="dbase";


$rss_title= "This is the title";
$rss_site= "mysite.com" ;
$rss_description= "News from Thiolia";
$rss_language="en";
$rss_logo="http://www.mysite.com/something.png";
$emailadmin="admin@mysite.com";
header("Content-Type: text/xml;charset=iso-8859-1"); 

mysql_connect ("localhost", $username, $password) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db($database) or die(" Unable to select db: ".mysql_error());
$query = "SELECT Notice_Title, Notice_Text, Notice_Link, Notice_Link_Text, Publication_Date, Expiry_Date_Time FROM sgj_notices";
$result = mysql_query($query) or die("Query failed") ; 

echo
'<?xml version="1.0" encoding="ISO-8859-1" ?>
<rss version="2.0">
<channel>
<title>'.$rss_title.'</title>
<link>http://www.'.$rss_site.'</link>
<description>'.$rss_description.'</description>
<language>en-en</language>
<image>
<url>'.$rss_logo.'</url>
<title>'.$rss_site.'</title>
<link>http://www.'.$rss_site.'</link>
</image>'; 

/**
$subject = mysql_result($result,$i,'Notice_Title');
$description = mysql_result($result,$i,'Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'Notice_Link');
$pubdate = mysql_result($result,$i,'Publication_Date');
**/

while ($row = mysql_fetch_assoc($result))
{
    echo '
    <item>
    <title>'.$row['Notice_Title'].'</title>
    <link>'.$row['Notice_Link'].'</link>
    <description>'.$row['Notice_Text'].'</description>
    <pubDate>'.$row['Publication_Date'].'</pubDate>
    </item>
    ';
}

mysql_close(); //close the DB
echo '
</channel>
</rss>
';
?>
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: RSS Feed only returns one result

Post by ourmaninparis »

Thanks for the help but that doesn't work either as it returns the same result many times.
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: RSS Feed only returns one result

Post by ourmaninparis »

I'm still struggling with this. The "For" statement works well except that it returns blank lines when my database contains less entries than 20. I'm just looking for a way to loop through and output results as long as the row contains values. Here is the code that I'm trying but as mentioned above it causes my browser to crash when I run it as it seems to enter into a never ending loop:

Code: Select all

mysql_connect ("localhost", $username, $password) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db($database) or die(" Unable to select db: ".mysql_error());

$query = "SELECT t1.*, t2.*
FROM sgj_notices AS t1 INNER JOIN sgj_contentbuilder_records AS t2 ON (t1.storage_id=t2.reference_id AND t1.id=t2.record_id)";

$result = mysql_query($query) or die("Query failed") ; 

echo
'<?xml version="1.0" encoding="ISO-8859-1" ?>
<rss version="2.0">
<channel>
<title>'.$rss_title.'</title>
<link>http://.'.$rss_site.'</link>
<description>'.$rss_description.'</description>
<language>en-en</language>
<image>
<url>'.$rss_logo.'</url>
<title>'.$rss_site.'</title>
<link>http://www.'.$rss_site.'</link>
</image>'; 

while($row = mysql_fetch_array($result)) {

$subject = mysql_result($result,$i,'t1.Notice_Title');
$description = mysql_result($result,$i,'t1.Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'t1.Notice_Link');
//to record when the feed was published
$pubdate = mysql_result($result,$i,'t2.publish_up');

//format the date
$format = 'D, d M Y H:i:s O'; 
$pubdate = date_format(date_create($pubdate), $format);

echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>'.$pubdate.'</pubDate>
</item>
';

} //end of the while loop

mysql_close(); //close the DB
echo '
</channel>
</rss>
';
?>

Any ideas?

Thanks.
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: RSS Feed only returns one result

Post by x_mutatis_mutandis_x »

Try Celauren's code. That's the right way to loop through resultset obtained from your query.
Your query seems to be correct, but if you are getting the same record multiple times, change query to add "distinct" and see if you are getting distinct records. If you get only 1 record, well then you have duplicate data in your table..

Code: Select all

$query = "SELECT DISTINCT Notice_Title, Notice_Text, Notice_Link, Notice_Link_Text, Publication_Date, Expiry_Date_Time FROM sgj_notices";
Post Reply