zend framework automatically alter queries

Question

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

Jamie Sutherland · Accepted Answer · 2012-04-04 21:27:49Z

2

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.

answered Apr 4, 2012 at 21:27

Jamie Sutherland

2,79019 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

russellr Over a year ago

Thanks for your response. Yes, that's obvious. What I'm looking for is a way to do this without specifying all the columns. I want a generic solution that doesn't require me to maintain the list of columns in the PHP code for every table that has a timestamp.

Jamie Sutherland Over a year ago

Ahhh. Sorry I misunderstood. I'd recommend looking at something like Doctrine2 then. That will map your PHP classes to MySQL using annotations. It will generate the correct queries depending on how you have annotated the field. Doctrine2 ORM supports mapping between the TIMESTAMP MySQL type and PHPs DateTime automatically. It's probably overkill for want you are wanting, but it does make life easier once you get the hang of it. docs.doctrine-project.org/projects/doctrine-orm/en/latest/…

Collectives™ on Stack Overflow

zend framework automatically alter queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related