Zend Framework: Proper way to interact with database?

Question

I'm fairly new to the Zend Framework and MVC and I'm a bit confused by Zend_DB and the proper way to interact with the database.

I'm using the PDO MySQL adapter and have created some classes to extend the abstract classes:

class Users extends Zend_Db_Table_Abstract {
    protected $_name = 'users';
    protected $_primary = 'user_id';
    protected $_rowClass = 'User';

    public function getUserbyID($id) { /* code */ }
    // More code here
}
class User extends Zend_Db_Table_Row_Abstract {
    // Code here
}
class Widgets extends Zend_Db_Table_Abstract {
    protected $_name = 'widgets';
    protected $_rowClass = 'Widget';

    public function getWidgetsfromUser($userid) { /* code */ }
    // More code here
}
class User extends Zend_Db_Table_Row_Abstract {
    public function doSomethingWithWidget() { /* code */ }
    // More code here
}

There seems to be so many ways to access the DB (fetchAll(), find(), fetchAll() through adapter, insert(), createRow() and save(), select() object) that I always find myself going back to the docs to figure out what I should be doing.

SO has taught me prepared statements are the way to go, and I've been trying to use rowsets and row (should I be?), but I'm still confused as to what's the best way to interact with the database?

(apologies for the terribly open ended question)

Answer 1

Using Zend_Db you probably don't want to get into the details of prepared statements and the like. You just want to use the model objects to do basic CRUD (Create, Read, Update and Delete). I know the Programmer's Reference Guide is extensive, but its a great introduction to Zend_Db. You may want to take a closer look at the Zend_Db_Table documentation.

But to give a quick answer to your question. Unless you need to override some default behavior you shouldn't need to extend the Zend_Db_Table_Row_Abstract. Also you can probably simplify the Users class to just be:

class Users extends Zend_Db_Table_Abstract {
  protected $_name = 'users';

  // Code here
}

Then to use it you would do some of things you mentioned using the following:

//Create a Users Model Object
$usersDb = new Users();

//Find the record with user_id = 4 and print out their name
$row = $usersDb->find(4);
echo $row->first_name . ' ' . $row->last_name

//Change the first name of this user to Brian    
$row->first_name = 'Brian';
$row->update();

//Insert a user into the database
$data = array(
  'first_name' => 'Gilean',
  'last_name' => 'Smith');
$usersDb->insert($data);

//Retrieve all users with the last name smith and print out their full names
$rows = $usersDb->fetchAll($usersDb->select()->where('last_name = ?', 'smith'));    
foreach ($rows as $row) {
  echo $row->first_name . ' ' . $row->last_name
}

Answer 2

In general, people prefer to access the database through the Table and Row objects, to match their habits of object-oriented programming.

The OO approach is useful if you need to write code to transform or validate query inputs or outputs. You can also write custom methods in a Table or Row class to encapsulate frequently-needed queries.

But the object-oriented interface is simplified, unable to perform all the types of database operations you might need to do. So you can delve deeper and run a SQL query against the Zend_Db_Adapter methods like query() and fetchAll() when you require finer control over your SQL.

This is pretty common for object-oriented interfaces to databases. An OO layer that could duplicate every SQL feature would be insanely complex. So to compromise, an OO layer usually tries to provide easy ways to do the most common tasks, while giving you the ability to go under the covers when necessary.

That's a very general answer to your very general question.

Answer 3

I recommend using the save method.

//Create a Users Model Object
$usersDb = new Users();

//Find the record with user_id = 4 and print out their name
$row = $usersDb->find(4);
echo $row->first_name . ' ' . $row->last_name

//Change the first name of this user to Brian    
$row->first_name = 'Brian';
$row->save();

//Insert a user into the database
$newUser = $usersDb->fetchNew();
$newUser->first_name = 'Gilean';
$newuser->last_name  = 'Smith';
$newUser->save();

// OR if you get your data from post or any array
$newUser = $usersDb->fetchNew();
$newUser->setFromArray($data_from_post);
$newUser->save();

the reason why i like this approach more is because this why you always have an instance of the user model and you can have custom methods in them ( ex isAdmin ) and also because you might want to ovewrite the save/insert/update function on userRow to do something before they are inserted/updated.