Page 1 of 1

RSS SQ(hel)L problems again...

Posted: Mon Jun 05, 2006 3:02 pm
by Milan
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:7. Do not divulge anyone's personal information in the forum - including your own. This includes e-mail addresses, IP addresses, age, house address, and any other individual information.
I's me again , did you missed me folks? :roll:

I have this code to generate the RSS for the last 10 posts

Code: Select all

<?php
$xml = "<?xml version="1.0" encoding="UTF-8"?>";
$xml .= <<<doc
<rss version="2.0">\n
	<title> Title is here  </title>\n
	<link> link is here  </link>\n
	<description>description is here</description>\n
doc;
	
$link = mysql_connect('mysql.host.address','username','password') or die('Database connection Error!!');
mysql_select_db('databasename');
$query = "Select projectname,
				 ownerid,
				 pricemax,
				 dateposted,
				 timeposted,
				 timestamp(dateposted,timeposted) as 'when' 
			from projects
			order by 'when' DESC LIMIT 10;";

$result_set = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result_set)) {
$xml .= <<<_XML_
	<Item>\n
		<Title>$row[projectname]</Title>\n
		<description>Description</description>\n
		<link>test linb</link>\n		
		<username>$row[ownerid]</username>\n
		<max_price>$row[pricemax]</max_price>\n 
		<Date_posted>$row[dateposted]</Date_posted>\n 
		<Time_posted>$row[timeposted]</Time_posted>\n 
	</Item>\n
_XML_;
}

mysql_free_result($result_set);
mysql_close($link);

$xml .= "</rss>";
$rss_file = simplexml_load_string($xml);

// change the path to $_SERVER['DOCUMENT_ROOT']
$rss_file->asXML('/home/content/p/i/c/pickageek/html/'); 
?>
but once i call it i get this error :

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(dateposted,timeposted) as 'when' from projects order


What can be wrong?

EDIT: Onion - Replaced MySQL server details with dummy data.

Posted: Mon Jun 05, 2006 3:53 pm
by daedalus__
Before I say anything else, I am going to say:

Did you really just post the host, name, and pass to your SQL server?

Posted: Mon Jun 05, 2006 4:42 pm
by Milan
nope it was edited.

Posted: Mon Jun 05, 2006 5:34 pm
by daedalus__
I haven't check thoroughly but off the top of my head, I would say it's the way you are using timestamp().

If I was you, I would have the database store a single MySQL timestamp and then unix_timestamp() it out using PHP to play with it, but that's just me.

I'll look more up about it later, I'm a little busy now and it seems like no one else cares :- /

Posted: Mon Jun 05, 2006 5:37 pm
by Milan
No problem, thanks for the guidelines!
it should be enough for me to troubleshoot it.

Posted: Mon Jun 05, 2006 9:19 pm
by John Cartwright

Code: Select all

SELECT `projectname`, `ownerid`, `pricemax`, `dateposted`, `timeposted`, timestamp(dateposted, timeposted) as `when`
FROM `projects`
ORDER BY `when` DESC 
LIMIT 10
#1, don't use single quotes around 'when', instead use backticks for column names
#2, what exactly are you trying to do with timestamp(dateposted, timeposted)? I don't follow you
#3, the proper syntax for timestamp() in sql is UNIX_TIMESTAMP()
#4, UNIX_TIMESTAMP expects the column to be a datetime field, from the looks of it you store the time and date in seperate fields therefore UNIX_TIMESTAMP won't work

Posted: Tue Jun 06, 2006 12:28 pm
by Milan
thanks! I will modify the code by your guidelines.