Урок 5 - База данных

********* * Database API * Drupal 7

• ******* ********** *********** * **** ******.
• ******** ****** ******* db_query(). ((***********))
• ********* ****** *** ****** * ********* * **** ******.

*********** * **** ******

********* ******** *********** ***** * **** ******:

// * Drupal 6
// $db_url = 'mysqli://username:password@localhost/basename';

// * Drupal 7
$databases['default']['default'] = array(
  'driver'   => 'mysql',
  'database' => 'basename',
  'username' => 'username',
  'password' => 'password',
  'host'     => 'localhost',
);

********** ******** * ******* db_query() * Drupal 7

Drupal 7 ************ db_query(), ** db_query() ******* *** ****** ** *********(placeholders).

******* ******** ********* db_query() * Drupal 6 * Drupal 7:

// ******: SELECT uid, login FROM {users} WHERE status = 1 AND name = "user"

// *** *** Drupal 6
$users = db_query('SELECT uid, login FROM {users} WHERE status = %d AND name = "%s"', 1, 'user');

// *** *** Drupal 7
$users = db_query('SELECT uid, login FROM {users} WHERE status = :status AND name = :name', array(':status' => 1, ':name' => 'user'));

********* ****** *** ****** * ***** ******.

****** *** ********** ******** * **** ****** ************** ******* db_query(). ****** ******* ** ***** ******** (*******, ********, **********, *******) ************* **** ******* (db_insert(), db_delete(), db_update(), db_select()). ****** ** **** ******* ********** ****** *********** ************** * ********* SQL ******.

******* *** ****** * ***** ******

• ******* db_and()
• ******* db_close()
• ******* db_condition()
• ******* db_delete()
• ******* db_driver()
• ******* db_escape_field()
• ******* db_escape_table()
• ******* db_insert()
• ******* db_like()
• ******* db_merge()
• ******* db_next_id()
• ******* db_or()
• ******* db_query()
• ******* db_query_range()
• ******* db_query_temporary()
• ******* db_select()
• ******* db_set_active()
• ******* db_transaction()
• ******* db_truncate()
• ******* db_update()
• ******* db_xor()

********* *********** ******** **** **********. ****** ******* db_result(), db_fetch_array(), db_fetch_object() * *. *., ****** ****** ****** DatabaseStatementInterface:

• ***** execute() - ********** *******
• ***** fetchAllAssoc() - ********** ********* ******* * **** ************** ******* ****** ********* ****
• ***** fetchAllKeyed() - ********** ********* *** ****** ************* ******
• ***** fetchAssoc() - ******** ********* ****** * ********** ** * ******** ************** *******
• ***** fetchCol() - ********** **** ******* *************** ******, *** *************** ******
• ***** fetchField() - ********** **** **** ** ********* ****** ** *************** ******
• ***** getQueryString() - ********** ****** *******
• ***** rowCount() - ********** ***** *****, ******* ********** ******

*********** ******** ********** ******* ******** ***** ****** execute().

******* ********** ******** * **** ******.

****** ** ********** ****** *******, * ********* * ******** ** ********* ******.

****** ********** ****** *** Drupal 6 * Drupal 7.

// ******: SELECT * FROM {node}

// *** Drupal 6 ******* **** ** *****:
$result = db_query("SELECT * FROM {node}');
print_r(db_fetch_object($result));

// *** Drupal 7 ******* 1
$result = db_select('node')->fields('node')->execute();
print_r($result->fetchObject());

****** ********* ****** *** Drupal 6 * Drupal 7, * ********:

// ******: SELECT * FROM {node} WHERE status = 1

// *** Drupal 6 ******* **** ** *****:
$result = db_query("SELECT * FROM {node} WHERE status = %d', 1);
print_r(db_fetch_object($result));

// *** Drupal 7 ******* 1
$result = db_select('node')->fields('node')->condition('status', 1)->execute();
print_r($result->fetchObject());

// *** Drupal 7 ******* 2
$nodes = db_select('node');
$nodes
  ->fields('node')
  ->condition('status', 1);
$result = $nodes->execute();
print_r($result->fetchObject());

// *** Drupal 7 ******* 3
$nodes = db_select('node');
$nodes->fields('node');
$nodes->condition('status', 1);
$result = $nodes->execute();
print_r($result->fetchObject());

****** ********* ****** * Drupal 6 * Drupal 7, *** ******** *******:

// ******: SELECT n.title, n.created, u.name FROM {node} n JOIN {users} u ON n.uid = u.uid WHERE status = 1 GROUP BY u.uid ORDER BY created DESC LIMIT 0, 10

// *** Drupal 6 ******* **** ** *****:
$result = db_query_range('SELECT n.title, n.created, u.name FROM {node} n JOIN {users} u ON n.uid = u.uid GROUP BY u.uid ORDER BY created DESC', 0, 10);
print_r(db_fetch_object($result));

// *** Drupal 7
$query = db_select('node', 'n');
$query->join('users', 'u', 'n.uid = u.uid');
$query->groupBy('u.uid');
$query->fields('n', array('title', 'created'));
$query->fields('u', array('name'));
$query->orderBy('created', 'DESC');
$query->range(0, 10);
$result = $query->execute();
while ($record = $result->fetchAssoc()) {
   print_r ($record);
}

****** ******* ****** *** Drupal 6 * Drupal 7.

// Drupal 6
db_query("INSERT INTO {table_name} (int_value, float_value, string_value) VALUES (%d, %f, '%s')", 100, 123.456, 'This is ******');
$id = db_last_insert_id();

// Drupal 7
$id = db_insert('table_name')
  ->fields(array(
    'int_value' => 100,
    'float_value' => 123.456,
    'string_value' => 'This is ******',
  ))
  ->execute();

****** ********** ****** *** Drupal 6 * Drupal 7.

// Drupal 6
db_query('UPDATE {node} SET `title` = "%s", `status` = %d WHERE `uid` = %d', 'This is ******', 1, 10);

// Drupal 7
$id = db_update('node')
  ->fields(array(
    'title' => 'This is ******',
    'status' => 1,
  ))
  ->condition('uid', 10)
  ->execute();

****** ******** ****** *** Drupal 6 * Drupal 7.

// Drupal 6
db_query('DELETE FROM {node} WHERE `uid` = %d AND `created` < %d', 10, time()-86400);

// Drupal 7
$id = db_delete('node')
  ->condition('uid', 10)
  ->condition('created', time()-86400, '<')
  ->execute();

*********

• http://drupal.org/node/310069 - ****** ******** DB API.
• http://sidashin.ru/ - **** ***** ******** * ****** ********.
• http://internetdevels.ru/