Using function arguments to dynamically generate a query
- by Varun
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.
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.
