How to insert and read time/date in MySQL/PHP?

I want to read the single day, month and year, without adding 3 extra MySQL-rows, in this format (with PHP):

day: 01
month: Jan, Feb, Mar..(first three letters)
year: 2011

This is table and PHP script, which I use now:

I add the date with PHP:

mysql_query("INSERT INTO news (...,`time`) VALUES (...'".date(d.".".m.".".Y)."');"); 

I read it out with:

$query = "SELECT * FROM news";
$result = mysql_query ($query);

while ($ro开发者_JAVA技巧w = mysql_fetch_assoc ($result)) {
echo $row['time'];
}

MySQL table:

news:
time(text):
"27.03.2011"

Query should be:

mysql_query("INSERT INTO news (...,`time`) VALUES (...'".date(d.".".M.".".Y)."');");

M instead of m gives you the 3 letter textual representation of the month.

Get it with:

while ($row = mysql_fetch_assoc ($result)) {
    echo date( 'd', strtotime( str$row['time'] ) );
    echo date( 'M', strtotime( str$row['time'] ) );
    echo date( 'Y', strtotime( str$row['time'] ) );
}

Read more on:

http://php.net/manual/en/function.date.php

you can either do it in PHP, check the strftime function or use in SELECT something like

   SELECT DAY(date) as day, MONTH(date) as month, YEAR(date) as year FROM table

and in php you would acccess it as $result['day']. $result['month'] etc. the "date" in SELECT query is of course the name of the column in which you store your date. I would recommend strftime

You can use MONTH(), DAY(), YEAR() Mysql functions., i.e,

SELECT MONTH(`time`) AS `month`, DAY(`time`) AS `day`, YEAR(`time`) AS `year` FROM `news` [...]

SELECT *, substring(1,2) as day, substring(4,2) as month, substring(7) as year FROM table

Also you can(and should) use date table format and use DAY(), MONTH(), YEAR() functions

You should be using a DATETIME column in your mysql table. MySQL is then responsible for storing the date in its own internal format, and you can retrieve it in any format you need.

To insert the current date, you can simply

INSERT INTO news (...,`time`) VALUES (...,NOW())

To retrieve it in the format you want, use

SELECT DATE_FORMAT(`time`, '%d.%b.%Y');

Documentation on MySQL DATE_FORMAT()