using BETWEEN in WHERE condition

Question

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

   }

Geralyngeraniaceous

is this in kohana, codeigniter or someOtherPHPFramework – Compositor

You tagged your question mysql, but your code is not mysql... – Anatomist

this has nothing to do with mysql, and you didn't wrote what framework you use. You could use, moreOrEqual minVal AND lessOrEqual maxVal instead of between – Paige

@om-nom-nom: that's PHP, ok, but he's using a private class or an extension: php mysql extension does not have those methods... – Anatomist

I guess they want to use the BETWEEN operator but have no idea how to use it with the database abstraction they use (which seems to be building the query behind the scenes). – Admire

Answer 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.

Looking at this link I think you could write:

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

Anatomist

@Anatomist $this->db->where("$accommodation BETWEEN $minvalue AND $maxvalue", null, false); Is the code required. – Massicot

Answer 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)).'"');

Pitt

Answer 3

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

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

Carlettacarley

This type usage genarates SQL Injection Security Issue – Yvor

Answer 4

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

Colombi

Please provide additional details in your answer. As it's currently written, it's hard to understand your solution. – Sharpeared

Answer 5

// 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;
            }
        }
    }

Amplexicaul

A code-only answer is not high quality. While this code may be useful, you can improve it by saying why it works, how it works, when it should be used, and what its limitations are. Please edit your answer to include explanation and link to relevant documentation. – Laughing

Answer 6

You can package all of your where conditions as associative elements in the second parameter of a get_where() method call.

The greater-than-or-equal-to and less-than-or-equal-to operators merely need to be appended to key string as demonstrated below.

I do not endorse conditionally returning false when an object is also possible. PHP has long-supported nullable types and specifically offers null coalescing operations. Because row() returns an object or null, I recommend that all model methods which return row() data should either return null or throw an exception when something "not good" happens while processing.

public function getContraMarking(
    string $from_date2 = '',
    string $to_date2 = '',
    string $acctno2 = ''
): ?object {
    if (!$from_date2 || !$to_date2 || !$acctno2) {
        return null;
    }
    return $this->db
        ->select([
           'account_no',
           'txn_date',
           'SUM(pd_amt) debit',
           'SUM(cr_amt) credit',
           'COUNT(value_date) total_count'
        ])
        ->group_by('account_no, txn_date, pd_amt')
        ->order_by('txn_date')
        ->get_where('ut_sbi_reco_paid_master', [
               'account_no' => $acctno2,
               'txn_date >=' => $from_date2,
               'txn_date <=' => $to_date2
        ])
        ->result();
    }
}

get_where() is not suitable if you want to use column names on both sides of the operator. In this case, you'll need to use separate where() methods with false as the escape parameter.

->where('col_one >=', 'col_two', false)
->where('col_one <=', 'col_two', false)
->get('ut_sbi_reco_paid_master')

Kerrill

Answer 7

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'");

Melise

This will be potentially insecure/unstable where user input is not sanitized. – Kerrill

Answer 8

I think we can write like this:

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

without dollar($) sign

Nymph