Page 1 of 1

RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:16 am
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>
';
?>


Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:22 am
by Celauran
That's because you're using mysql_result and no loop. You need to iterate over each row in the result set.

Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:25 am
by ourmaninparis
OK. With a For Each I suppose? Sorry for the lack of knowledge!

Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:28 am
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


Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:29 am
by Celauran

Code: Select all

while ($row = mysql_fetch_assoc($result))
{
    // do stuff here
}

Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:41 am
by ourmaninparis
Thanks but that seems to create a never ending loop.

Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:44 am
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>
';
?>

Re: RSS Feed only returns one result

Posted: Tue Apr 24, 2012 8:55 am
by ourmaninparis
Thanks for the help but that doesn't work either as it returns the same result many times.

Re: RSS Feed only returns one result

Posted: Fri May 04, 2012 10:25 pm
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.

Re: RSS Feed only returns one result

Posted: Mon May 07, 2012 8:29 am
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";