Fetch records between 0 and 100 without using between in mysql

Question

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

The `HAVING` token is for aggregated results only. Do you aggregate? What framework do you use, doesn't it do real mysql statements? — Daniel W., Feb 13 '18 at 13:27
is $this->db your db driver, or a adapter class or a framework? This is really guessing — Nebulosar, Feb 13 '18 at 13:27
try `$this->db->having('(distance>0 and distance<=100) or (distance>=0 and distance<100)')` — cdaiga, Feb 13 '18 at 13:41

score 0 · Answer 1 · answered Feb 13 '18 at 15:58

0

how about

$this->db
    ->group_start()
        ->where('distance <=', 100)
        ->where('distance >=', 0)
    ->group_end();

its perfectly fine if you remove the group statements in case you don't need it

answered Feb 13 '18 at 15:58

Atural

5,389
5
18
35

score 0 · Answer 2 · answered Feb 14 '18 at 05:19

0

use

$this->db->having(array('distance >=' => 0, 'distance <=' => 100));
// Produces: HAVING distance => 0, distance <= 100

answered Feb 14 '18 at 05:19

Abdulla Nilam

36,589
17
64
85

score 0 · Accepted Answer · answered Feb 14 '18 at 06:31

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();

Fetch records between 0 and 100 without using between in mysql

3 Answers3