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";