sql to filter data from dropdown boxes

[eluser]richzilla[/eluser]
Im not sure i entirely understand your question but ill give it a shot anyway.

Your looking for a way to add a search function into a site, and add 'default' values into your dropdown boxes that don't necessarily appear in your database? The first bit is relatively easy. In your controller simply add an extra arbitrary value onto your array of values. 0 or -1 is a good idea as it doesn't need to be changed if you find yourself getting more results than you anticipated. (the -1 idea came from facebook). The sql query itself is a bit more complicated. In your model if you check that the returned value does not equal your default value, then add the relavant 'where' clause onto your query, and build it as a string. I wrote a very similar function for a project we were working on:

Code:

function db_search ($search_array)
        {
            $start_date = $search_array['start_date'];
            $end_date = $search_array['end_date'];
            
            $query_string = "SELECT issues.*, assignments.assignment_user_id, priorities.priority_code, status_list.status_code
                                   FROM issues
                                   LEFT JOIN assignments ON issues.issue_id = assignments.assignment_issue_id
                                   LEFT JOIN priorities ON issues.issue_priority_id = priorities.priority_id
                                   LEFT JOIN status_list ON  issues.issue_status_id = status_list.status_id
                                   WHERE issue_raised > ".mysql_real_escape_string($start_date) ."
                                   AND issue_raised < ". mysql_real_escape_string($end_date);
            
            if($search_array['raised_by'] != 0)
            {
                $query_string .= " AND issue_raised_by = ".mysql_real_escape_string($search_array['raised_by']);
            }
            
            if($search_array['open_closed'] == 1)
            {
                $query_string .= " AND issue_closed_by IS NULL";
            }
            elseif($search_array['open_closed'] == 2)
            {
                $query_string .= " AND issue_closed_by IS NOT NULL";
            }
            
            if($search_array['priority'] != 0)
            {
                $query_string .= " AND issue_priority_id = ".$search_array['priority'];
            }
            
            if($search_array['status'] != 0)
            {
                $query_string .= " AND issue_status_id = ".$search_array['status'];
            }
            
            if($search_array['assigned'] != 0)
            {
                $query_string .= " AND assignments.assignment_user_id = ".$search_array['assigned'];
            }
            
            $result = $this->db->query($query_string);
            
            $data = $result->result();
            
            return $data;
        }