Running a query when certain field s are not filled up in form

I am running a query like this

$dataProvider=new CActiveDataProvider('Post', array(

    'criteria'=>array(

        'condition'=>'status='.$_POST['Details']['status'].' and gender='.$_POST['Details']['status'].' and age='.$_POST['Details']['status'].' and name='.$_POST['Details']['name'],

        'order'=>'order_id DESC',

        'with'=>array('author'),

    ),

    'pagination'=>array(

        'pageSize'=>20,

    ),

));

Now status will be compulsory

Now When some one does not select the gender I do not want the query to run and search with a where condition in gender,

same goes for age, name.

i.e. When the user does not select that field in the search form I do not want to search for it in my query.

But right now what is happening is that if he does not select gender, age, name HE gets an sql error with showing where clause error on

where gender = and age= and name=

How can I solve this?

for example ternary isset($_POST[‘Details’][‘status’]) ? ‘status=’.$_POST[‘Details’][‘status’].’ : ‘’

Yes How will I implement it in my statement

$dataProvider=new CActiveDataProvider('Post', array(

    'criteria'=>array(

        'condition'=>'status='.$_POST['Details']['status'].' and gender='.$_POST['Details']['status'].' and age='.$_POST['Details']['status'].' and name='.$_POST['Details']['name'],

        'order'=>'order_id DESC',

        'with'=>array('author'),

    ),

    'pagination'=>array(

        'pageSize'=>20,

    ),

));

I would suggest using CDbCriteria for reasons of clarity

      $criteria = new CDBCriteria();


      $criteria->compare('status', $_POST['Details']['status'], false, 'AND');

      if($_POST['Details']['gender'] !== '')

            $criteria->compare('gender', $_POST['Details']['gender'], false, 'AND');

      //similarly for age and name


$dataProvider=new CActiveDataProvider('Post', array(

    'criteria'=>$criteria,

    'pagination'=>array(

        'pageSize'=>20,

    ),

));

Hi. I have several remarks.

First of all, using constructions kind of:

'condition'=>'status='.$_POST['Details']['status'].' and gender='.$_POST['Details']['status'].' and age='.$_POST['Details']['status'].' and name='.$_POST['Details']['name'],

…is a straight way to hell sql-injection.

Secondly, such lines as

$criteria->compare('status', $_POST['Details']['status'], false, 'AND');

gives you notice in case if no post data submitted.

To prevent any efforts to hack you script and to improve skills I suggest:

• use parameter bindng (PDO will escape your parameters)

• validate all data received from client (this will be helpful not only for validation, but for generating secondary objects)

// controller code:

public function getDetailsCriteria()

{

  $defaultCriteria = new CDbCriteria(  // default criteria will be applied even if no post submitted

    'order'=>'order_id DESC',

    'with'=>array('author'),

  ));

  $filterModel = new PostFilter();

  $filterModel->attributes = Yii::app()->getRequest()->getPost('Details', array());

  if ($filterModel->validate())

  {

    $defaultCriteria->mergeWith($filterModel->getConditions());

  }

  return $defaultCriteria;

}


// form model

class PostFilter extends CFormModel

{

  public $name;

  public $status;

  public $age;

  public $gender;


  public function rules()

  {

    return array(

      array('age', 'numerical', 'min' => 0, 'max' => 199, 'allowEmpty' => true),

      array('name', 'length', 'min' => 0, 'max' => 255, 'allowEmpty' => true), // name max length should not be greater that DB field length

      array('status', 'in', 'range' => array(STATUS_ACTIVE, STATUS_INACTIVE), 'allowEmpty' => true),

      array('gender', 'in', 'range' => array('male', 'female'), 'allowEmpty' => true),

    );

  }

  

  public function getConditions()  // returns conditional part of criteria 

  {

    $cond = array();

    $params = array();

    foreach ($this as $attribute => $value)

    {

      if ($value !== null)

      {

        $cond[] = $attribute . '=:' . $attribute; // 'name=:name'

        $params[':'.$attribute] = $value; // array(':name' => 'John')

      }

    }

    return array(

      'condition' => implode(' AND ', $cond),

      'params' => $params,

    );

  }

}


// initializing data-provider

$dataProvider=new CActiveDataProvider('Post', array(

    'criteria'=>$this->getDetailsCriteria(),

    'pagination'=>array(

        'pageSize'=>20,

    ),

));

Feel free to ask any questions if you have troubles with this brief example.