database api, your new friend
TRANSCRIPT
![Page 2: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/2.jpg)
About meFrancisco Alonso Borragán
Drupal Developerwww.kikoalonsob.com
IRC #drupal_es : kikoalonsobTwitter : @kikoalonsob
![Page 3: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/3.jpg)
Index1. Intro2. Static queries3. Dynamic queries
1. Select queries2. Insert queries3. Update queries4. Delete queries
4. Query alteration5. Working against other DB6. Questions
![Page 4: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/4.jpg)
IntroAbstraction layer for accessing database servers
Support multiple database serversDynamic construction of queriesEnforce security checks and good practicesProvide an interface for intercept and modify queries
![Page 5: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/5.jpg)
Static queries
![Page 6: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/6.jpg)
Static queriesExplicit query string (Better for performance)Used for simple select queriesReturns a prepared statement object, already executed
db_query($query, array $args = array(), array $options = array());
![Page 7: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/7.jpg)
Static queries
$uid = 1;$result = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid), array('fetch' => PDO::FETCH_ASSOC) );
PrefixingAll table names must be wrapped in { }
![Page 8: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/8.jpg)
Static queries
$uid = 1;$result = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid), array('fetch' => PDO::FETCH_ASSOC) );
PlaceholdersLiteral or an array that will be inserted into a query for execution
![Page 9: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/9.jpg)
Static queries
$uid = 1;$result = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid), array('fetch' => PDO::FETCH_ASSOC) );
Query optionsSet the query behavior (typically only two directives)
target (master,slave)fetch (PDO::FETCH_OBJ, PDO::FETCH_ASSOC, etc)
![Page 10: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/10.jpg)
Static queries
$uid = 1;$result = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid), array('fetch' => PDO::FETCH_ASSOC) );
and...
how to use the result?
![Page 11: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/11.jpg)
Iterating over the resultwith a foreach loop...
foreach ($result as $record) {// Do something with each $record}
![Page 12: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/12.jpg)
Iterating over the result...or with a while loop
if ($result) { while ($record= $result->fetch()) { // Do something with each $record }}
Instead of use $result->fetch(), we can use:
$result->fetchAssoc() //to fetch an associative array$result->fetchObject() //to fetch an object$result->fetchField($column_index) //to fetch just a single field
![Page 13: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/13.jpg)
All results at once into anarray
//Indexed array$result->fetchAll()//Associative array keyed by $field value$result->fetchAllAssoc($field) //associative array of field 1 => field 2$result->fetchAllKeyed()//associative array of field I => field j$result->fetchAllKeyed(i, j) //Single array$result->fetchCol(i=0)
![Page 14: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/14.jpg)
Dynamicqueries
![Page 15: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/15.jpg)
Dynamicqueries
Dynamically builtUsed for some select queriesUsed for all insert, update, delete and merge queries
![Page 16: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/16.jpg)
Select queriesAlways start using
$query = db_select($table, $alias = NULL, array $options = array());
and need at least one field
$query -> fields(‘table_or_alias’, array(‘field_1’,’field_2’));$query -> fields(‘table_or_alias’); //All table fields
![Page 17: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/17.jpg)
Joins
You can add one or more joins
$query->join($table, $alias=NULL, $condition=NULL, $arguments=array());
* join can be replaced by innerJoin, rightJoin or leftJoin too.
![Page 18: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/18.jpg)
Conditional clauses
$query -> condition($field, $value = NULL, $operator = NULL);
$operator accepts =, <, >=, IN, LIKE, BETWEEN, etc…
By default, conditions are concatenated with ANDUse db_and(), db_or() or db_xor() to overwrite it
db_or = db_or();$db_or -> condition(‘type’, ‘page’, ‘=’);$db_or -> condition(‘field_1’, array(12, 15), ‘IN’);$query -> condition ($db_or);
![Page 19: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/19.jpg)
Ordering
$query -> orderBy($field, $direction = 'ASC')
Grouping
$query -> groupBy($field)
Ranges and limits
$query -> range($start = NULL, $length = NULL)
![Page 20: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/20.jpg)
how to use the result?Remember: db_query() returns a prepared statement object, already
executed
But ... db_select() returns a SelectQuery object
Now, you can use the $result like in static queries
$result = $query -> execute()
![Page 21: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/21.jpg)
Insert queriesAlways start using:
$query = db_insert($table, array $options = array());
We have to specify values with fields($value) functionCompact form: The preferred form for most Insert queriesDegenerate form: Useful for running a multi-insert query
![Page 22: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/22.jpg)
Compact formA simple $key => $value array where $key is the field name.
$query = ->fields(array( 'title' => 'Example', 'uid' => 1, ))$query->execute();
![Page 23: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/23.jpg)
Degenerate formfields() only specifies field namesvalues() specifies a $key => $value array
$query = db_insert('node');$query -> fields(array('title', 'uid'));$values = array( array('title' => 'Example', 'uid' => 1), array('title' => 'Example2','uid' => 1));foreach ($values as $record) { $query -> values($record); }$query->execute();
![Page 24: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/24.jpg)
Insert based on the result of a selectBuild the select query, but not execute!!
$query = db_insert('node'); -> from($select);$query -> execute();
![Page 25: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/25.jpg)
Update queriesSimilar to insert queries, start using
$query = db_update($table, array $options = array());
and continuate specifying fields (like in insert queries) and conditions
$query -> fields($table_alias, array $fields = array()) -> condition($field, $value, $operator); $query -> execute();
![Page 26: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/26.jpg)
Delete queriesYes... start using
$query = db_delete($table, array $options = array());
and continue with a condition
$query -> condition($field, $value, $operator) ->execute();
![Page 27: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/27.jpg)
Query alterationMost interesting feature of this API (IMHO)
Allow other modules to alter queries on the fly
![Page 28: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/28.jpg)
Preparing your queryAdd aone or more tags to identify the query
$query -> addTag('myTag');
Attach meta data to provide additional context (Opt.)
$query -> addMetaData('node', $node);
![Page 29: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/29.jpg)
How to modify a query?In your custom module, you can use
hook_query_alter(QueryAlterableInterface $query) // orhook_query_TAG_alter(QueryAlterableInterface $query)
and inside them...
$query -> hasTag($tag);$query -> hasAnyTag($tag_1, $tag_2);$query -> hasAllTags($tag_1, $tag_2);$query -> getMetaData($key);
![Page 30: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/30.jpg)
In your mymodule.views.inc
hook_views_query_alter(&$view, &$query);
Can I modify a views query?Yes, you can!
![Page 31: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/31.jpg)
Working againstother DB
In your settings.php:
$databases = array();$databases['default']['default'] = array( // Drupal's default credentials here.);$databases['my_other_db']['default'] = array( // Your secondary database's credentials here.);
![Page 32: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/32.jpg)
Working againstother DB
In your module
db_set_active('my_other_db');db_set_active(); //Connect to default
![Page 33: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/33.jpg)
Working againstother DB
You can define the connection directly in your module
$my_other_db = array ( 'database' => 'my_other_db', 'username' => 'username', 'password' => 'password', 'host' => 'localhost', 'driver' => 'mysql', );Database::addConnectionInfo('YourDatabaseKey', 'default', $my_other_db);db_set_active('YourDatabaseKey');
![Page 34: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/34.jpg)
2
![Page 35: Database API, your new friend](https://reader033.vdocuments.us/reader033/viewer/2022052822/554f3965b4c905cd048b4f7b/html5/thumbnails/35.jpg)
thanks