Cakephp和Ajax搜索

I have column keyword, date filter and status and now, i want to search using ajax, its working fine when send all the variable in data string, but when i just send keyword and other field empty its not working ? i want to know how to set WHERE clause when some field value pass empty.

Data String : keyword=assignment&date=created&fromdate=&todate=

string pass in cakephp controller but because date filed empty query not working.

Ajax

$('#keyword, #fromdate, #todate, input[name="status"]').on('change',function(){

    var status = "";
    if($('input[name="status"]').is(':checked')) {
        var val = $('input[name="status"]:checked').val();  
        status = '&status='+val;
    }

    var keyword = $('#keyword').val();
    var date = $('#date').val();

    var fromdate = $('#fromdate').val();
    var todate = $('#todate').val();

    var data = 'keyword='+keyword+'&date='+date+'&fromdate='+fromdate+'&todate='+todate+status;
    console.log(data);

    $.ajax({
        url:HOST+'assignments/assignment_search',
        type:'POST',
        data:data,
        beforeSend : function() {
            $('#all_assignment').html('<div id="loading_div"><img src="/img/loading.gif"/></div>');
        },
        success: function(data){
            if(data != "")
            {
                $('#all_assignment').html(data);
            }
        }
    });
});

controller :

                $where = "WHERE ";

                if(isset($this->data['status'])) {
                    $status_val = $this->data['status'];
                    $where .= "Assignment.status = $status_val";
                }

                if(!empty($keyword)) {
                    $where .= "Assignment.title LIKE '%$keyword%'";
                }

                if($fromdate != "" && $todate != "") {
                     $where .= " AND DATE_FORMAT(Assignment.created, '%Y-%m-%d') BETWEEN $fromdate AND $todate";
                }

                $assignment = $this->Assignment->query("
                    SELECT * FROM assignments as Assignment
                    $where
                ");

I want to add variable in where clause if its not empty. please help me to make query for searching.

You need to be more careful where you put your "AND" keywords. It seems to me that your generated query will be missing some "AND" words in some cases.

you should use find methods to do this easier, with a find method you could better define your conditions like this:

$conditions = array();
if(isset($this->data['status'])) {
   $conditions['Assignment.status'] = $this->data['status'];
}
if(!empty($keyword)) {
   $conditions['Assignment.title like'] = '%'.$keyword.'%';
}
if($fromdate != "" && $todate != "") {
    $conditions['Assignment.created between ? and ?'] = array($fromdate, $todate);
}

Those will automatically be placed into a query with 'AND' operator between them when you user them in a find(), now your CakePHP find() should be something like this:

$assignment = $this->Assignment->find('all',array('conditions'=>$conditions));

i havent tested that code to check if it has no errors but once you tweek it to your needs it should work fine.