D2ev
CMS

Using PDO in Drupal 7

3 Mins Read
Pinterest LinkedIn Tumblr

Below is 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 '%<a href="mailto:asiq@gmail.com">asiq@gmail.com</a>'
 
$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 = '<a href="mailto:someemail@gmail.com">someemail@gmail.com</a>')
 
 $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','<a href="mailto:someemail@gmail.com">someemail@gmail.com</a>');
$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='<a href="mailto:asiq.harvest@gmil.com">asiq.harvest@gmil.com</a>' WHERE uid =2
 
$query = db_update('users');
$query->Fields( array('mail' => '<a href="mailto:asiq.harvest@gmail.com">asiq.harvest@gmail.com</a>'));
$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);

 

 

Related Posts

Write A Comment