MySQL date to day(th) month year

[Flames]

how do i get a date from a normal date column in a mysql table e.g. yyyy-mm-dd, to a format like 1st Janurary 2008. i looked around on t'interweb and found nothing simple instead i found massive functions which im sure arent necessary.

 

[Bendude14]

www.php.net/date

[Flames]

Well how do i get the MySQL date to be converted into the php format. i know i can do echo date("jS M, y") to get the current date in 1st Jan 08 but that wont help because i need to get the values for j,S, M and y from the database.

[Bendude14]

use the second parameter

 

date("jS M, y", $mysqldate)

[AndyB]

i need to get the values for j,S, M and y from the database.

 

I'm not too sure what you're asking for, but maybe this will help:

 

list($y,$m,$d) = explode("-",$date);

 

It returns the year, month, and day values from a yyyy-mm-dd date

[PFMaBiSmAd]

how do i get a date from a normal date column in a mysql table e.g. yyyy-mm-dd, to a format like 1st Janurary 2008.

Simple, just use the mysql DATE_FORMAT() function in your SELECT query, that is what it is for - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[Flames]

this is the php from my code:

<?php
$sql = "SELECT * FROM News ORDER BY id DESC LIMIT 1";
$query = mysql_query($sql);
while($news = mysql_fetch_assoc($query))
{
$message = $news['message'];
$poster = $news['poster'];
$date = $news['date'];
$date2 = date("jS M, y", $date);
}
echo <<<EOT
$message, posted by <span class="poster">$poster</span>, on the <span class="style7">$date2</span>
EOT;
?>

it always returns 31st december 69, even though the date is 2008-10-19

 

@AndyB i dont get how i would put that in to the date function, otherwise that would just say 19 10 2008 which isnt how i want it.

 

@PFMaBiSmAd i cant do that because im selecting other columns as well.

[PFMaBiSmAd]

Select the other columns and do that too.

 

SELECT *, DATE_FORMAT(....) AS formatted_date ....

[Flames]

so in my code would it just be

<?php
$sql = "SELECT *, DATE_FORMAT(date) AS %d %b, %y FROM News ORDER BY id LIMIT 1";
$query = mysql_query($sql);
while($news = mysql_fetch_assoc($query))
{
$message = $news['message'];
$poster = $news['poster'];
$date = $news['date'];
}
echo <<<EOT
$message, posted by <span class="poster">$poster</span>, on the <span class="style7">$date</span>
EOT;
?>

[PFMaBiSmAd]

You might want to read the information and the examples at that link. The syntax is - DATE_FORMAT(date,format). Date is your date column name and format is the format string that gives you the results you want.

 

The AS some_name syntax assigns an alias name so that you can refer to the resultant value by that name, which you probably already knew from your basic mysql reference book.

[PFMaBiSmAd]

Since mind reading seems to be working today over the Internet, I'll answer your next question before you even ask it in this thread -

 

All strings must be enclosed in quotes (single-quotes are used in mysql). All six examples at that link show the format string enclosed in single-quotes. How you managed to read that information and leave out that part of the syntax is a mystery.

[Flames]

i managed to get my code working thanks.

 

I didnt notice that, i tried my code without and i thought it was a MySQL error hence the reason i made a new topic, do you know anywhere where i could find help with the AS some_name because i've never used it before but i managed to get it working by a complete guess of $news['some_name'].

[Flames]

A new problem, what do i do if its a datetime field, i'm not fussed about the time only the date, although i cant get rid of the time on it because its a WP blog that im playing around with.

[Flames]

bump and ive tried understand how WP does it but i dont understand there code.