A first look at the Zend Framework
(Page 2 out of 4)Zend_Db
The Zend_Db component comes with several different sub-components, used for various things. Let's begin with the main database API component, called Zend_Db_Adapter, which makes it extremely simple to use any database that supports SQL, like MySQL, Microsoft SQL Server, or others. The following code is used to connect to a MySQL database:
require_once 'Zend/Db.php';
$db = Zend_Db::factory('pdo_Mysql', array('host' => '127.0.0.1',
'username' => 'malory',
'password' => '******',
'dbname' => 'camelot'
));
?>
When connecting to a database you must specify the right adapter, which is pdo_Mysql for MySQL and pdo_Sqlite for a SQLite database.
Zend_Db_Adapter also comes with two quoting functions to prevent SQL injection. The first one, $db->quote(), is pretty much just like the regular mysql_real_escape_string() function, but the second one $db->quoteInto() is much more useful, because it allows you to quote parameters into a string, for example:
// create a $db object, assuming Mysql as the adapter.
// quote a scalar into a WHERE clause
$where = $db->quoteInto('id = ?', 1);
// $where is now 'id = "1"' (note the surrounding quotes)
// quote an array into a WHERE clause
$where = $db->quoteInto('id IN(?)', array(1, 2, 3));
// $where is now 'id IN("1", "2", "3")' (a comma-separated string)
?>
As you can see in the above example, this allows you to prepare SQL statements with safe parameters.
Another neat piece of functionality is the inbuilt support for transactions. With the Zend_Db_Adapter it's possible to do transactions by calling the beginTransaction() function, and then either using commit() or rollBack() to save or undo the changes. See the following example:
// create a $db object, and then start a transaction.
$db->beginTransaction();
// attempt a query.
// if it succeeds, commit the changes;
// if it fails, roll back.
try {
$db->query(...);
$db->commit();
} catch (Exception $e) {
$db->rollBack();
echo $e->getMessage();
}
?>
The rest of the Zend_Db_Adapter is fairly standard, like update, delete and fetching functions.
Let's look at the Zend_Db_Select, which makes it really easy to select records. First create the db object:
require_once 'Zend/Db.php';
$params = array (
'host' => '127.0.0.1',
'username' => 'malory',
'password' => '******',
'dbname' => 'camelot'
);
$db = Zend_Db::factory('pdo_Mysql', $params);
?>
And after that, create the Zend_Db_Select object, by calling the select() function on the db object:
$select = $db->select();
// $select is now a Zend_Db_Select_PdoMysql object
?>
The advantage of the Zend_Db_Select object, over a regular SQL query, is that it allows you to use a fluent statement to select records, which is extremely easy to read, and completely prevents SQL injection mistakes. See the below example on how to use the Zend_Db_Select object:
//
// SELECT *
// FROM round_table
// WHERE noble_title = "Sir"
// ORDER BY first_name
// LIMIT 10 OFFSET 20
//
// you can use an iterative style...
$select->from('round_table', '*');
$select->where('noble_title = ?', 'Sir');
$select->order('first_name');
$select->limit(10,20);
// ...or a "fluent" style:
$select->from('round_table', '*')
->where('noble_title = ?', 'Sir')
->order('first_name')
->limit(10,20);
// regardless, fetch the results
$sql = $select->__toString();
$result = $db->fetchAll($sql);
// alternatively, you can pass the $select object itself;
// Zend_Db_Adapter is smart enough to call __toString() on the
// Zend_Db_Select objects to get the query string.
$result = $db->fetchAll($select);
?>
As you can see, it makes selecting records really easy, and so much easier. The Zend_Db_Select object supports pretty much everything, except for LEFT, RIGHT JOINs, etc. It only supports regular JOINs.
There are a few more database objects, which automate tasks even more, but to learn more about those have a look at the manual, and you can ignore the Zend_Db_DataObject as it does NOT exist (yet).
Let's have a look at the Zend_Feed component now.
March 6th, 2006 at 6:27 pm
So, compare these four lines:
$select->from(’round_table’, ‘*’);
$select->where(’noble_title = ?’, ‘Sir’);
$select->order(’first_name’);
$select->limit(10,20);
“SELECT * FROM round_table WHERE noble_title = ‘Sir’ ORDER BY first_name limit 10,20″
How is the 4 line object statement (copied below) “so much easier” than a single line SQL statement?
The SQL can be formatted however the user wishes, remaining readable, and happily including variables.
SELECT * FROM round_table
WHERE noble_title = ‘Sir’
ORDER BY first_name
LIMIT 10,20
The SQL-way has less typing, reads as an English sentence, both enhancing creation and maintenance. Importantly, the SQL select statement can be made in one string, so errors where a piece of the object has not been updated since a previous call, but the SQL still executes, are not possible.
For getting the job done, bog-standard PHP still seems to rule for me. My prediction is that the X on X fad will maintain a noisy list of supporters, but the 80% apps that make money, help us discover things, and change the world, are going to remain, like php, simple enough to get the job done, and leave SQL alone to do its lovely specialism.
I think Andy is right: Scaffolding saves 5 minutes once. Php saves you those minutes every hour for the rest of your coding. It stays out of your way while you work, understanding that no one can do the work for you, least of all a dumb language. Work remains a product of mass acceleration and distance: that makes it fundamentally hard.
March 6th, 2006 at 8:19 pm
Indeed it will be very interesting to see where this framework will go. I also like the input filtering approach in which the access to the raw data is removed. Marco Tabini wrote about this (or a similar) concept in the feb issue of PHP architect, calling it poka-yoke, which is Japanese for “fail-safe mechanism.”
March 8th, 2006 at 8:46 pm
Zend Framework - wejście smoka
Kilka dni temu została opublikowana pierwsza wersja zapowiadanego od dluższego czasu Zend Framework oznaczona numerkiem 0.1.1. Podszedłem do tego dość sceptycznie i z dystansem, bo publikacja tak wczesnej wersji mogła okazać się niewypałem, a …
August 6th, 2006 at 8:35 pm
Ok, so the argument to fetchAll is a string. So, like,
$result = $db->fetchAll(”SELECT * FROM round_table WHERE noble_title = ‘Sir’ ORDER BY first_name limit 10,20″)
does what you want, right?