Using function arguments to dynamically generate a query

Question

I am working on an issue management system, developed in PHP/MySQL. It requires search functionality, where the user will mention the search parameters and based on these parameters the system will return the result set.

To solve this I am trying to write a function and all the user selected parameters are passed as arguments. Based on the arguments I will dynamically generate the query. At times(based on choices made by user) one or more of these arguments can be null.

A Sample Query:

    select * from
        tickets 
        inner join ticket_assigned_to 
            on tickets.id=ticket_assigned_to.ticket_id
    where 
        tickets.project_id= in ('')
        and tickets.status in ('')
        and ticket_assigned_to.user_id in ('')
        and tickets.reporter_user_id=''
        and tickets.operator_user_id in ('')
        and tickets.due_date between '' and ''
        and tickets.ts_created between '' and '';
    

I also need to handle cases where the arguments can be ORed or ANDed in the query.

For example:

 
    select * 
    from
        tickets 
        inner join ticket_assigned_to 
            on tickets.id=ticket_assigned_to.ticket_id
    where 
        tickets.project_id= in ('')
        and tickets.status in ('')
        or tickets.due_date = ''
        or tickets.ts_created between '' and '';

I am also planning to use the same function at other places in the project also. Like to display all the tickets of a user or all tickets created between given dates and so on...

How to handle this situation?
Should I go with a single function which handles all this or numerous small functions? Need guidance here.

Answer 1

I don't think that directly converting a users serach query into mysql query is a good idea. Basically you just become more vulnerable against SQL injection.

I recommend to use an abstraction layer like PHP ActiveRecord and for search maybe a full-text search engine like Sphinx which has it's own advanced query mode, where you can query via @author Peter or Sam | Martha.

Answer 2

What about using the Specification Pattern to build up your query criteria?

Links are not PHP specific but hopefully convey how you might approach building up your WHERE criteria:

LINQ expression trees & the Specification pattern
C# Specification Pattern Example
Google Books result - Jason Sweat's Guide to PHP Design Patterns
Learn the Specification Pattern