SQL Query [RESOLVED]

[DJel]

Hello,

I am trying to construct a SQL query for a MySQL database that selects certain fields but at the same time converts a datetime field into something more user friendly.

So far I have this query that selects and converts the date;

SELECT DATE_format(time_sent, '%M %e, %Y<br>%l:%i%p') as newdate FROM messages

Which outputs a date in a form like so; January 9, 2008 2:27PM (with a line break between the date and time)

And this query which selects all the other information I want;

SELECT from_username, subject, read, FROM messages WHERE to_username='$session->username'

I have tried to incorporate the formatting query (DATE_format(time_sent, '%M %e, %Y<br>%l:%i%p') as newdate) straight into the normal query like so;

SELECT DATE_format(time_sent, '%M %e, %Y<br>%l:%i%p') as newdate, from_username, subject, read, FROM messages WHERE to_username='$session->username']

as well as assigning it as a variable then inputting it. When I do this nothing is output. I am using PHP as my scripting language and the error message I receive is;

SQL query:

SELECT DATE_format( time_sent, '%M %e, %Y<br>%l:%i%p' ) AS formatted_date, from_username, subject,
READ ,
FROM messages
WHERE to_username = 'username'
LIMIT 0 , 30

MySQL said:
#1064 - 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 'read , FROM messages WHERE to_username = 'username'
LIMIT 0, 30' at line 1

Any help would be greatly appreciated,

DJel.

[Elyzion]

Why don't you just select it then format it in PHP.. It would be much easier.

[PhuckNut]

get rid of the , before the FROM

[DJel]

Originally Posted by Elyzion

Why don't you just select it then format it in PHP.. It would be much easier.

By just incorporating it into the query I can avoid pointless code, at the moment there is a fair bit of code so if I can find ways to reduce the amount of code the better.

Originally Posted by PhuckNut

get rid of the , before the FROM

Thanks for that, the comma was one problem and there was also another problem with some quote marks further on.

Thanks for the help guys,

DJel.

[houseofzeus]

Originally Posted by DJel

By just incorporating it into the query I can avoid pointless code, at the moment there is a fair bit of code so if I can find ways to reduce the amount of code the better.

Maybe, I'd argue it makes it less portable if you ever decide to switch dbms down the track.