RSS SQ(hel)L problems again...

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
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

RSS SQ(hel)L problems again...

Post 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.
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post 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?
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

Post by Milan »

nope it was edited.
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post 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 :- /
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

Post by Milan »

No problem, thanks for the guidelines!
it should be enough for me to troubleshoot it.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

Post by Milan »

thanks! I will modify the code by your guidelines.
Post Reply