Using PDO in Drupal 7

Below are the list of Sql queries used to use in Drupal coding -:

(1)Simple select query: SELECT * FROM users;

$query = db_select('users');
$query->Fields('users');
$results = $query->execute();

(2)Select selected columns: SELECT uid, uname, mail FROM users;

$query = db_select('users');
$query->Fields('users', array('uid', 'uname', 'mail'));
$results = $query->execute();

(3)Add colomn on coditions to select query:

$query = db_select('users', 'u');
$query->addField('u', 'uid');
$query->addField('u', 'uname');
$query->addField('u', 'mail');
$results = $query->execute();

(4)Calculated Columns: SELECT uid, name, CONCAT(CAST(uid as CHAR), name, mail) as newc FROM users;

$query = db_select('users', 'u');
$query->addField('u', 'uid');
$query->addField('u', 'name');
$query->addExpression('CONCAT(CAST(uid as CHAR), name, mail)', 'newc');
$results = $query->execute();

(5)Conditional select - conditon($field, $value, $operator) - $operator is default "=" if "IN" then $value should be an array : SELECT * FROM user where uid = 5

$query = db_select('users', 'u');
$query->Fields('u');
$query->Condition('uid', 5);
$result = $query->execute();

(6)Use "AND" and "OR" in where conditions: SELECT * FROM users WHERE uid =5 OR mail LIKE '%asiq@gmail.com'

$query = db_select('users', 'u');
$query->Fields('u');
$conditions = db_or();
$conditions->condition('uid', 5);
$conditions->condition('mail', '%asiq.gmail.com', 'LIKE');
$query->condition($conditions);
$result = $query->execute();

(7)Use both "AND" and "OR" in where condition: SELECT * FROM users WHERE ( uid =5 OR name LIKE '%asiq%') AND (mail = 'someemail@gmail.com')

 $query = db_select('users', 'u');
$query->Fields('u');
$conditions = db_or();
$conditions->condition('uid', 5);
$conditions->condition('name', '%asiq%', 'LIKE');
$query->condition($conditions);
$query->condition('mail','someemail@gmail.com');
$result = $query->execute();

(8) Sub-query: SELECT name FROM users WHERE uid IN ( SELECT uid FROM users WHERE uid  0);

$subquery = db_select('users', 'u');
$subquery->addField('u', 'uid');
$subquery->codition('uid', '0', '');

$query = db_select('users');
$query->addField('users', 'name');
$query->condition('uid', $subquery, 'IN');
$result = $query->execute();

(9)Joining: SELECT N.type, N.title, U.name, F.field_first_name_value FROM node N, user U, field_data_first_name F WHERE N.nid = F.entity_id AND N.uid = U.uid AND U.uid = 1

$query = db_select('node', 'N');
 $query->Fields('N', array('type', 'title'));
$query->addJoin('INNER', 'users', 'U', 'N.uid = U.uid');
$query->addJoin('INNER', 'field_data_first_name', 'F', 'N.nid = F.entity_id');
$query->addField('U', 'name');
$query->addField('F', 'field_first_name_value');
$query->condition('U.uid', 1);
$result = $query->execute();

(10)Insert query: INSERT INTO variables (name, value) values('asiq', '2');

$query = db_insert('variables');
$query->Fields( array('name' => 'asiq', 'value' => '2'));
$result = $query->execute();

(11)Update query: UPDATE users SET mail='asiq.harvest@gmil.com' WHERE uid =2

$query = db_update('users');
$query->Fields( array('mail' => 'asiq.harvest@gmail.com'));
$query->condition('uid', 2);
$result = $query->execute();

(12)Delete query: DELETE FROM node WHERE nid = 3

$query = db_delete('node');
$query->condition('nid', 3);
$result = $query->execute();

(13)  select * from mytable where concat(mytable.name,mytable.tel) like %value%

$query = db_select('node', 'u');
$query->Fields('u');
$query->where("concat(title, type) like '%'", array());
$result = $query->execute()->fetchAll();


(14) More - 

$record = $result->fetch();            // Use the default fetch mode.
$record = $result->fetchObject();  // Fetch as a stdClass object.
$record = $result->fetchAssoc();   // Fetch as an associative array.
$record = $result->fetchField($column_index);// To fetch just a single field out of the result set
$number_of_rows = $result->rowCount(); // To count the number of Rows returned use

To fetch all records at once into a single array, use one of the following:

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();
// Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);
// Retrieve a 2-column result set as an associative array of field 1 => field 2.
$result->fetchAllKeyed();
// You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be field 0 => field 2
$result->fetchAllKeyed(1,0); // would be field 1 => field 0
// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defaults to first column
$result->fetchCol($column_index);
Tags: 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Syntax highlight code surrounded by the <pre class="brush: lang">...</pre> tags, where lang is one of the following language brushes: css, jscript, php.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.