How to get data from my database using two datepickers. CODEIGNITER

Question

I want to select all data from my database between my two datepickers. Kindly help me on how to use BETWEEN AND statements in this situation. I pick a date from the first datepicker and then the data shows, but when i pick a date on 2nd date picker the data of the date from the 2nd date picker is only shown the first one is gone

i used this query

$this->db->where('date_sold >=',$this->input->post('selectdate'));

$this->db->where('date_sold <=',$this->input->post('selectdate2'));

but i only get 1 data..

<span>Select 1st sem:</span>
                <div id="datetimepicker1" class="input-append date">
                    <input data-format="MM/dd/yyyy" type="text" readonly name="selectDate" id="selectDate"></input>                                 
                    <span class="add-on">
                      <i data-time-icon="icon-time" data-date-icon="icon-calendar">
                      </i>                                                        
                    </span>
                </div>
                <script type="text/javascript">

                 $(function(){ 
                    var date = new Date();
                    date.setDate(date.getDate());

                      $('#datetimepicker1').datetimepicker({                            
                            pickTime: false
                      });

                  });
                </script>   

                <span>Select 2st sem:</span>
                <div id="datetimepicker2" class="input-append date">
                    <input data-format="MM/dd/yyyy" type="text" readonly name="selectDate" id="selectDate2"></input>                                    
                    <span class="add-on">
                      <i data-time-icon="icon-time" data-date-icon="icon-calendar">
                      </i>                                                        
                    </span>
                </div>

                <script type="text/javascript">

                 $(function(){ 
                    var date = new Date();
                    date.setDate(date.getDate());
                      $('#datetimepicker2').datetimepicker({                            
                            pickTime: false
                      });

                  });
                </script>

/this is my view

    function get_semreportdata(){
    $q = $this->db->select('
             books.title,
             reserved_books.id,
             reserved_books.isbn, 
             reserved_books.price,
             reserved_books.item_sold,
             reserved_books.date_sold,
             reserved_books.total_amount
            ')
        ->from('reserved_books')
        ->join('books','reserved_books.isbn= books.isbn')
        ->where('date_sold >=',$this->input->post('selectdate'))
        ->where('date_sold <=',$this->input->post('selectdate2'));



        $reserved['rows'] = $q->get()->result();
        $this->db->select_sum('total_amount');
        $this->db->where('date_sold',$this->input->post('selectdate'));

        $query = $this->db->get('reserved_books');
        $reserved['total'] = $query->result();

        return $reserved;

}

this is my model

Steve · Accepted Answer · 2013-10-24 13:09:14Z

1

Both of your fields are named selectDate in your code sample. I think this is why your original query isn't working. Try renaming the second field to selectdate2

answered Oct 24, 2013 at 13:09

Steve

363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jelle Ferwerda · Accepted Answer · 2013-10-15 12:30:52Z

0

I'd say something like this:

where 'date_sold' between ".$this->input->post('selectdate')." and ".$this->input->post('selectdate')."

Assuming you have the submitted dates in the right format for your database table.

answered Oct 15, 2013 at 12:30

Jelle Ferwerda

1,2291 gold badge7 silver badges13 bronze badges

1 Comment

Ezekiel Blanco Over a year ago

$this->db->where('date_sold between',$this->input->post('selectdate')." and ".$this->input->post('selectdate2')); i used this but still the selected date of the 2nd date picker is shown

M Khalid Junaid · Accepted Answer · 2013-10-15 13:32:58Z

Try this one make sure you have appropriate type of date_sold like date or datetime and also care for posted inputs they contains the exact date format as date_sold column has.I have make three cases one for between and other two contains the single date data. I have used custom string in where by passing third parameter as false

    $datef=$this->input->post('selectdate');
    $dates=$this->input->post('selectdate2');
    $q = $this->db->select('
             books.title,
             reserved_books.id,
             reserved_books.isbn, 
             reserved_books.price,
             reserved_books.item_sold,
             reserved_books.date_sold,
             reserved_books.total_amount
            ')
        ->from('reserved_books')
        ->join('books','reserved_books.isbn= books.isbn');

if(!empty($datef) && !empty($dates)){
$this->db->where(" `date_sold` BETWEEN '".$datef."' AND '".$dates."' ", NULL, FALSE); 

}elseif(!empty($datef)){
$this->db->where( " `date_sold` >=.'"$datef."' ", NULL, FALSE);

}elseif(!empty($dates)){

$this->db->where(" `date_sold` <=.'".$dates."' ", NULL, FALSE);
}

Reference

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

Active Record

Collectives™ on Stack Overflow

How to get data from my database using two datepickers. CODEIGNITER

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related