Database Expressions

Database expressions are used extensively and even required when you begin creating complex SQL statements. Typical expressions include NOW(), COUNT(), LOWER(), and SUB(), just to name a few. You can review the RDBMS's documentation for a complete list of expressions you can use.

When it comes to Zend_Db, these expressions pose a problem. If you use a Zend_Db_Adaptor and try to pass in the literal string NOW() as an example, the PHP script will fail before it reaches the insert() method with the error message Call to undefined function NOW(). At this point, NOW() in the code is seen as just another PHP function, not a database expression. You need a way to tell PHP to ignore the database expression call and allow the database to handle the function. The Zend_Db_Expr class lets you do just that.

Listing 5-5 demonstrates the functionality of the Zend_Db_Expr class. Apart from that, the code inserts only one user. You use the insert() method and pass in two parameters: the name of the table, accounts, and a key-value pair array. The important difference is how you treat the NOW() database expression. You create a new instance of the Zend_Db_Expr class and using its constructor you pass in the expression as a string: "NOW()".

Listing 5-5. Zend_Db_Expr Usage: testExpressionAction

* Test Expression

* Using Database Expressions.

public function testExpressionAction() {

try{

//Create a DB object require_once "Db/Db_Db.php"; $db = Db_Db::conn();

$userData = array("username" => 'test_7',

"email" => '[email protected]', "password" => 'password', "status" => 'active',

"created_date"=> new Zend_Db_Expr("NOW()"));

//Insert into the accounts. $db->insert('accounts', $userData);

//Close Connection $db->closeConnection();

echo "Completed Inserting";

}catch(Zend_Db_Exception $e){ echo $e->getMessage();

//Supress the View

$this->_helper->viewRenderer->setNoRender();

Pull up the browser and enter the URL http://localhost/account/test-expression to run the action. When the action finishes, open the database and take a look at the records currently saved in the accounts table. You should see a new user with an accurate date in the created_date column for the account.

Zend_Db_Expr contains only two methods: its constructor and the_toString() method. The constructor accepts a single parameter as a string. The string is a database expression such as NOW(), SUM(), or COUNT(), and can be used during the calls to insert() and update(). (You'll learn more in the "Updating Records" section.)

Was this article helpful?

0 0

Post a comment