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);
Leave a Reply