To call the specific SQL functions of your
database server, Zend_Db_Select class has a helper class called Zend_Db_Expr. Zend_Db_Expr is used for
calling SQL functions or creating other expressions for use in SQL. Let??™s consider an example where we want
to extract the names of our users in lower case. The code is in Listing 5.5.
Listing 5.5: Using functions in SQL statements
$select = $db->select();
$columns = array(id, "CONCAT(first_name, ' ', last_name" as n); #1
$select->from('users', $columns);
$stmt = $db->query($select);
$result = $stmt->fetchAll();
(annotation) <#1 CONCAT() is a MySQL specific function.>
The from() function realizes that a bracket has been used in the columns parameter and so converts it to a
Zend_Db_Expr automatically for us. We can however always use Zend_Db_Expr ourselves by setting the
columns statement explicitly:
$columns = array(id, "n"=> new
Zend_Db_Expr("CONCAT(first_name, ' ', last_name"));
Now that we have considered how to abstract the differences between database engines away by using
adapters created by the factory method of Zend_Db, we can now turn our attention to how we use a database
within an application. Novice web programmers tend to put the database calls required directly where they
need them which leads to a maintenance nightmare with SQL statements spread all over the application.
Pages:
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157