CODEWITHSUNDEEP
phpsqlmysql
agente_secreto
agente_secreto

Reputation: 8079

How to get a mysql datetime field as a datetime in php?

I am new to PHP and MYSQL so this should be a pretty basic question.

I am querying a mysql database and getting three fields into an array. One of the fields type is datetime, but with my current query, it is being captured as a string.

This is my code:

$myquery = mysql_query ("SELECT id,text,when FROM messages ORDER BY cuando DESC");
 $nrows = mysql_num_rows ($myquery);
 for ($i = 0; $i < $nrows; $i++) {
 $row = mysql_fetch_assoc ($myquery);
 $when = $row["when"];

I've been googling and I think i have to use the AS operator in my query, but I dont' know how. How can I do this? If possible, just by changing the query...

Thanks!

Upvotes: 2

Views: 764

Answers (2)

jfoucher
jfoucher

Reputation: 2281

If you want a unix timestamp, you can either do it in your query or in php.

In your query: 

$myquery = mysql_query ("SELECT id,text,UNIX_TIMESTAMP(when) FROM messages ORDER BY when DESC");

in PHP:

$when = strtotime($row['when']);

Edit: WHEN being a reserved MySQL keyword, this query will give an error. Use something else for your date field.

Upvotes: 0

bensiu
bensiu

Reputation: 25604

  1. in PHP: $when = strtotime($row["when"]);
  2. in mySQL: SELECT id, text, UNIX_TIMESTAMP( when ) AS when FROM...

Upvotes: 3

Related Questions