Filter database results without running an additional query

Question

I'm using CodeIgniter for a classifieds website. Here's a specific example of what I'm trying to achieve:

On page http://www.example.com/browse/Real-Estate all the Real Estate classifieds are listed. I'm using CI's Pagination Class to paginate the results.

On the leftbar, a set of filters are shown that correspond to each db column of the "Real Estate Classifieds" table. For example:

Type : House, Apartment Bedrooms : 1,2,3,4... Furnished: Yes, No etc.

Each time the user selects a value, a query runs and the results are updated with Ajax.

However, since the selecting of a value corresponds to an additional "WHERE" clause in the initial query, I was wondering which would be the best way to filter the already available results without re-running the query.

Answer 1

Why are you opposed to running an additional query? If the user starts with a broad query with no filters, filtering it down would mean iterating over the result set and performing filter checks on every row. If the user starts with a narrow query with filters, changing or removing filters would require a new query anyway, and adding filters would require iteration over the result set.

If I were you, I'd build a filter system that works in all conditions, sends information to the server to be built into a query, builds a view with the query results, and returns the HTML as a string to the browser where you can use jQuery (or whatever) to insert it into your results box. Doing this would save you the most time, allow you to write the least amount of Javascript, and, I think, make your application appear quite snappy (as opposed to using Javascript to iterate over large result sets).

Answer 2

Basically at the beginning you can bring all the results by -

select * from TABLE

Then when user selects any specific criteria you can use ajax to post that criteria.

The ajax will be calling a controller function -

function update_criteria(){
//Here you can call an another function say aply_criteria as follows.

array_walk('your actual result array','apply_criteria');

//finally parse the html with new filtered result throuh ajax.

 echo $this->parser->parse('view_name','data array',TRUE);

//catch the above html in the sucess callback function of ajax and replace it in the 
//previous html page.

Note :- the parser i have used above is an library in an codigniter so make sure you load it before using.

}

function apply_criteria(){

//Actual logic to unset the specific key value pair 
//(which does not satisfy the  criteria depending on the post data) from array.

}

array_walk documentation - http://www.php.net/manual/en/function.array-walk.php