UNIX_TIMESTAMP usage query

Question

Hi, in my php5 code i want to use the UNIX_TIMESTAMP function to convert a datetime to a unix timestamp without using php code to convert as i believe this way is quicker but how do you then get the unix timestamp out of the rowset ie how to index into the rowset to get the integer unix timestamp out

Below is a summary of the database schema

age   int(5) 
user  varchar(256)    
created  timestamp   CURRENT_TIMESTAMP 

$query="SELECT user, UNIX_TIMESTAMP(created) FROM  accounts WHERE age='$age'" ;
$result = mysql_query($query)  or die ('Error querying domain');

while($row = mysql_fetch_array($result))
{
 $callerid = $row['callerid'];
 $created_ts = $row['created'];
 etc....

Answer 1

By default (MYSQL_BOTH), mysql_fetch_array gives you both numeric and associative indices. So you can do:

$created_ts = $row[1]; 

Or, give it a name:

$query="SELECT user, UNIX_TIMESTAMP(created) AS created_ts FROM  accounts WHERE age='$age';";
// ...
$created_ts = $row['created_ts'];

Also note that if age comes from user input you should use mysql_real_escape_string before concatenating it. Or switch to a framework (like PDO or MySQLi) that supports prepared statements.

Answer 2

Use alias SELECT user, UNIX_TIMESTAMP(created) AS created FROM accounts

Answer 3

Or use AS clause to give your column more descriptive name like this:

UNIX_TIMESTAMP(created) AS ucreated

And you can get it like:

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

  $created_ts = $row['ucreated'];
............

More Info:

• http://dev.mysql.com/doc/refman/5.0/en/select.html