Drupal 7 db_select if condition using different tables and conditions

Posted by harikris2007 on December 12, 2012 at 8:24am

Hello Friends,

I have created a new form in theme.inc as follows:

<?php
  `function mymodule_example_form($form, &$form_state) {
   $form['Looking For']['PropertyCategory'] = array(
   '#type' => 'select', 
   '#title' => t('Looking For'),
   '#options' => array(
    '' => t('Any'), 
    Commercial => t('Commercial'), 
     Residential => t('Residential'), 
   ),
  );`

  ` $form['City ']['City'] = array(
'#type' => 'select', 
'#title' => t('City'),
   '#options' => array(
 '' => t('Any'), 
 Doha => t('Doha'), 
  ),
 );`






  `$form['Location']['Location'] = array(
  '#type' => 'select', 
  '#title' => t('Location'),
   '#options' => array(
   '' => t('Any'), 
     Doha => t('Doha'), 
       ),
);`


`$form['Price Between']['Price1'] = array(
'#type' => 'select', 
'#title' => t('Price Between'),
'#options' => array(
 '' => t('Any'), 
   2000 => t('2000'), 
  3000 => t('3000'),
   4000 => t('4000'),
    5000 => t('5000'),
    10000 => t('10000'),
      50000 => t('50000'),
     ),
  );`


`$form['Price Between']['Price2'] = array(
'#type' => 'select', 
'#title' => t('Price Between'),
 '#options' => array(
  '' => t('Any'), 
  2000 => t('2000'), 
   3000 => t('3000'),
    4000 => t('4000'),
    5000 => t('5000'),
    10000 => t('10000'),
      50000 => t('50000'),
       ),
   );`


 `$form['Furnishing']['Furnish'] = array(
   '#type' => 'select', 
  '#title' => t('Furnishing'),
  '#options' => array(
  '' => t('Any'), 
   Furnished => t('Furnished'), 
  UnFurnished => t('UnFurnished'),
    ),
   );`


 `$form['submit'] = array( '#type' => 'submit', '#value' => 'Search', );
 return $form; }`
?>

------------------------------------------------------------------------------------------------------------------------

# 1 is a working search query based on the above form.

i need to fetch nodes from content type name:properties. based on the below sql conditions

But i need a drupal7 db_select query with the all these conditions #2 is my try to create a drupal 7 db_query.But i can't move forward or i'm not sure whether it is right or not.Could you please write down a query for me?

# 1)

<?php
$qry  = 'SELECT fp.entity_id,        field_property_category_value,field_property_type_value, 
     field_city_value, field_price_value, field_location_value, field_furniture_value
     FROM field_data_field_property_category   AS   fpc ';
$qry .= 'JOIN field_data_field_property_type  AS  fpt  ON  fpc.entity_id =fpt.entity_id  ';
    $qry .= 'JOIN field_data_field_location   AS  fl  ON   fpt.entity_id =fl.entity_id  ';  
  $qry .= 'JOIN  field_data_field_city   AS  fc  ON  fl.entity_id =fc.entity_id  ';   
$qry .= 'JOIN  field_data_field_furniture  AS  ff  ON  fc.entity_id =ff.entity_id  ';   
$qry .= 'JOIN  field_data_field_price  AS  fp  ON  ff.entity_id =fp.entity_id  ';

  $operator = 'WHERE';

  if(isset($PropertyCategory) and $PropertyCategory != '') 
  {
  $qry .= "$operator field_property_category_value ='{$_POST['PropertyCategory']}' ";
  $operator = 'AND';
 }

 if(isset($PropertyType) and $PropertyType != '') {
$qry .= "$operator field_property_type_value ='{$PropertyType}' ";
$operator = 'AND';
 }

 if(isset($City) and $City != '') {
$qry .= "$operator field_city_value ='{$City}' ";
$operator = 'AND';
 }

if(isset($Location) and  $Location != '') {
$qry .= "$operator  field_location_value='{$Location}' ";
$operator = 'AND';
 }

  if(isset($Price1) and  $Price1 != '' and isset($Price2) and $Price2 !=''  ) {
    $qry .= "$operator field_price_value >= '{$Price1}' AND  field_price_value 
?>

2) here i tried to construct a db_select query..but i dont know how to check the if conditions..(if condition is for checking whether user select the option "ANY" ,in my search form the value of 'ANY' is

for e.g.

Any
Commercial
Residential

<?php


$query = db_select('field_data_field_property_category', 'fpc');
$query->join('field_data_field_property_type', 'fpt', 'fpc.entity_id =fpt.entity_id');
$query->join('field_data_field_location', 'fl', 'fpt.entity_id =fl.entity_id');
$query->join('field_data_field_city', 'fc', 'fl.entity_id =fc.entity_id');
$query->join('field_data_field_furniture', 'ff', 'fc.entity_id =ff.entity_id');
$query->join('field_data_field_price', 'fp', 'fc.entity_id =fp.entity_id');
$stat = $query
      ->fields('fb',array('entity_id'))
      ->fields('fpc',array('field_property_category_value'))
      ->fields('fpt',array('field_property_type_value'))
      ->fields('fc',array('field_city_value'))
      ->fields('fp',array('field_price_value'))
      ->fields('fl',array('field_location_value'))
      ->fields('ff',array('field_furniture_value'))
      ->condition('??', array(??,?,),'IN')

 }
      ->orderBy('name', 'ASC')
      ->extend('PagerDefault')
          ->execute();
?>

Please help me to write a Drupal 7 format select query for the #1 conditions and i really need this asap.Thanks in advance.

Comments

Any body there

Posted by harikris2007 on December 12, 2012 at 12:12pm

Please give a code for this........

harikris

db_select query for condition #1

Posted by cpsinghal on December 12, 2012 at 2:29pm

$query=db_select('field_data_field_property_category','fpc');
	$query->join('field_data_field_property_type', 'fpt', 'fpc.entity_id =fpt.entity_id');
	$query->join('field_data_field_location', 'fl', 'fpt.entity_id =fl.entity_id');
	$query->join('field_data_field_city', 'fc', 'fl.entity_id =fc.entity_id');
	$query->join('field_data_field_furniture', 'ff', 'fc.entity_id =ff.entity_id');
	$query->join('field_data_field_price', 'fp', 'fc.entity_id =fp.entity_id');
	$query->fields('fb',array('entity_id'))
    $query->fields('fpc',array('field_property_category_value'))
    $query->fields('fpt',array('field_property_type_value'))
    $query->fields('fc',array('field_city_value'))
    $query->fields('fp',array('field_price_value'))
    $query->fields('fl',array('field_location_value'))
    $query->fields('ff',array('field_furniture_value'))
	if(isset($PropertyCategory) and $PropertyCategory != '') 
	{
		$query->condition(db_and()->condition("field_property_category_value", $_POST['PropertyCategory'], '='));
	}
	if(isset($PropertyType) and $PropertyType != '') {
		$query->condition(db_and()->condition("field_property_type_value", $PropertyType, '='));
	}
	if(isset($City) and $City != '') {
		$query->condition(db_and()->condition("field_city_value", $City, '='));
	}
	if(isset($Location) and  $Location != '') {
		$query->condition(db_and()->condition("field_location_value", $Location, '='));
	}
	if(isset($Price1) and  $Price1 != '' and isset($Price2) and $Price2 !=''  ) {
		if(isset($Price1) and  $Price1 != ''   ) {
			$query->condition(db_and()->condition("field_price_value", $Price1, '>='));
		}
		if(isset($Price2) and  $Price2 != ''   ) {
			$query->condition(db_and()->condition("field_price_value", $Price2, '=<'));
		}
	}

You can use also BETWEEN operator looks below

	if(isset($Price1) and  $Price1 != '' and isset($Price2) and $Price2 !=''  ) {
               $query->condition(db_and()->condition('field_price_value', array($Price1, $Price2), 'BETWEEN'));
         }

chetan

really helpful!

Posted by harikris2007 on December 12, 2012 at 3:05pm

thank you Chetan,

I especially appreciate the information and advice you have provided.

Again, thank you so much. I greatly appreciate your generosity.

Let me try with this query, if i have any i will reply to this .Please answer ,if you have enough time..

harikris....

harikris

Some problems

Posted by harikris2007 on December 12, 2012 at 4:57pm

i'm sorry when i used the above query directly to and i got a blank screen ...
Do we need to add anything or should edit the above code?

Where will i add $result = $query->execute()->fetchField(); or any other syntax to fetch the rows?

I diid some edits in the above code,but not sure...this time i got the page without any error

.....please check the code as follows.........i also appended the echoed $query's output

# edited code

<?php
    $query=db_select('field_data_field_property_category','fpc');
    $query->join('field_data_field_property_type', 'fpt', 'fpc.entity_id =fpt.entity_id');
    $query->join('field_data_field_location', 'fl', 'fpt.entity_id =fl.entity_id');
    $query->join('field_data_field_city', 'fc', 'fl.entity_id =fc.entity_id');
    $query->join('field_data_field_furniture', 'ff', 'fc.entity_id =ff.entity_id');
    $query->join('field_data_field_price', 'fp', 'fc.entity_id =fp.entity_id');
    $query->fields('fp',array('entity_id'))
     
           ->fields('fpc',array('field_property_category_value'))
        ->fields('fpt',array('field_property_type_value'))
        ->fields('fc',array('field_city_value'))
        ->fields('fp',array('field_price_value'))
        ->fields('fl',array('field_location_value'))
        ->fields('ff',array('field_furniture_value'));
      
              
    if(isset($PropertyCategory) and $PropertyCategory != '') 
    {
        $query->condition(db_and()->condition("field_property_category_value", $_POST['PropertyCategory'], '='));
    }
    if(isset($PropertyType) and $PropertyType != '') {
        $query->condition(db_and()->condition("field_property_type_value", $PropertyType, '='));
    }
    if(isset($City) and $City != '') {
        $query->condition(db_and()->condition("field_city_value", $City, '='));
    }
    if(isset($Location) and  $Location != '') {
        $query->condition(db_and()->condition("field_location_value", $Location, '='));
    }
    
    if(isset($Furnish) and  $Furnish != '') {
        $query->condition(db_and()->condition("field_furniture_value", $Furnish, '='));
    }
    if(isset($Price1) and  $Price1 != '' and isset($Price2) and $Price2 !=''  ) {
        if(isset($Price1) and  $Price1 != ''   ) {
            $query->condition(db_and()->condition("field_price_value", $Price1, '>='));
        }
        if(isset($Price2) and  $Price2 != ''   ) {
            $query->condition(db_and()->condition("field_price_value", $Price2, '=<'));
        }
    }
      $result = $query->execute()->fetchField();
?>

# echoed $query's output :
This is printed when in submitted a search condition.

<?php
SELECT fp.entity_id AS entity_id, fpc.field_property_category_value AS field_property_category_value, fpt.field_property_type_value AS field_property_type_value, fc.field_city_value AS field_city_value, fp.field_price_value AS field_price_value, fl.field_location_value AS field_location_value, ff.field_furniture_value AS field_furniture_value FROM {field_data_field_property_category} fpc INNER JOIN {field_data_field_property_type} fpt ON fpc.entity_id =fpt.entity_id INNER JOIN {field_data_field_location} fl ON fpt.entity_id =fl.entity_id INNER JOIN {field_data_field_city} fc ON fl.entity_id =fc.entity_id INNER JOIN {field_data_field_furniture} ff ON fc.entity_id =ff.entity_id INNER JOIN {field_data_field_price} fp ON fc.entity_id =fp.entity_id WHERE ( (field_property_category_value = :db_condition_placeholder_0) )AND( (field_city_value = :db_condition_placeholder_1) )AND( (field_furniture_value = :db_condition_placeholder_2) )
?>

Could you please check this?

thanks,

Hari

harikris

Modify query

Posted by cpsinghal on December 13, 2012 at 7:25am

$result = $query->execute()->fetchAll();

You can use fetchAll() and fetchObject().
fetchField is use for fetch only one field.

After fetchAll() when you will print $result then you will understand how to further work.

chetan

thanks Chetan,

Posted by harikris2007 on December 13, 2012 at 9:32am

Now i can fetch and display all the results based on our conditions.....

How can we add pagination to this results??can i use
->extend('PagerDefault')
->limit(2); ///want two items per row...

How can we show paging...
can i use this theme_pager(2, 0);

it is not showing any pagination.....

harikris

pager theme

Posted by cpsinghal on December 13, 2012 at 10:35am

theme('pager');

when u add theme('pager'); drupal pagination work own;

chetan

paging

Posted by harikris2007 on December 13, 2012 at 11:39am

i added theme('pager'); and showing Drupal pagination.

but when i click on the first page , there is no result and the result page is a blank ..

I think when i click on the first or next page ,the page is refreshing and the post values are not passing...
How can we over come this.....

FYI ...i have passing this values from the search form by calling the $_POST['filed_value'] ......

w3qatar.info/alderarcs/search-list s the page.....please have a look...

harikris

theme pager

Posted by cpsinghal on December 17, 2012 at 4:08pm

r u adding

 ->extend('PagerDefault')
->limit(2); 

echo below line
theme('pager');
drupal default paging use get method.

chetan