How to dynamically generate a SQL query based on user preferences?

Question

I need to create a graphical interface with which users can select several attributes that will be used to query the database to find suitable people. I am looking for ideas on how to dynamically generate a database query according to user choices.

Query will contain several fields, but to get this idea, I will give only three of the following:

• Profession - can be from 0 to n lines of employment. If busy lines are specified, one of them must match.

• Age - age can be set as:

  • exact match (30)
  • (e.g. 30-40)
  • less value (-40)
  • greater than value (30 -)

An age parameter is optional in the request. In addition, the user can indicate whether age is a desired parameter. If this is not required, and the person has no age, this is his profile, age criteria are ignored for this person.

• Height - Similar to Age

Request example:

No criteria were given:

select * from persons

Given only lesson:

select * from persons where occupation = 'dentist'

Several classes were provided:

select * from persons where (occupation = 'dentist' or occupation = 'engineer')

Age was set as greater than the value, and it must exist in the personal profile:

select * from persons where age >= 30

Height is set as a range, and it does not have to exist in your personal profile:

select * from persons where (height is null or (height >= 30 and height <= 40))

Combination of various criteria:

select * from persons where occupation = 'dentist' and age >= 30 and (height is null or (height >= 30 and height <= 40))

I have already implemented code capable of generating queries as strings, but this, of course, is not too pretty. I am looking for ideas that would be the most effective and beautiful way to achieve this.

Answer 1

- Zend_Db_Select. () , .

$select = $db->select();
$select->from( /* ...specify table and columns... */ )
       ->where( /* ...specify search criteria... */ )
       ->where( /* ...specify other criteria... */ )
       ->order( /* ...specify sorting criteria... */ );

, , , , , , , - , .

, , , , , (faux code).

    request = Request->getParams()            // selection criteria set from GUI
    sql     = Products->getBaseQuery(request) // basic query for requested View
    sql     = Decorator->applyUserConfig(sql) // custom fixed user filter
    results = sql->execute()

Answer 2

, , , :

select * from persons where (occupation = 'dentist' or occupation = 'engineer')

:

select * from persons where occupation IN ('dentist','engineer')

PHP script.

Answer 3

, , , , , .