Help setting up logic for advanced search parameters in PHP
I don't know if it's what you need, but I usually create some search class.
<?php$search = new Search('people');$search->minPay(1000);$search->maxPay(4000);$search->jobType('IT');$results = $search->execute();foreach ($results as $result){ //whatever you want}?>You can have all this methods, or have some mapping at __set() between method name and database field. The parameter passed to the constructor is the table where to do the main query. On the methods or mapping in the __set(), you have to take care of any needed join and the fields to join on.
There are much more 'enterprise-level' ways of doing this, but for a small site this should be OK. There are lots more ActiveRecord methods you can use as necessary. CI will chain them for you to make an efficient SQL request.
if($this->input->get('min_pay')) { $this->db->where('min_pay <', $this->input->get('min_pay'));}if($this->input->get('keyword')) { $this->db->like($this->input->get('keyword'));}$query = $this->db->get('table_name');foreach ($query->result() as $row) { echo $row->title;}
To use Search criterias in a nice way you should use Classes and Interfaces.
Let's say for example you define a ICriteria interface. Then you have different subtypes (implementations) of Criteria, TimeCriteria, DateCriteria, listCriteria, TextSearch Criteria, IntRange Criteria, etc.
What your Criteria Interface should provide is some getter and setter for each criteria, you'll have to handle 3 usages for each criteria:
- how to show them
- how to fill the query with the results
- how to save them (in session or database) for later usage
When showing a criteria you will need:
- a label
- a list of available operators (in, not in, =, >, >=, <, <=, contains, does not contains) -- and each subtypes can decide which part of this list is implemented
- an entry zone (a list, a text input, a date input, etc)
Your main code will only handle ICriteria elements, ask them to build themselves, show them, give them user inputs, ask them to be saved or loop on them to add SQL criteria on a sql query based on their current values.Some of the Criteria implementations will inherits others, some will only have to define the list of operators available, some will extends simple behaviors to add rich UI (let's say that some Date elements should provide a list like 'in the last day', 'in the last week', 'in the last year', 'custom range').
It can be a very good idea to handle the SQL query as an object and not only a string, the way Zend_Db_Select works for example. As each Criteria will add his part on the final query, and some of them could be adding leftJoins or complex query parts.