Page 1 of 3

can anyone help?

Posted: Mon Jan 17, 2005 7:01 am
by mohson
їcode]

$query = "SELECT * FROM people ORDER BY firstname";

ї/code]

Guys,

this query displays people details including two date coloums one named dateoflastcontact and the other called datecontactagain I want these two date coloums to appear in uk date format i.e dd/mm/yy. I have tried incorporating the DATE_format function from the php manual but it has no effect can anyone help

Posted: Mon Jan 17, 2005 7:55 am
by feyd
the DATE_FORMAT function is a MySQL function, not a php one.

Code: Select all

SELECT DATE_FORMAT('%d/%m/%y', `your_date_name`) AS `your_date_field_name` FROM `table`

Posted: Mon Jan 17, 2005 8:46 am
by mohson
thanks, but that willl only pull up the date what abt the other fields I want to retrieve??

Posted: Mon Jan 17, 2005 8:47 am
by feyd
you can add them like so:

Code: Select all

SELECT DATE_FORMAT(...) ..., * FROM `table`

Posted: Mon Jan 17, 2005 9:08 am
by mohson
thanks,

date_name and date_field_name whats the difference my date field name is dateoflastcontact

Posted: Mon Jan 17, 2005 9:15 am
by feyd
both should be `dateoflastcontact` then.. sorry ;)

Posted: Mon Jan 17, 2005 9:46 am
by mohson

Code: Select all

$query = "SELECT DATE_FORMAT(%d/%m/%y, dateoflastcontact, datecontactagain) AS dateoflastcontact, datecontactagain, * FROM people ORDER BY firstname";
Ive tried this so that the two fields dateoflastcontact and datecontactagain appear in uk date format but im getting a sql syntax error can you spot anything that im missing

Posted: Mon Jan 17, 2005 9:54 am
by feyd
they require seperate calls to the function.. i.e.

Code: Select all

SELECT DATE_FORMAT(...) ..., DATE_FORMAT(...) ..., * FROM `table`

Posted: Mon Jan 17, 2005 9:55 am
by mohson
Sorry can you explain on my code?

Posted: Mon Jan 17, 2005 10:17 am
by timvw
rtfm :p DATE_FORMAT(date,format)


$query = "SELECT DATE_FORMAT('%d/%m/%y, dateoflastcontact) AS dateoflastcontact, DATE_FORMAT('%d/%m/%y, datecontactagain) AS datecontactagain * FROM people ORDER BY firstname";

Posted: Mon Jan 17, 2005 10:45 am
by mohson
put it in exactly as you said it but it still doesnt work? any ideas??

Code: Select all

$query = "SELECT DATE_FORMAT('%d/%m/%y, dateoflastcontact) AS dateoflastcontact, DATE_FORMAT('%d/%m/%y, datecontactagain) AS datecontactagain * FROM people ORDER BY firstname";

Posted: Mon Jan 17, 2005 10:50 am
by feyd
missing a comma after the last datecontactagain.

Posted: Mon Jan 17, 2005 11:48 am
by timvw
typo of death ;)




$query = "SELECT DATE_FORMAT('%d/%m/%y', dateoflastcontact) AS dateoflastcontact, DATE_FORMAT('%d/%m/%y', datecontactagain) AS datecontactagain, * FROM people ORDER BY firstname";


feyd | forgot the comma! ;)

Posted: Tue Jan 18, 2005 4:26 am
by mohson
sorry but put in exactly as you said and I get this error message:

Error in query: You have an error in your SQL syntax near '* FROM people ORDER BY firstname' at line 1

Code: Select all

$query = "SELECT DATE_FORMAT('%d/%m/%y', dateoflastcontact) AS dateoflastcontact, DATE_FORMAT('%d/%m/%y', datecontactagain) AS datecontactagain, * FROM people ORDER BY firstname";
any ideas??

Posted: Tue Jan 18, 2005 5:39 am
by snicolas

Code: Select all

$query = "SELECT DATE_FORMAT('%d/%m/%y', 'dateoflastcontact') AS dateoflastcontact, DATE_FORMAT('%d/%m/%y', 'datecontactagain') AS datecontactagain, * FROM people ORDER BY firstname";