MySQL and RSS help - Esato archive

Posted by Jim
Hi all,

I need some help from experienced webmasters on mysql, I try to create a rss feed based on a mysql table but I suck in mysql. Here is the code

Code:


header("Content-Type: text/xml;charset=utf-8");



include('conf/db.php');



if (!$db)

{

   error_log("Error: Could not connect to database in rss.php.");

   exit;

}



mysql_select_db("mydatabase");



$query1 = ("select * from annuaire_sites where valid='1' limit 10");

$result1 = mysql_query($query1);



echo <<<END

<?xml version="1.0" encoding="utf-8"?>

<rss version="2.0">

   <channel>

      <title>My title</title>

      <link>My link</link>

      <description>My description</description>

      <language>en-us</language>

	  <docs>My doc</docs>

      <generator>My generator</generator>

END;



   $row = mysql_fetch_array($result1);

   $title = $row["nomsite"];

   $link = $row["url"];

   $description = $row["description"];



echo <<<END



      <item>

         <title>$title</title>

		 <description>$description</description>

         <link>$link</link>

      </item>

END;





echo <<<END



   </channel>

</rss>

END;



?>

Nothing shows up, only <html><body/></html> which makes no sence at all. I guess that my sql coding is bad and that it can't get the results.

Can anybody please help me ? I don't wanna ask to those php/mysql guru sites cause they will have explanation that I don't understand.

Many thanks
_________________
K700 - T610 themes site

[ This Message was edited by: Jim on 2005-03-31 23:27 ]

Posted by Krubach
First, try the SQL command directly in MySQL.
See what's the result recordset.
If it's empty, there's a problem in the query.
If not, there's a problem binding the recordset columns to the variables.

Posted by Cycovision
Usually when you assign a database query to a variable in PHP, you don't put the MySQL query in brackets, so:

$query1 = ("select * from annuaire_sites where valid='1' limit 10");

Should be:

$query1 = "SELECT * FROM annuaire_sites WHERE valid='1' limit 10";

Note the capitalisation, it's not strictly necessary but it is the standard way of writing MySQL queries so it's probably best to user it.

Not sure if that'll solve all of the problem but it'll hopefully get the script a bit further

Posted by Jim
Hi and many thanks, the query is correct as the sql gives me results but it seems that I can't call the values from my php file, I'm lost.

Btw I searched on the mysql manual but I can't find how to return the last 10 results and not the first 10

Posted by Krubach
Sort them the other way around and get the first ten...

Posted by Jim
Good idea

Posted by Krubach
Have you tried what Cyco proposed?

[EDIT]

I'm not much into PHP programming, but in all other languages it goes like this:
- You fetch the recordset
- You position in the 1st row
- You cycle through all rows until you get to the end.

I can't see point's 1 & 2 in your code.

(but maybe i'm wrong)

_________________

Stop looking at me. I don't like people looking at me like that.

[ This Message was edited by: Krubach on 2005-04-01 12:05 ]

Posted by Jim
Yeah it's the same, for point 1 and 2 it's the $query and $row no ?

Posted by Jim
Well I found it out, was really simple actually but didn't pay attention where I should

The rss feed works with my mysql database.

Thanks again guys

Posted by Krubach
So, what was it?

Posted by Jim
Well I chaned everything, here is the code if someone is interested:

Code:

<?php

header("Content-Type: text/xml; charset=iso-8859-1");



if (!$link = mysql_connect("localhost", "username", "password"))

    exit("Unable to make a connection to the database");

if (!mysql_select_db("my_database", $link))

    exit("Unable to select the database");

// Define a SQL Select Query

$query = "select nomsite, url, description, date from annuaire_sites where valid='1' order by date and id limit 10";

if (!$result = mysql_query($query, $link))

    exit("Illegal query");

// Start the xml output

echo "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>

<rss version=\"2.0\">

   <channel>

      <title>My title</title>

      <link>My URL</link>

      <description>My description</description>

      <language>en-us</language>

      <generator>My generator</generator>"; 

// Display the results of the query

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{ 

	$title = $row["nomsite"];

	$link = $row["url"];

	$description = $row["description"];

   

    echo "<item>";

    echo "<title>$title</title>";

	echo "<link>$link</link>";

	echo "<description>$description</description>";

    echo "</item>\n";

}

echo "</channel></rss>";

?>