Search code examples
mysqlcodeignitercodeigniter-3dbal

Fetch records between 0 and 100 without using between in mysql

I want to fetch records between 0 and 100 in mysql without using between clause, because when I use a query like this

$this->db->having('distance between 0 and 100');

I get an error like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near between0 and 100

You'll see the 0 does not get a space before it, so that the statement produces an error.

How to make sure a space remains before the 0 or else: how to exclude between in a query

Solution

  • Two simple ways to get this

    $this->db->select("*");
$this->db->from("table-name");
$this->db->where('distance >=', 0)
$this->db->where('distance <=', 100);
$query = $this->db->get();
return $query->result();

    OR

    $query = $this->db->query("SELECT * FROM table-name WHERE distance <= 100 AND distance >= 0");
return $query->result(); //or $query->result_array();