1,105,625 Community Members

Drupal 7 db_select condition using different tables and conditions

Member Avatar
harikris2007
Newbie Poster
3 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hello friends,
In drupal 7
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['Type of Property']['PropertyType'] = array(
    '#type' => 'select', 
    '#title' => t('Type of Property'),
    '#options' => array(
      '' => t('Any'), 
      Flat => t('Flat'), 
      Compoundvilla => t('Compoundvilla'), 
     ),
  );


    $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.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 <= '{$Price2}' ";  
$operator = 'AND';
}

if(isset($Furnish) and  $Furnish != '' ) {
$qry .= " $operator  field_furniture_value ='$Furnish'";
}


</pre>


#2)

<pre>
$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 and i really need this asap.Thanks in advance.

LastMitch
Deleted Member
 
1
 

@harikris2007

number 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?

No, noone will write a query. Why can't you just go to the drupal forum and ask for help there. The reason why because it's their product and it's their template.

Member Avatar
Squidge
Posting Pro in Training
476 posts since Dec 2009
Reputation Points: 101 [?]
Q&As Helped to Solve: 75 [?]
Skill Endorsements: 6 [?]
 
0
 

Drupal documentation is very well presented. Have u looked through it?

And as LastMitch said +1

Their forum is probably the best place to ask,

Member Avatar
harikris2007
Newbie Poster
3 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

thanks for all the replies!!!!!i solved the the problem......

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article