This post is my effort to guide you through the whole new abstraction layer for accessing the database server, which comes with the Drupal 7 core distribution. In this blog-post I’ll try to explain to you guys (girls) how this new layer works, without diving into all the details, in order to give you a clear view on this new system.

First of all I’ll explain the benefits of this new system to you. Drupal needed a database system that could easily support multiple database servers in a unified way which preserves the syntax power of SQL. The system was also build to enforce the security checks. There are 6 different types of Query’s which can be called in this database system: Insert, Update, Delete, Merge, Static and Dynamic.

Now lets get it started!

Settings.php

We should start by taking a look at the renewed database definition in the settings.php. In most cases you will be using a drupal site with only one database. For this you should use the following structure.

<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb’,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘localhost’,
);
?>

The first thing you should notice are the values “default” in the $database array. The first one is the CONNECTION KEY, the second one is the TARGET.

The connection key is a unique identifier for a database connection, there must ALWAYS be a ‘default’ connection key available.

The target is used to define master/slave database structures. If the master (default) isn’t available, the system will search for the slave database. It is also possible to flag a query to run on the slave database. To make this a bit clearer I’ll give you a more complicated database structure.

<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb1′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver1′,
);
$databases['default']['slave'][] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb2′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver2‘,
);
?>

In this case, the first database is the default database, the second is the slave databases.

It is also possible to define separate database structures.

<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb1′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver1′,
);
$databases['extra']['default'][] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb2′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver2‘,
);
?>

Note that no matter how many connections are defined in the settings file, These connections will not be used by Drupal until they are actually opened.

Select Query

So far for the database connections, now lets take a look at the actual use of query’s in this new database layer. For the regular select query’s not much changes. Here is an example of a select query with a short explanation.

$result = db_query(“SELECT nid, title FROM {node} WHERE type = :type”, array(
‘:type’ => ‘page’,
));

The db_query function uses three arguments, first one is the query string, the second one are the values used to fill up the placeholders. The third one will be explained at the next example.

Note that the placeholder (:type) doesn’t use quotes. Another thing you should take in to account is to put your database names between {}. This is needed for the database system to attache a prefix string if this is defined in your settings.

Now for the third argument of the db_query we will take a look at the following code:

<?php
$result = db_query(“SELECT nid, title FROM {node}”, array(), array(
‘target’ => ’slave’,
));
?>

The third argument is an array of configuration directives to detect the way the query should run. In this case the query runs on the slave database. I won’t go into the details here, because (as i said before) in this blog post I will only pick up the basics of the new database system.

The following is just a handy guide about the way you can use the database query’s results. (Not 100% relevant to this post, but this might come in handy for some of you. The others should just scroll through the code, as if it doesn’t exist).

$result = db_query(“SELECT nid, title FROM {node}”);
foreach ($result as $record) {
// Do something with each $record
}

$record = $result->fetch(); // Use the default fetch mode.

$record = $result->fetchObject(); // Fetch as a stdClass object

$record = $result->fetchField($column_index); // Fetch only one field.

$number_of_rows = $result->rowCount(); //Count the results.

Insert Query

Now we’ve arrived to he fun part. The INSERT, DELETE and UPDATE query’s require that you use the query builder object in order to behave consistently across all different databases. This is where the new object-oriented query API comes in.

The compact INSERT form is the following:

<?php
$nid = db_insert(‘node’)
->fields(array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
))
->execute();
?>

This will result in the following query:

INSERT INTO {node} (title, uid, created) VALUES (’Example’, 1, 1221717405);

Note: If you don’t call the execute() method, the query will not run!

The insert query object can also be used with multiple values. To insert multiple rows you shouldn’t only use fields() but also values(). In this case fields() only defines the fields, but doesn’t put any content into the selected fields. The values() may be called multiple times in order to add more than one line to your database.

<?php
$query = db_insert(’node’)

->fields(array(‘title’, ‘uid’, ‘created’))
->values(array(
array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
array(
‘title’ => ‘Example 2′,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
)))
->execute();
?>

Using a ‘foreach’ the code will look like this:

<?php
$values = array(
array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
array(
‘title’ => ‘Example 2′,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
);
$query = db_insert(‘node’)->fields(array(‘title’, ‘uid’, ‘created’));
foreach ($values as $record) {
$query->values($record);
}
$query->execute();
?>

Update Query

Next stop is the UPDATE query. The update query is pretty straight forward, if you understand how the insert query’s work, it shouldn’t be a problem to understand the update query. Here it goes:

<?php
$num_updated = db_update(‘node’)
->fields(array(
‘uid’ => 5,
’status’ => 1,
))
->condition(‘created’, REQUEST_TIME - 3600, ‘>=’)
->execute();
?>

This will result in the following query:

UPDATE {node} SET uid=5, status=1 WHERE created >= 1221717405;

Not much to explain here, so let’s go to the DELETE query’s.

Update Query

Again the same story here. The DELETE query is probabily the easiest form of the query object:

<?php
$num_deleted = db_delete(‘node’)
->condition(‘nid’, 5)
->execute();
?>

This will result in the following query:

DELETE FROM {node} WHERE nid=5;

Update Query

Finally we’ve got to the last one. The MERGE query. This one is a bit more complicated. If you would strip this one down to it’s original form, you will finde that a merge query is actually just the combination of an insert and an update query. In php it would be something like this:

<?php
if (db_query(”SELECT COUNT(*) FROM {example} WHERE id=:id”, array(’:id’ => $id)->fetchField()) {
// Run an update using WHERE id = $id
}
else {
// Run an insert, inserting $id for id
}
?>

In the new database API structure the merge query’s are build up like this:

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->execute();
?>

Here the “example” table is used. The specified key field ‘name’ has the value of $name. Now two things could happen.

First option: If the $name value exists in the database, then fields “field1” and “field2” will get an update with the correspondingvalues.

Second option: If the $name value doesn’t exist in the database, a new row will be created in which “name” gets the value $name, “field1” gets the value $field1 and “field2” gets the value $field2.

In some cases the values you want to set will have to be different, according to the fact that the key field does or doesn’t already exist. This can be handled in two ways.

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->update(array(
‘field1′ => $alternate1,
))
->execute();
?>

In this case, if the “name” already exists the value of “field1” will be $alternate1, and the value of “field2” will be $value2. If the “name” doesn’t allready exist, $value1 and $value2 will be used.

It is also possible to use expressions. I’ll give you an example in which, if the ‘name’ already exists, the “value1” field will become the current value +1:

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->expression(‘field1′, ‘field1 + :inc’, array(‘:inc’ => 1))
->execute();
?>

Note that expression() can be used multiple times, 1 time for each field.

Field updates can also be limited, if the row already exists. In this case, if the “name” already exists, only “field2” will be updated, and “field1” will be ignored:

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->updateExcept(‘field1′)
->execute();
?>

This is the end of my quick guide into the new Drupal 7 database layer. Hope you’ve enjoyed it. For more detailed information about this system, I would like to refere to the official drupal database API: http://drupal.org/developing/api/database

I use Zend_Layout to create a global view template for my website and the view (.phtml) that corresponds to a controller/action will be loaded into the $this->layout->content variable automatically.

All good but what to do with a form in your global template?

Building the form (Zend_Form) into your view isn’t really a clean option and what about validation of the form? There is no controller/action from where the form was build and so there is no controller/action where to post the form to.

Solution:

I read through the Zend manual and I discovered there was a view helper named ‘Action’.

What does it do?

It enables you to call a controller/action from within your view script. It will process the controller/action code, render the view and return it into your view script.

Example:

IndexController.php

class IndexController extends Zend_Controller_Action {

public function indexAction(){

}

public function formAction(){

//build a form

$form = new Zend_Form();

//create a text element

$elementName = new Zend_Form_Element_Text(’name’);

$element->setRequired(true); //a validation saying our field has to be filled out

//create a submit button

$elementSubmit = new Zend_Form_Element_Submit(’submit’);

$elementSubmit->setLabel(’Send’);

//add elements to the form

$form->addElement($elementName);

$form->addElement($elementSubmit);

//if we click the ‘Send’ button

if($this->getRequest()->isPost()){

if($form->isValid()){

//form is valid

}

}

//add the form to a view variable

$this->view->form = $form;

}

}

index/form.phtml

<?php echo this->form; ?>

layout.phtml

<?php echo$this->doctype(Zend_View_Helper_Doctype::XHTML1_STRICT) . “\n”; ?>

<html>

<head>

</head>

<body>

<div id=”container”>

<?phpecho$this->layout()->content.”\n”;?>

</div>

<div>

<?php echo $this->action(’form’, ‘index’); ?>

</div>

</body>

</html>

parameter list of the view action

action($action, $controller, $module = null, array $params = array())

Result:

The form action of the index controller is called from the layout.phtml.

The business logic is executed, the view (index/form.phtml) is rendered and is returned to our layout.phtml.

When we press the ‘Send’ button in our form. The form will be submitted to index/form and the validation will happen.

2) Changing the charset of the HTML file in the <head> section

3) Sent a header via PHP

4) Using the mb_string module for apache

5) The Database

Extra

1) Communication with Doctrine

2) Communication with AMFPHP

1) Making sure the HTML file is saved as UTF-8

Displaying UTF-8 characters in the browser.

First we make sure that when we save an html file that contains UTF-8 characters, it really is saved as UTF-8 text. I use Eclipse as IDE and you will find the setting at the following location:

• Windows: Window > Preferences… > General > Workspace : Text file encoding
• Mac: Eclipse > Preferences… > General > Workspace : Text file encoding

Check the ‘Other’ radio button and choose ‘UTF-8′ from the dropdown list. Press the ‘OK’ button

2) Changing the charset of the HTML file in the <head> section

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<body>

When we make an HTML file and use UTF-8 characters, it will display the characters correctly.

Using a PHP server to generate a page

When we use a PHP server to process the php code and receive HTML there is another catch we have to keep in mind. The server could have a server encoding setting which could screw up the UTF-8 characters. We solve this by sending a header in the php script.

3) Sent a header via PHP

At the top of our page we put the following PHP code:

header('Content-type: text/html; charset=utf-8');

The server which processes the page now also sends the UTF-8 characters as UTF-8 to the client.

Manipulating the UTF-8 data in PHP

Everybody uses the strlen() function to get the length of a string. Now there is a problem when using the strlen() function on an UTF-8 string. The strlen() function reads the number of bytes instead of the number of characters. So when we execute the command : echo strlen(’être’);

It will return 5 and not 4 as we expected. This is because the character ê is a character of 2 bytes.

Obviously this is not the behavior we want.

4) Using the mb_string module for apache

The solution to this problem is installing a module ‘mb_string’ which has to be loaded into apache.

It is a module with a set of standard functions that can operate on multi byte strings (such as UTF-8)

More info can be found at http://be2.php.net/mb_string

So instead of using strlen() we use mb_strlen().

But also here there is a catch. The module ‘mb_string’ has an internal encoding setting.

We have to set the internal encoding to UTF-8.

There are 2 ways for doing this

1) add the string ‘UTF-8′ as second parameter to the function

echo mb_strlen('être',‘UTF-8′);

2) or changing the ini setting via the commmand

ini_set('mbstring.internal_encoding','UTF-8');

5) The database

The final step in our way to success is setting up the database correctly.

You have to make sure that your table + the fields have a collation that supports UTF-8 such as utf8_general_ci

This should do the trick to display and manipulate UTF-8 based characters in PHP.

EXTRA (Thanks to Filip Heymans)

1) Communication with Doctrine

2) Communication with AMFPHP

1) Communication with Doctrine

Doctrine is a persistence framework for PHP. Instead of using SQL to create CRUD operations, you will create objects and perform actions on those objects. More info at http://www.doctrine-project.org.

It is simular to Hibernate for Java.

When considering using doctrine, you must also set a property to the UTF-8 encoding or the insert and update statements will not produce the expected result.

Doctrine uses a connection object. You have to set the charset on that connection object via the setCharset method.

$conn->setCharset(’UTF8′);

2) Communication with Amfphp

AMFPHP is a free open-source PHP implementation of the Action Message Format(AMF). AMF allows for binary serialization of Action Script (AS2, AS3) native types and objects to be sent to server side services.

In the gateway.php file in the amphp folder. You have to find the rule

$gateway->setCharsetHandler("utf8_decode", "ISO-8859-1", "ISO-8859-1");

and change it to

$gateway->setCharsetHandler("none", "UTF-8", "UTF-8");

This makes sure you also pass utf-8 encoded characters to your flash or flex application.