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

Popular posts from this blog

Get The field values of node in Drupal 8

use Drupal \ node \ NodeInterface ; /** * Implements hook_ENTITY_TYPE_insert() for node entities. * * This tests saving a node on node insert. * * @see \Drupal\node\Tests\NodeSaveTest::testNodeSaveOnInsert() */ function node_test_node_insert ( NodeInterface $node ) { // Set the node title to the node ID and save. if ( $node - > getTitle ( ) == 'new' ) { $node - > setTitle ( 'Node ' . $node - > id ( ) ) ; $node - > setNewRevision ( FALSE ) ; $node - > save ( ) ; } } Now There is so many functions are there to get the values, For All the functions available visit the API code, https://api.drupal.org/api/drupal/core%21modules%21node%21src%21NodeInterface.php/interface/NodeInterface/8.2.x Some of as below, Node edit form, Drupal 8 Automatically Load the whole object no need to load the entity like below, if ($event->getFormId() == 'node_alexa_audio_clips_edit_form') { $node = \Drupal::ro...

Drupal Setup with docker

Option 1:  docker pull mysql docker pull phpmyadmin docker pull drupal docker run -d --name MysqlClient -e MYSQL_ROOT_PASSWORD=drupal mysql Download the phpmyadmin image and Setup Container: docker run --name phpmyadmin -d --link MysqlClient:db -p 8083:80 phpmyadmin Download the Drupal image and Setup Container: docker run -d --name dweb2 --link MysqlClient -p 8082:80 -e MYSQL_USER:root -e MYSQL_PASSWORD:drupal drupal   We are using docker4 drupal and following below URL https://wodby.com/docs/1.0/stacks/drupal/local/ From Mount my codebase Download and unpack docker4drupal.tar.gz with hidden files in a new directory, also get the drupal code with in web directory composer create - project drupal / recommended - project my_site_name_dir It means with in new directory we have docker files and drupal files also has web folder for drupal. now run docker-compose up -d Now PROJECT_BASE_URL is mentioned in .env file open this url in the web with port, it will open the drupal page, ...