1

Currently I'm using CodeIgniters MVC framework to fetch my records between a particular time period. I have done the same filtering for a dropdown and achieved the result, but I cant figure out how to do the same with a date picker. The following is my View model:

<html lang="en"> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> </head> <body> <div id="statusModal"> <input type="date" name="startDate" class="startDate" id="startDate"/> <input type="date" name="endDate" class="endDate" id="endDate"/> <select name="status" class="status" id="status"> <option>Draft</option> <option>Unpublish</option> <option>Publish</option> </select> <br/><br/> <button type="button" class="alertbox" id="alertbox" name="alertbox">alertbox</button> <br/><br/> <div id="result"></div> </div> </form> </body> </html> <script> $(document).ready(function(){ load_status(); $('#alertbox').click(function(){ var startDate = $('#startDate').val(); var endDate = $('#endDate').val(); if(startDate != '' && endDate != ''){ $.ajax({ url:"<?php echo base_url(); ?>testcontroller/fetch_dat", method:"POST", data:{startDate:startDate, endDate:endDate}, success:function(data){ $('#result').html(data) } }) }else{ alert("Please enter a date"); } }) function load_status(status){ $.ajax({ url:"<?php echo base_url(); ?>testcontroller/fetch_stat", method:"POST", data:{status:status}, success:function(data){ $('#result').html(data) } }) } $('#status').click(function(){ var search = $(this).val(); if(search != ''){ load_status(search); } else{ load_status(); } }) }) </script>

Controller Class:

class Testcontroller extends CI_Controller { public function index() { $this->load->view('crm/test'); } function fetch_dat(){ $output =''; $startDate = ''; $endDate = ''; $this->load->model('crm/user_model'); if($this->input->post('startDate')){ $startDate = $this->input->post('startDate'); } if($this->input->post('endDate')){ $endDate = $this->input->post('endDate'); } $data = $this->user_model->fetch_date($startDate,$endDate); $output .= ' <div class="table-responsive"> <table class="table table-bordered table-striped"> <tr> <th>Customer id</th> <th>Title</th> <th>Added Date</th> <th>Status</th> </tr> '; if($data->num_rows() > 0) { foreach($data->result() as $row) { $output .= ' <tr> <td>'.$row->id.'</td> <td>'.$row->title.'</td> <td>'.$row->added_date.'</td> <td>'.$row->status.'</td> </tr> '; } } else { $output .= '<tr> <td colspan="5">No Data Found</td> </tr>'; } $output .= '</table>'; echo $output; } function fetch_stat(){ $output =''; $status = ''; $this->load->model('crm/user_model'); if($this->input->post('status')){ $status = $this->input->post('status'); } $data = $this->user_model->fetch_status($status); $output .= ' <div class="table-responsive"> <table class="table table-bordered table-striped"> <tr> <th>Customer id</th> <th>Title</th> <th>Added Date</th> <th>Status</th> </tr> '; if($data->num_rows() > 0) { foreach($data->result() as $row) { $output .= ' <tr> <td>'.$row->id.'</td> <td>'.$row->title.'</td> <td>'.$row->added_date.'</td> <td>'.$row->status.'</td> </tr> '; } } else { $output .= '<tr> <td colspan="5">No Data Found</td> </tr>'; } $output .= '</table>'; echo $output; } }

And my model class:

function fetch_status($status){ $this->db->select(*); $this->db->from(crm_listings); if($status != ''){ $this->db->where('status',$status); } $this->db->order_by('id','DESC'); return $this->db->get(); } function fetch_date($startDate,$endDate){ $this->db->select(*); $this->db->from(crm_listings); if($startDate != '' && $endDate != ''){ $this->db->where('added_date BETWEEN "' . $startDate . '" AND "' . $endDate . '"',$startDate,$endDate); } $this->db->order_by('id','DESC'); return $this->db->get(); }
Improve this question
7
  • what is data type of added_date in database table? Commented Oct 5, 2021 at 8:29
  • @umefarooq it is datetime Commented Oct 5, 2021 at 8:33
  • can you tell me $startDate and $endDate format is it same datetime or just date? Commented Oct 5, 2021 at 8:50
  • @umefarooq how can I check that? both these variables are the ones that I'm picking from my input tag and they both have to refer the added_date column in my database Commented Oct 5, 2021 at 8:53
  • in your controller fetch simply echo $startDate and endDate and die(); you will able to see date format Commented Oct 5, 2021 at 9:06

1 Answer 1

Reset to default
1

Hi after all comments on above post as you are getting $startDate and $endDate in Y-m-d format here is my answer you need to cast added_date to just date in where query here is how you can do it.

 function fetch_date($startDate,$endDate){ $this->db->select(*); $this->db->from(crm_listings); if($startDate != '' && $endDate != ''){ $this->db->where('cast(added_date as date) BETWEEN "' . $startDate . '" AND "' . $endDate . '"',null,false); } $this->db->order_by('id','DESC'); return $this->db->get(); }

it will work for you.

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

9 Comments

Hey sorry to bother you, but I added a count(*) in my statement to get the number of rows for the data but I cant get the number to display. I think it has something to do with my controller class, but I dont know what to change
can you share your select?
It's your same code just changed $this->db->select("count(*)");
@umefarooq sir please describe about cast()
@Jay Vijay Modi what is your next task?
|