CODEWITHSUNDEEP
phpmysqldatetime
JonasT
JonasT

Reputation: 616

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 ($row = mysql_fetch_assoc ($result)) {
echo $row['time'];
}

MySQL table:

news:
time(text):
"27.03.2011"

Upvotes: 1

Views: 2135

Answers (5)

Michael Berkowski
Michael Berkowski

Reputation: 270609

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()

Upvotes: 0

Nemanja
Nemanja

Reputation: 1535

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

Upvotes: 1

RiaD
RiaD

Reputation: 47619

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

Upvotes: 0

John Cartwright
John Cartwright

Reputation: 5084

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` [...]

Upvotes: 1

mkk
mkk

Reputation: 7693

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

Upvotes: 1

Related Questions