MySQL query: variable value for SQL does not work

Question

I make some functions for retrieving data.

Query results must depend on an input value. The inputs value is in the variable $start & $end.

But my code returns all data not based on the input value.

It's means the variable $start & $end does not have value (reader null).

function tryme($start, $end) {

      $query = $this->db->query("
      SELECT b.liId, r.status
      FROM raws r
      LEFT JOIN books b ON r.rawsId = b.booksId
      GROUP BY b.booksId 
      HAVING 
       SUM((b.start < '$start' AND b.done < '$start') 
                   OR 
      (b.start > '$end' AND b.done > '$end')) = COUNT(*)
      ");

      return $query->result_array();
   }

Then I change the code with fix value, and the result is as expected.

   HAVING 
       SUM((b.start < '18-04-2022' AND b.done < '18-04-2022') 
                   OR 
      (b.start > '19-04-2022' AND b.done > '19-04-2022')) = COUNT(*)
      ");

So what was the mistake, how to use the variable?

Answer 1

Your variables $start and $end are not being expanded in your query string. Assign your query string to variable and print it out to confirm. That said, use bind variables instead of interpolating strings:

      $query = $this->db->query("
          SELECT b.liId, r.status
          FROM raws r
          LEFT JOIN books b ON r.rawsId = b.booksId
          GROUP BY b.booksId 
          HAVING SUM((b.start < ? AND b.done < ?) OR
              (b.start > ? AND b.done > ?)) = COUNT(*)
      ", [$start, $start, $end, $end]);