date_format

how can I change the date format to DD MM YYYY?

$query = "SELECT * FROM  news_home ORDER BY timestamp DESC";

$results = mysql_query($query);

$returnS="";

while($line = mysql_fetch_array($results))

Maybe this article may help you?

Thanks. But can someone give me an quick answer... I'm in a desperate hurry!!

I'm trying to pull some data from a mysql and I'm successful in it. But I need the date format changed to DD MM YYYY.

You should probably use MySQL's DATE_FORMAT() function, which will convert a date/time from whatever format it is stored in to whatever format you need it in:

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

SELECT newsname, DATE_FORMAT(newstime,'%d/%m/%Y') FROM news_home

The second argument to DATE_FORMAT() can be changed around to suit your needs. Refer to the formatting table in the MySQL manual for all the options. My example would produce DD/MM/YYYY.

Doesn't seem to work though.

$query = "SELECT * FROM news_homez DATE_FORMAT(timestamp,'%d/%m/%Y') ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$line[timestamp].",,".$line["Newsz"].",,".$line["linksz"].",,,";
}

Is your timestamp field called "timestamp" or "timestampz", and is your table called "news_home" or "news_homez"? Or were you just joking around?

In that example also you would need to do this:

$query = "SELECT subject, DATE_FORMAT(timestamp,'%d/%m/%Y') AS timestamp, News, links FROM news_homez ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$line["timestamp"].",,".$line["Newsz"].",,".$line["linksz"].",,,";
}

Notice the AS timestamp in the SQL and the quotes around $line["timestamp"] in your while loop. Your field names were in the wrong place, they should come before the FROM. It's also generally better form to name the fields you are pulling out rather than using *. Also, double quotes invoke the PHP interpreter's interpolation scan for variables, so if you're not parsing any variables inside the string, it's more correct to do:

$arrayname['arraykey']

than

$arrayname["arraykey"]

Because it slows the script execution down trivially, but some. It's also (arguably) more legible.

Thanks jayarsee!

But I'm getting the following error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /php/test.php on line 9

I wanted to add that the reason the DATE_FORMAT comes before FROM is because it's essentially treated as a column name in your result set, just like the others. Since you would also not do:

SELECT *,timestamp FROM table

Because you would end up with 2 timestamp fields, when using a function as one of the result fields you should list out the names of the fields you want. SELECT wildcards (*) are also generally troublesome, more on my blog about that here: http://jrciiphp.blogspot.com/2010/08/in-offense-of-sql-wildcards.html

Additionally, when using a MySQL function as a "field", the automatic name of the field that comes back contains the function name. Thus, to access it easily inside your while loop (like $line['timestamp']) you use the AS keyword to assign an "alias" to it.

Thanks jayarsee!

 

But I'm getting the following error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /php/test.php on line 9

It's difficult to diagnose that because I can't see what your table looks like. Double check to make sure all the field names, and table name, matches what it says in your database.

Maybe if you actually showed us the error?

I'm sort of sucked with this. All that I need is to get the date which is in my mysql in this format %d/%m/%Y'.

I'm not getting any data when I use code A. But works perfectly when I use code B (without that date format though). I wonder if there is any error in the syntax?

code A

$query = "SELECT DATE_FORMAT(timestampz, '%d/%m/%Y') FROM news_homez ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$line["timestampz"].",,".$line["Newsz"].",,".$line["linksz"].",,,";
}
echo $returnS;
mysql_close($link);
?>

code B

$query = "SELECT * FROM  news_homez ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$line["timestampz"].",,".$line["Newsz"].",,".$line["linksz"].",,,";
}
echo $returnS;
mysql_close($link);
?>

Yes there is.

$query = "SELECT DATE_FORMAT(timestampz, '%d/%m/%Y') AS timez FROM news_homez ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$line["timez"].",,".$line["Newsz"].",,".$line["linksz"].",,,";
}
echo $returnS;
mysql_close($link);
?>

. it looks all wired now... Some fields show the message 'undefined'

I don't know if this is important, but in the MYSQL db I'm using the CURRENT_TIMESTAMP to add the date atuomatically in to the timestampz field. Could this have an impact to the code provided because it has got the date & time?

Thanks Guys!

Sorry. Must have had a brainfart while writing that out. I left out the rest of the fields from the query string. Having the date and time should have no effect on DATE_FORMAT() since you specify in the function which values are displayed, and how they are displayed. The field is a DATETIME field, and the values are in there as YYYY-MM-DD HH:MM:SS, right?

Can you post any errors, and whatever part of it looks weird?

$query = "SELECT subjectz, DATE_FORMAT(timestampz, '%d/%m/%Y') AS timez, Newsz, linksz FROM news_homez ORDER BY timestampz DESC";

...The field is a DATETIME field, and the values are in there as YYYY-MM-DD HH:MM:SS, right?

 

Can you post any errors, and whatever part of it looks weird?

 

$query = "SELECT subjectz, DATE_FORMAT(timestampz, '%d/%m/%Y') AS timez, Newsz, linksz FROM news_homez ORDER BY timestampz DESC";

Hi Pickachu...

yes it is YYYY-MM-DD HH:MM:SS.

I'm getting ONLY THE DATE and NO other data. It seems only 2 lines are the idfference between the working code and the not working code. That's weird!

Works alright (without the required date format from the date field).

$date =date('d-m-Y');
$query = "SELECT * FROM  news_homez ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$date.",,".$line["Newsz"].",,".$line["linksz"].",,,";
}
echo $returnS;
mysql_close($link);
?>

Pulls ONLY the date.

$query = "SELECT DATE_FORMAT(timestampz, '%d/%m/%Y') AS timez FROM news_homez ORDER BY timestampz DESC";
$results = mysql_query($query);

$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line["subjectz"].",,".$line["timez"].",,".$line["Newsz"].",,".$line["linksz"].",,,";
}
echo $returnS;
mysql_close($link);
?>

// without TIME in order by

$query = "SELECT DATE_FORMAT(timestampz, '%d/%m/%Y') timez, Newz, linkz, subjectz FROM news_homez ORDER BY timez DESC";

// with TIME in order by

$query = "SELECT DATE_FORMAT(timestampz, '%d/%m/%Y') timez, Newz, linkz, subjectz FROM news_homez ORDER BY timestampz DESC";

ORDER BY depends on whether you want to order by the formated date or by the column timestampz which also includes time.