Zend Database Handling

Question

I am new to Zend but am quite familiar with PHP and other MVC frameworks. A client requires that Zend be used for an application and I am having a little bit of trouble finding a database approach that I like. I am only connecting to one database (MySQL for now, MS SQL in production) and really only interacting with one table. With that said I would like to find a good approach for opening a handle to that database and keeping it open so that it is available in all of my controllers and allows me to use such methods as fetchAll, fetchOne, etc.

Ideally I would like to be able to make calls like the one below from my controllers and pass the results to the views.

$this->db->fetchAll("SELECT * FROM TABLE"); 

I have followed the tutorials on the Zend site but after using the Zend_Db_Table approach I wasn't able to find the flexibility that I wanted and thus am hoping someone here can point me in a different direction. I seem to be having a tough time wrapping my head around how Zend database models interact w/ Zend controllers but I am sure that there are lots of SO users who can provide some solid input.

Thanks in advance!

Answer 1

When you put this config to your application.ini Zend will estabilish connection to database when you will try to query it (some of them are optional):

resources.db.adapter = "pdo_mysql"
resources.db.params.host = "[host_adress]"
resources.db.params.username = "[db_username]"
resources.db.params.password = "[db_password]"
resources.db.params.dbname = "[db_name]"
resources.db.isDefaultTableAdapter = true
resources.db.params.charset = "utf8"
resources.db.params.driver_options.1002 = "SET NAMES utf-8"

To query database it is best to use Zend Models, for example define models like:

class Application_Model_Book extends Zend_Db_Table_Abstract{
    public function getBookById($id){
        return $this->find($id);
    }
}

And then use it in your controller like:

$mBook = new Application_Model_Book();
$myBook = $mBook->getBookById(5);

Ofcourse this is a trivial example and easily can be made in controller. But for more complidated queries it will heavily organize and clean your code. Read http://framework.zend.com/manual/1.11/en/zend.db.select.html and use knowledge in models.

Answer 2

$this->db->fetchAll("SELECT * FROM TABLE"); is actually fairly easy to accomplish.

I'm going to assume that you have already set up a database adapter in your application.ini or your bootstrap.php it really doesn't matter which, as long as you have defined an adapter somewhere.

//simplified for demonstration
class SomeController extends Zend_Controller_Action

    protected $db;

    //items that are put into init() or predispatch() or similar function 
    //can usually be put into a frontcontroller plugin if needed app wide.
    public function init() {
        //this works as long as you have only one adapter defined or have set one as default.
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }
    public function indexAction() {
        //with your db adapter defined you have access to the api for Zend_Db_Table_Abstract
        $result = $this->db->fetchRow($sql);
        //while a simple string will work as $sql I would
        //recommend the select() be used if for not other reason then that
        //the queries are built programatically
        $select = $this->db->select();
        $select->where('id = ?', $id)->orWhere('role = ?', 1); //selects can be chained or separated
        $select->order('id','ASC');

        $rows = $this->db->fetchAll($select);//returns rowset object, toArray() if needed.

    }
}

I think the biggest problem most people have when first using Zend_Db is figuring out which is the best way to use it. There are many ways to use this component and most of us find one we like and use it for everything.

For example a simple sql query can be represented as a string $sql = "SELECT * FROM TABLE";
as a select() object as in my example code.
You can use Zend_Db_Expr to represent more complex expressions $select->where(new Zend_Db_Expr("FIND_IN_SET('$genre', genre)"));
and don't forget Zend_Db_Statement (which I have never used, so can't demo correctly).

Quoting of values and identifiers is available if needed.
[EDIT]

As it has been previously stated you must define a database adpater. I prefer to do this in my application.ini, however the Bootstrap.php is also a common place to define an adapter.

In you application.ini make sure you have at least these lines:

resources.db.adapter = "pdo_Mysql" //or your chosen adapter
resources.db.params.username = "username"
resources.db.params.password = "password"
resources.db.params.dbname = "dbname"

I hope this at least gives you direction to look in. Good Luck!

Answer 3

You should keep in mind that Zend models are utterly ignorant of your controllers. That's part of the idea of the MVC pattern.

You seem to be very close to what you're looking for. If you want to directly query your db using SQL statements, see the Zend_DB->query() method. I think it is exactly what you asked for. http://framework.zend.com/manual/en/zend.db.statement.html

But really, doing that is throwing away a lot of the benefit of Zend_DB. If you build up a query using Zend_Select objects, you'll get automatic interpolation of a table/field prefix, parameterization of all queries, joins, etc. They really are great, if you can get up the SQL->ORM learning curve. http://framework.zend.com/manual/en/zend.db.select.html

And no matter what, be sure to use Zend's parameterization for queries, or you'll be throwing away all the great security and safety features of the DB layer.