How Can I Emulate Where 1 In A Dynamic Query?

I'm making a query dynamically like this: $query = 'SELECT u.* FROM users u WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 $range))'; Also $ran

Solution 1:

Perhaps use a case statement in your where clause?

$query = "SELECT u.*
           FROM users u
           WHERE CASE {$range}
                 WHEN 'ALL' then TRUE
                 ELSE date_time > unix_timestamp(DATE_SUB(now()
                                  , INTERVAL 1 {$range}))
                 END";

Solution 2:

switch ($_GET['range']){
  case"week":
  $range = "WEEK";
 case"month":
  $range = "MONTH";
 case"year":
  $range = "YEAR";
 case"ALL":
  $range = null;
}

$query = "SELECT u.*
      FROM users u
      WHERE 1=1 ";
if(!empty($range)) {
  $query .= " AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 $range))";
}

Add datetime where clause conditionally; WHERE 1=1 will always be true. So the next condition's result will matter on the final result

Solution 3:

I think what Adam was suggesting is something like this:

$where = '';

switch ($_GET['range']) {

    case"week":
       $where = "date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))":
        break;

    case"month":
       $where = "date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 MONTH))":
        break;

    case"year":
       $where = "date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))":
        break;

    default:
        $where = '';
}

$query = "SELECT u.* FROM users u WHERE $where";

Assume reporting with other criteria? Where the string concatenation gets messy. Adding 'AND' between parts. Suppressing WHERE keyword entirely? Where OOP code to represent the SQL query can be useful.

Solution 4:

Let's start with the fact that your switch statement doesn't have break statements and isn't going to work the way you expect.

Here is what I'd suggest:

$query = "SELECT u.*
          FROM users u";

$range = false;

if (isset($_GET['range'])) {    
    switch ($_GET['range']){
       case"week":
          $range = "WEEK";
          break;
       case"month":
          $range = "MONTH";
          break;
       case"year":
          $range = "YEAR";
          break;
       default:
          $range = false;
    }
} 


if ($range) {
  $query .= " WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 $range))";
}

Solution 5:

I would define an array with valid ranges and validate the user input first. Then build the query dynamicly.

$validRanges = [
    'year'  => 'YEAR',
    'month' => 'MONTH',
    'week'  => 'WEEK',
    'ALL'   => null
];

if (isset($_GET['range']) && !isset($validRanges[$_GET['range']])) {
    $range = $validRanges[$_GET['range']]);
} else {
    // it's up to you, what to do in this casethrownewException('Range is not valid or missing');
}

if ($range === null) {
    $rangeCondition = '1=1';
} else {
    $rangeCondition = "date_time > unix_timestamp(NOW() - INTERVAL 1 $range)";
}

$query = "SELECT u.*
          FROM users u
          WHERE $rangeCondition";