SELECTing using DATE_FORMAT

[Rick]

I have been trying to run this MySQL query using PHP:

SELECT id, trans_type, remote_addr, DATE_FORMAT(tim e, '%M %e, %Y %r') as time
FROM transactions WHERE client_id = " . $client_id . " ORDER BY time asc

and I am getting this error message:

MySQL Error number: 1064: You have an error in your SQL syntax near 'ORDER BY time asc' at line 2

I added the "as time" in hopes it wasn't seeing the 'DATE_FORMAT()' field as the time field.

Does anyone have any advice?

Thanks in advance,

Rick Barnes

[Rick]

Re: SELECTing using DATE_FORMAT

Rick wrote:
[color=blue]
> I have been trying to run this MySQL query using PHP:
>
> SELECT id, trans_type, remote_addr, DATE_FORMAT(tim e, '%M %e, %Y %r')
> as time
> FROM transactions WHERE client_id = " . $client_id . " ORDER BY time asc
>
> and I am getting this error message:
>
> MySQL Error number: 1064: You have an error in your SQL syntax near
> 'ORDER BY time asc' at line 2
>
> I added the "as time" in hopes it wasn't seeing the 'DATE_FORMAT()' field
> as the time field.
>
> Does anyone have any advice?
>
> Thanks in advance,
>
> Rick Barnes[/color]

Sorry I just noticed this only happens when there are no records...this error occurs before I can
check the value of the returned query handle...how can this be avoided without running 2 queries?
One to check for any records and the other to run if there are any? I currently use:

"$result = mysql_query($qu ery)or die("MySQL Error number: " . mysql_errno() . ": " . mysql_error().
"\n");

Should I leave this out so I can test the returned handle's value ($result)?

TIA,

RB

[Richard Allsebrook]

Re: SELECTing using DATE_FORMAT

time is an SQL reserved word

try:

SELECT id, trans_type, remote_addr, DATE_FORMAT(tim e, '%M %e, %Y %r')
as my_time
FROM transactions WHERE client_id = " . $client_id . " ORDER BY my_time asc

Hope this helps
Richard

Rick wrote:[color=blue]
> Rick wrote:
>[color=green]
>> I have been trying to run this MySQL query using PHP:
>>
>> SELECT id, trans_type, remote_addr, DATE_FORMAT(tim e, '%M %e, %Y
>> %r') as time
>> FROM transactions WHERE client_id = " . $client_id . " ORDER BY time asc
>>
>> and I am getting this error message:
>>
>> MySQL Error number: 1064: You have an error in your SQL syntax near
>> 'ORDER BY time asc' at line 2
>>
>> I added the "as time" in hopes it wasn't seeing the
>> 'DATE_FORMAT()' field as the time field.
>>
>> Does anyone have any advice?
>>
>> Thanks in advance,
>>
>> Rick Barnes[/color]
>
>
> Sorry I just noticed this only happens when there are no records...this
> error occurs before I can check the value of the returned query
> handle...how can this be avoided without running 2 queries? One to check
> for any records and the other to run if there are any? I currently use:
>
> "$result = mysql_query($qu ery)or die("MySQL Error number: " .
> mysql_errno() . ": " . mysql_error(). "\n");
>
> Should I leave this out so I can test the returned handle's value
> ($result)?
>
> TIA,
>
> RB[/color]