CodeIgniter/jQuery database filter system

I'm trying to create a dynamic filter system using ajax with CodeIgniter & jQuery.

I have a list of items that are pulled from my database, and a list of checkboxes to filter these items. I want to be able to select more than one checkbox allowing various filters.

W开发者_Python百科hen a filter checkbox is clicked I serialize the form, but I'm unsure how to pass this to codeigniter and how to deal with it. So far I have tried this, passing the serialized data in a query string.

$('#filter input').bind('change', function(){
    var f = $('#filter').serialize();
    $('#list').load('http://localhost/testing/filter?'+f);
});

My serialized form returns the following, corresponding to the filter check boxes.

cat_0=1&cat_1=1

In CodeIgniter I can easily create this sql query, querying the database and returning items that match the filters.

SELECT * FROM `items` WHERE `category` ='0' OR `category` ='1'

I guess my main problem is getting category information from my serialized form into CodeIgniter. Or is there another more efficient method of filtering. Any help would be much appreciated. Thanks

EDIT

My problem wasn't receiving the query string, it was how to deal with it in CI. I ended up doing this. It's quite messy so if anyone can see improvements please let me know. Thanks

I just built a sql query using the categories in the received query string.

function filter(){

    if($this->input->get('cat_0', true)) {
        $a[] = " OR `category` = '0'";
    }
    if($this->input->get('cat_1', true)) {
        $a[] = " OR `category` = '1'";
    }
    if($this->input->get('cat_2', true)) {
        $a[] = " OR `category` = '2'";
    }
    if($this->input->get('cat_3', true)) {
        $a[] = " OR `category` = '3'";
    }
    if($this->input->get('cat_4', true)) {
        $a[] = " OR `category` = '4'";
    }
    if($this->input->get('cat_5', true)) {
        $a[] = " OR `category` = '5'";
    }   
    if($this->input->get('cat_6', true)) {
        $a[] = " OR `category` = '6'";
    }

    if(!isset($a)){
        echo "no items";
    } else {
        $sql = 'SELECT * FROM `items` WHERE ';
        $a[0] = str_replace(" OR ", "", $a[0]); //remove 'OR' from first
        foreach($a as $b){
            $sql = $sql.$b;
        }       
        echo "<pre>";
            print_r($this->db->query($sql)->result());
        echo "</pre>";
    }       
}

EDIT PART II

I'm coming back to this question, I now need to add a username check to the query. I've tried the following wit now luck.

function filter(){

    $this->db->where('username', $this->ion_auth->profile()->username);

    foreach($_GET as $key=>$value){
        if($value == 1){
            $key = explode('_', $key);
            $this->db->or_where('cat', $key[1]);                
        }
    }

    print_r($this->db->get('items')->result());
        }

This is the SQL statement I need to create and works in my database.

SELECT * FROM `items` WHERE `username` = 'tmp_username' AND `cat` = '0' OR `cat` = '6';

if your url is correct, you are trying to use "querystrings" with codeigniter which is a uri segment based url.

in your system/applicaiton/config/config.php on line 151 there should be this line

$config['enable_query_strings'] = FALSE;

Change this value to TRUE and see if that allows you to use $this->input->get('cat_0'); and $this->input->get('cat_1'); to grab the data from the url.

Also, to make sure your url is correct, try hitting it directly in the browser passing some arbitrary data, and make sure you're not getting a 404 or 500 message, but the data returned to the browser as you expect.

NOTE

While this method should work, the correct way to send data via a .load() is to pass it as the second parameter, so for your example $('#list').load('path/to/file', f);

EDIT

This is how I would accomplish what you are trying to, utilizing Codeigniter's ActiveRecord Class makes it much less messy to build queries gradually like you are trying to do.

function filter(){

    foreach($_GET as $key=>$value) {
        if ($value == 1) {
            $key = explode('_',$key);
            $this->db->or_where('category', $key[1]);
        }
    }

    if(count($_GET) > 0){
        $query = $this->db->get('items');
        echo "<pre>";
            print_r($query->result());
        echo "</pre>";
    } else {
        echo "no items.";
    }       
}

Better way is use ajax to pass the data to your controller.

$('#filter input').bind('change', function(){
    var f = $('#filter').serialize();
    $.ajax({
       type: "POST",
       url: "http://localhost/testing/filter",
       data: "value="+f,    
       dataType: "json",
       success: function(data){
          //code after success.
       }
   });
});