Skip to main content

Dynamic query in drupal 7

Today i am demonstrating how to write dynamic query in drupal 7,

$query  =  db_select('node','n')
                 ->fields('n',array('title','nid'))
                 ->condition('n.status',1)
                 ->execute();

It simply returns the object of the current query,

foreach($query as $result){
    echo $result->title;
}


How To Use database function in dynamic query

 $tbl = 'secc_rural';
 $house_hold_id = '09280030005';

  $query = db_select($tbl.".individual_21", "n");
  $query->fields('n',array('name','ahl_tin','ahl_tin','fathername','name_sl','mothername','occupation','yob','hof','genderid','relation','house_hold_id'));
  //$query->addExpression('substr(house_hold_id,1,11)',$house_hold_id);
  $query->where('substr(house_hold_id,1,11) = :hid',array(':hid' => $house_hold_id));

  $query = $query->extend('TableSort')->extend('PagerDefault')->limit(20);
  $result = $query->execute();

=========================================================
GROUP BY CLAUSE WITH HAVING CONDITION:-

 $res = db_select('ssid_generated_data.uwssid','gb');
               $res->groupBy('gb.ssid_batch');
               $res->addExpression('count(ssid_batch)','total_records');
               $res->havingCondition('gb.statecode','32');
               $res->havingCondition('gb.districtcode','09');
               $res->groupBy('gb.statecode');
               $res->groupBy('gb.districtcode');
               $res->groupBy('gb.tehsilcode');
               $res->groupBy('gb.towncode');
               $res->fields('gb',array('ssid_batch','statecode','districtcode','tehsilcode','towncode'));
             
               $all = $res->execute()->fetchAll();
You have to add as many fields on groupby as you choose as a field options


for further references: Visit
http://browse-tutorials.com/tutorial/sql-query-examples-drupal-7



Comments

Post a Comment

Popular posts from this blog

How to span column of custom table in Drupal

If you want to span the column of custom drupal table like below image, Follow the below code to make the header of the table , <?php $header = array('S.N','District', array('data' => '<div class ="house">Household </div><br><span>Rural</span> <p>Urban</p>','colspan' => 2), array('data' => '<div class ="house">Members</div> <br><span>Rural</span> <p>Urban</p>','colspan' => 2), 'Duplicate/Failed Registration', array('data' => '<div class ="house">Pending De duplication </div><br><span>Rural</span> <p>Urban</p>','colspan' => 2), 'Non Un-organised Workers', 'SSID Generated', 'No. of Card Personlised', ); $rows[] = arra...
1 comment
Read more

Drupal 8 : Link actions,Link menus,Link Tasks,Routings

Drupal 8 : Link actions,Link menus,Link Tasks,Routings Link actions Local actions have also been moved out of the hook_menu() system in Drupal 8 .Use actions to define local operations such as adding new items to an administrative list (menus, contact categories, etc). Local actions are defined in a YAML format, named after the module they are defined by. Such as menu_ui.links.action.yml for this example from menu_ui module: menu_ui.link_add:   route_name: menu_ui.link_add   title: 'Add link'   appears_on:     - menu_ui.menu_edit Here, menu_ui.link_add: It is the Unique name of the link action Most likely start with module name, route_name : Name of the route it means when click the link it redirect to this route, appears_on :  An array of route names for this action to be display on. Now how to know the Route name of any internal/external admin pages like below, By through the drupal console we achieve it, drupal debug:router...
Post a Comment
Read more

Cache In Drupal

Drupal 8 core caching modules: The Internal Page Cache module: this caches pages for anonymous users in the database. Pages requested by anonymous users are stored the first time they are requested and then are reused for future visitors. The Internal Dynamic Page Cache module: This is a key feature that Drupal 7 did not have. Unlike the Internal Page module, the Dynamic Page module aims to speed up the site for both anonymous and logged-in users. How Cache Works in Drupal: There are two modules available in the drupal core 1 - Internal page cache: The Internal Page Cache module caches pages for anonymous users in the database. Pages requested by anonymous users are stored the first time they are requested and then are reused. Configuring the internal page cache :   From the performance page we can set the maximum time how long browsers and proxies may cache pages based on the Cache-Control header. 2 - Internal dynamic page cache: Drupal 8 provides the Dynamic Page Cache m...
Post a Comment
Read more