Zend Framework - Using SQL directly

Question

I'm currently starting a project from scratch using the Zend Framework. I'm experienced in PHP and SQL, but I've never used a PHP framework before.

Regarding the database side of things - a lot of the tutorials use the Zend classes to wrap the SQL so that you don't have to write raw SQL. For example:

$select = $this->select()
    ->from('MyTable')
    ->order('MyField');

However, my preference is writing SQL as this is what I'm used to and feel that for both flexibility and speed to development, this is the best decision. (Note that I actually mean parameterised SQL using PDO, not completely raw SQL).

My questions are:

Are there any issues with doing it this way? Am I better in the long run going with the Zend way of doing database queries? Is it common for Zend developers to write the SQL themselves rather than using the Zend-style method queries?
What's the most common way of implementing this whilst keeping with an MVC code layout? A Google gave me this link (and similar), but that has the SQL in the controller (which I don't want). I still want to create my own application specific model classes, and adhere to the MVC split. I can think of plenty of places to instantiate the Zend_Db_Adapter_Pdo_Mysql class for using in my models - however I'd like to know what the most common design pattern is for this.

mingos · Accepted Answer · 2011-05-03 21:46:51Z

8

I believe your best bet would be to write this in your model:

public function myQuery ($table, $id) {
    return $this
        ->getDefaultAdapter()
        ->query("SELECT * FROM ? WHERE id = ?",
            array($table,$id)
        )
        ->fetchAll()
    ;
}

It's perfectly OK to do it this way. There shouldn't be any issues. In fact, you'll save yourself a lot of trouble when you hit a point when you need to use subqueries or other more complex stuff. You can do most of it with Zend's functions, but let's be honest: they tend to become unreadable as the queries get more complicated.

Whether you're better off with the Zend way or not, is up to you and how comfortable you feel with them. I prefer the functions Zend provides for most of the work, but it's just a matter of personal preference, really.

It's common to use both styles, AFAIK, although I don't know too many people using Zend (mostly from the company I work for), so don't quote me ;).

answered May 3, 2011 at 21:46

mingos

24.7k12 gold badges73 silver badges108 bronze badges

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

2 Comments

Tomáš Fejfar Over a year ago

zend_db_select stars when you need to build the query in parts and send it around. Ideal example is paginator. It gets sql query and calls limit(1,10). In text SQL you can do that too, but only because you can append it. Say you want to replace columns with count() or something like that. ->reset('columns')->columns('count()') :)

Marcin Over a year ago

+1. Also I could recommend having a look at this. It shows that sometimes, as far as the performance is concerned, it is better to use SQL directly.

PetrolMan · Accepted Answer · 2011-05-03 21:26:09Z

1

Try the following link: http://framework.zend.com/manual/en/zend.db.statement.html

answered May 3, 2011 at 21:26

PetrolMan

3813 silver badges9 bronze badges

Collectives™ on Stack Overflow

Zend Framework - Using SQL directly

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related