Help setting up logic for advanced search parameters in PHP

I would like to create an advanced search form much like a job site would have one that would in开发者_运维百科clude criteria such as keyword, job type, min pay, max pay, category,sub category etc...

My problem is deciding on how best to set this up so if I have to add categories to the parameters I'm not having to modify a whole bunch of queries and functions etc...

My best guess would be to create some sort of associative array out of all of the potential parameters and reuse this array but for some reason I feel like it's a lot more complex than this. I am using CodeIgniter as an MVC framework if that makes any difference.

Does anybody have a suggestion as how best to set this up?

Keep in mind I will need to be generating links such as index.php?keyword=designer&job_type=2&min_pay=20&max_pay=30

I hope my question is not to vague.

---

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.

---

Search queries can be a pain sometimes, but not as big of a pain as pagination. Luckily, CodeIgniter helps you out a bit with this with their pagination library.

I think you're on the right track. The basic gist, I would say, is:

1. Grab your GET variables from the URL.
2. Create your database query (sanitize the GET values).
3. Generate the results set.
4. Do pagination.

Now, CodeIgniter destroys the GET variable by default, so make sure you enable http query strings in your config file.

Good luck!

---

I don't know anything about CodeIgniter, but for the search application I used to support, we had drop-down combo-boxes with category options stored in a database table and would rely on application and database cacheing to avoid round-trips each time the page was displayed (an opportunity for learning in itself ;-). When you update the table of job_type, location, etc. the new values will be displayed in your combo-box.

It depends on

• how many categories you intend to have drop-down lists
• how often you anticipate having to update the list

• how dynamic you need it to be.

• And the size of your web-site and overall activity are factors you will have to consider.

I hope this helps.

P.S. as you appear to be a new user, if you get an answer that helps you please remember to mark it as accepted, or give it a + (or -) as a useful answer

---

A pagination class is a good foundation. Begin by collecting query string variables.

<?php

    // ...in Pagination class

    $acceptableVars = array('page', 'delete', 'edit', 'sessionId', 'next', 'etc.');

    foreach($_GET as $key => $value) {
        if(in_array($key, $acceptableVar)) {
            $queryStringVars[] = $key . '=' . $value;
        }
    }

    $queryString = '?' . implode('&', $queryStringVars);

    $this->nextLink = $_SEVER['filename'] . $queryString;

    ?>

---

Duplicate the searchable information into another table. Convert sets of data into columns having two values only like : a search for color=white OR red can become a search on 10 columns in a table each containing one color with value 1 or 0. The results can be grouped after so you get counters for each search filter. Convert texts to full text searches and use MATCH and many indexes on this search table. Eventually combine text columns into one searchable column. The results of a seach will be IDs which you can then convert into the records with IN() condition in SQL

---

Agile Toolkit allows to add filters in the following way (just to do a side-by-side comparison with CodeIgniter, perhaps you can take some concepts over):

$g=$this->add('Grid');
$g->addColumn('text','name');
$g->addColumn('text','surname');
$g->setSource('user');

$conditions=array_intersect($_GET, array_flip(
  array('keyword','job_type','min_pay'));

$g->dq->where($conditions);

$g->dq is a dynamic query, where() escapes values passed from the $_GET, so it's safe to use. The rest, pagination, column display, connectivity with MVC is up to the framework.

---

    function maybeQuote($v){
        return is_numeric($v) ?: "'$v'";
    }

    function makePair($kv){
    +--  7 lines: $a = explode('=', $kv);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    }

    function makeSql($get_string, $table){
    +-- 10 lines: $data = explode('&', $get_string);
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    }
    $test = 'lloyd=alive&age=40&weather=hot';
    $table = 'foo';
    print_r(makeSql($test, $table));