How can I emulate WHERE 1 in a dynamic query?

1
votes

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 $range contains a word like this:

switch ($_GET['range']){
   case "week":
      $range = "WEEK";
   case "month":
      $range = "MONTH";
   case "year":
      $range = "YEAR";
   case "ALL":
      $range = <I don't know what should I put here to get the expected result>;
}

As I've said above, I want to set something as $range value to make the WHERE clause something like WHERE 1. How can I do that?

5
phpmysqlsqldynamicquery
How about put the whole part of the range part in the switch and not put it in in there if it isn't needed? – Adam Forbis
@AdamForbis I use $range somewhere else too. I cannot do what you've suggested. – stack
^ agreed. Also a good scenario for having some framework/ORM support. You could then maybe have an API for manipulating a SQL statement. e.g. $query->addWhere()... Otherwise the string concatenation can get messy fast. – ficuscr

5 Answers

1
votes

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.

3
votes

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";
2
votes
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

0
votes

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))";
}
0
votes

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 case
    throw new Exception('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";