Hi there,

I have a drop down list that holds the following options:
"today", "this week", "this month", "this year".

Is it possible to get records from a mysql database based on what the user chooses from the above drop down using only one query?

I know that using something like that:

SELECT * FROM table WHERE (month(DATEFIELD) = month(now()) and dayofmonth(DATEFIELD) = (dayofmonth(now())) and year(DATEFIELD)=year(now()));

I can retrieve today's records. So, one solution would be to create multiple queries and based on user input, execute the associated query. But given the fact that my form has various search criteria, i will end up with to many queries.

Any help would be very much appreciated.
Thanks.

you have to build the query "on the fly" depending on what the user chooses

Thank you for the reply,

can you give me an example?

probably, and there will be plenty of examples around in the forum for whatever serverside code you happen to be using.

I am using PHP.
Can i create a query on the fly using the IN clause;

I appreicate your help.

PHP Code:

switch ($_POST['select_date']) {
  
  case 1: // This year
    $fromDate = mktime(0, 0, 0, 1, 1, date('Y'));
    break;
    
  case 2: // This month
    $fromDate = mktime(0, 0, 0, date('n'), 1, date('Y'));
    break;
    
  case 3: // This week
    $fromDate = time() - ( 60*60*24*(date('w'))  );
    break;
    
  default: // Today
    $fromDate = time();
    break;
}
$sql = 'SELECT * FROM table WHERE datefield BETWEEN "' . date('Y-m-d', $fromDate) . '" AND NOW()';