Drupal 7 Cheat Sheet: The Database
This is a quick post while it's fresh in my head, regarding porting your contributed modules to the Drupal 7 API. This post specifically focusses on database manipulation and provides the like-for-like changes. If anything here is wrong, or could be done better, please let me know!
First up, individual results:
<?php
/*
* Remember this?
*/
$value = db_result(db_query("SELECT some_field FROM {some_table}"));
/*
* Forget it! It now looks like this:
*/
$value = db_query("SELECT some_field FROM {some_table}")->fetchField();
?>Selects still look pretty much the same, at first glance, but wait:
<?php
/*
* Let's fetch a database object in Drupal 6:
*/
$result = db_query("SELECT some_field FROM {some_table} WHERE some_other_field = %d", $some_var);
while ($row = db_fetch_object($result)) {
// Loop through your query results and do some stuff.
}
/*
* In Drupal 7 there is no db_fetch_object() or db_fetch_array():
*/
$result = db_query("SELECT some_field FROM {some_table} WHERE some_other_field = :some_other_field", array(':some_other_field' => $some_var));
foreach ($result as $row) {
// Loop through your query results and do some stuff.
}
?>Deletes and updates now look quite different. You don't use db_query() at all any more for those types of SQL query. Each one has its own query building function:
<?php
/*
* A delete query in Drupal 7:
*/
db_delete('some_table')
->condition('some_field', $some_var)
->execute();
/*
* An update query in Drupal 7:
*/
db_update('some_table')
->fields(
array(
'some_field' => $some_var,
'some_other_field' => $some_other_var,
)
)
->condition('some_identifying_field', $some_id_var)
->execute();
?>You should already be using drupal_write_record() for inserts anyway, and it has not changed, as far as I can tell.
Good news is hook_schema() and the related functions don't seem to have changed at all, so many of you won't need to touch installation scripts, unless you need to manipulate data as part of the update process, of course.
And that's that for now. Hope it's useful.
dynamic ones too!
Or you can use the dynamic version, something like:
<?php$query = db_select('file', 'f');
$query->join('image', 'i', 'f.fid = i.fid');
$query->condition('i.nid', $id, '=')
->fields('f')
->fields('i');
$result = $query->execute();
?>
Oh, this looks different, indeed ...
Oh, this really looks different indeed. Thanks a lot for this explanation.
Please add to the Examples module
The Example Module has a dbtng_example.module in it, but it mostly only has the easy things in it. Patches are welcome to demonstrate more extensive and complex examples. There are even a couple of issues open about these.
D7 database seems easy when first look at it... but then doing the real-life things you need to do sometimes gets a little more challenging.
similar tutorial
http://blog.worldempire.ch/story/howto-convert-module-dbtng
small correction
<?php$result = db_query("SELECT some_field FROM {some_table} WHERE some_other_field = %d", $some_var);
?>
should be
<?php$result = db_query("SELECT some_field FROM {some_table} WHERE some_other_field = :some_other_field", array(':some_other_field' => $some_var));
?>
Good catch
Haha, see what I did there? Good "catch"? *ahem*
I'll get my coat.
Updated! Thanks! =)
What about more complex queries?
A while back I ran into a problem in D6 with the schema module - it was throwing errors because I had used a date field and this isn't supported in the Drupal schema API.
I contributed a patch and now date and timestamp types are supported.
However there are plenty of types that are still not supported - from boolean to polygon.
I'm concerned that the D7 move will lock out more advanced database use.
Your examples above make me wonder how to represent multi table updates
I'm working on one right now
<?phpdb_query('update shorturl_link, (select count(*) as c, url_id from shorturl_access group by url_id) as access_count set clicks =access_count.c where access_count.url_id=shorturl_link.lid');
?>
This does the heavy lifting of data aggregation in a cron job - it does what I need in a single query.
My 'table' field is a join on a derived table - not a simple string.
I tried to look up the Database class being used - but the api module doesn't seem to be able to cope with documenting class structures yet
The syntax of the new API looks nice, and your examples are helpful.
Still possible
I think you *can* use
db_query()still for updates and deletes, but you shouldn't unless you have to. That's the impression I get, anyway.class DatabaseConnection extends PDO
After a bit of a struggle* I've found the docs I wanted
http://api.drupal.org/api/drupal/includes--database--database.inc/7/source
<?php/**
* Base Database API class.
*
* This class provides a Drupal-specific extension of the PDO database
* abstraction class in PHP. Every database driver implementation must provide a
* concrete implementation of it to support special handling required by that
* database.
*
* @see <a href="http://php.net/manual/en/book.pdo.php
" title="http://php.net/manual/en/book.pdo.php
">http://php.net/manual/en/book.pdo.php
</a> */
abstract class DatabaseConnection extends PDO {
?>
db_query calls pdo->db_query
so yes you can use this where required to run more complex updates etc.
* the api module doesn't tell you about the class hierarchy and, I have in the past managed to get phpdoc to run on drupal code - but can't get it working right now !
Post new comment