CODEWITHSUNDEEP
mysqldatabasezend-framework
russellr
russellr

Reputation: 58

zend framework automatically alter queries

My database (mysql) tables use TIMESTAMP columns, and whenever I want them returned in a query, I want them to be queried as "UNIX_TIMESTAMP(columnname)".

How do you easily modify queries in zend framework to achieve this?

For example, the current code is:

select = $this->select();
$select->where('user_id = ?',$user_id);
return $this->fetchAll($select);

This eventually becomes:

select * from tablename where user_id = 42;

I want something that automatically finds the TIMESTAMP column and changes the resulting query to:

select user_id,name,unix_timestamp(created) where user_id = 42;

I know I can use a MySQL view to achieve this, but I'd rather avoid that.

Thanks.

RR

Upvotes: 0

Views: 194

Answers (1)

Jamie Sutherland
Jamie Sutherland

Reputation: 2790

You should be able to specify the fields you want in the select using the $select->from() object.

Zend_Db_Select

You should end up with something like this.

$select = $this->select();

$select->from(
    array('t' => 'tablename'),
    array('user_id', 'name', 'UNIX_TIMESTAMP(created)')
);

$select->where('user_id = ?',$user_id);
return $this->fetchAll($select);

If you wanted to run an expression that doesn't have parenthese in the function, Use the Zend_Db_Expr() method to escape the query properly.

Upvotes: 2

Related Questions