'using BETWEEN in WHERE condition

I'd like the following function to select hotels with an accomodation between a certain $minvalue and $maxvalue. What would be the best way to do that?

function gethotels($state_id,$city,$accommodation,$minvalue,$maxvalue,$limit,$pgoffset)
    {
        $this->db->limit($limit, $pgoffset);
        $this->db->order_by("id", "desc");
        $this->db->where('state_id',$state_id);
        $this->db->where('city',$city);

        // This one should become a between selector
        $this->db->where($accommodation,$minvalue); 

        $result_hotels = $this->db->get('hotels');
        return $result_hotels->result();

   }
phpcodeigniter


Solution 1:[1]

You should use

$this->db->where('$accommodation >=', minvalue);
$this->db->where('$accommodation <=', maxvalue);

I'm not sure of syntax, so I beg your pardon if it's not correct.
Anyway BETWEEN is implemented using >=min && <=max.
This is the meaning of my example.

EDITED:
Looking at this link I think you could write:

$this->db->where("$accommodation BETWEEN $minvalue AND $maxvalue");

Solution 2:[2]

In Codeigniter This is simple Way to check between two date records ... 

$start_date='2016-01-01';
$end_date='2016-01-31';

$this->db->where('date BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

Solution 3:[3]

Sounds correct but some issues maybe creates executing this query: I would suggest: 

$this->db->where( "$accommodation BETWEEN $minvalue AND $maxvalue", NULL, FALSE );

Solution 4:[4]

You might also encounter an error message. "Operand type clash: date is incompatible with int.

Use single quotes around the dates. E.g.: $this->db->where("$accommodation BETWEEN '$minvalue' AND '$maxvalue'");

Solution 5:[5]

I think we can write like this : $this->db->where('accommodation >=', minvalue); $this->db->where('accommodation <=', maxvalue);

//without dollar($) sign It's work for me :)

Solution 6:[6]

Check the following code. Its works for me

$dateStart = $this->input->post('dateStart);
$dateTo= $this->input->post('dateTo);
$fromDate=date('Y-m-d',strtotime($dateStart));
$toDate=date('Y-m-d',strtotime($dateTo));
    
$this->db->from('tbl_sales sl');
$this->db->where('DATE(sl.created) >=',$setDate);
$this->db->where('DATE(sl.created) <=',$dateend);
$this->db->get();

Solution 7:[7]

// Codeigniter  3.1.11  

  public function getContraMarking($from_date2='',$to_date2='',$acctno2='')
    {
        if($from_date2!='' && $to_date2!='' && $acctno2!='')
        {
            $this->db->select('account_no,txn_date,SUM(pd_amt) AS debit,SUM(cr_amt) AS credit,COUNT(value_date) AS total_count');
            $this->db->from('ut_sbi_reco_paid_master');
            $this->db->where('account_no', $acctno2);
            $this->db->where('txn_date BETWEEN "'. $from_date2. '" AND "'. $to_date2. '" ');
            $this->db->group_by('account_no,txn_date,pd_amt');
            $this->db->order_by("txn_date");

            $query = $this->db->get();
            
            if($query->num_rows() > 0){
                return $query->row();
            }else{  
                return FALSE;
            }
        }
    }

Solution 8:[8]

$this->db->where('accommodation BETWEEN '' . $sdate . '' AND '' . $edate . ''');

this is my solution

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Muhammad Fahad
Solution 3 syyu
Solution 4 user3593238
Solution 5 Dek Sudiana
Solution 6 himansu malla
Solution 7 Sonu Chohan
Solution 8 G12007

Related Questions