Best and efficient way to code multiple queries

[erezvol]

Here is my query:

"SELECT ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit
 FROM data
 ORDER BY c_time"

Here is the time (o_time, c_time) : 2017-08-14 07:20:36

I want to add an option to get results for various time periods

All – all results (this is the current query). This option will be the default option.

This year or any previous year in a pull down menu

A certain munth (this month is default)

Any time range (e.g. 2017-10-14 to 2019-8-17)

Certain day of week (e.g. all Mondays)

Specific hours (e.g. all resultswhere the time is between 11:00 to 14:00)

The only way I can think of is a div with options to select the desired period of time any a button which says “APPLY”

It will lead to a case statement that will execute a query, and I will need a separate function for each option.

Am I right?

Is there a shorter way?

[rpkamp]

The principle sounds correct, yes. The queries are different enough from each other that it will be very hard to get into one query. Even if you succeed, the query will be quite unreadable.

Not sure I’d go for a case statement though. At first, sure, but if this gets big enough you may want to switch to the specification pattern.

[skycar87]

If the only part of the query that is changing is the where clause, then you could use the case statement to specify the condition. This would then be added to the rest of the query string and executed.