[eluser]mdcode[/eluser]
I think that I'm going around in circles with this and so tying myself in a knot - I've made so many changes so far that I can't count and I've only go a little way forward. Basically what I want is a form with two boxes, allowing the user to specify a date to run the report (this part is fine, so I won't go further with that). When the form is submitted, I want the page to take those values, run the query with a BETWEEN clause to bring up the list of records. Whatever I do, I am unable to get the query to substitute the variables for the values. I hope the code will explain better:
CONTROLLER:
Code:
function bydatequery()
{
if (is_numeric($this->uri->segment(3)))
{
$date_range = $this->uri->segment(3);
}
else
{
$date_range = FALSE;
}
/* load pagination class */
$this->load->library('pagination');
$config['base_url'] = base_url().'index.php/reports/bydate/'.$date_range;
$config['total_rows'] = $this->reports_model->get_num_rows_date_range($date_range);
$config['per_page'] = '10';
$config['full_tag_open'] = '';
$config['full_tag_close'] = '';
$config['uri_segment'] = '4';
$this->pagination->initialize($config);
$data['site_title'] = $this->config->item('site_title');
$data['title'] = 'View projects by date - results';
$data['query'] = $this->reports_model->get_date_range();
$data['report'] = $this->reports_model->get_projects_by_date_range($date_range,$config['per_page'],$this->uri->segment(4));
$this->template->write_view('content', 'default/pages/reports/projects_by_date_range', $data);
$this->template->render();
}
MODEL:
Code:
/* get dates, and the number of rows for the listing */
function get_date_range()
{
$info = array(
'date_from'=>$this->input->post('date_from', TRUE),
'date_to'=>$this->input->post('date_to', TRUE),
);
return $info;
}
function get_num_rows_date_range($date_from = '', $date_to = '')
{
$query = "SELECT * FROM lip_projects WHERE date_complete BETWEEN '$date_from' AND '$date_to'";
$result = $this->db->query($query);
return $result->num_rows;
}
function get_projects_by_date_range($date_complete, $num, $offset)
{
$this->db->select('projects.id, projects.customer, projects.date_complete, projects.job_status,
customers.id AS cust_id, customers.name');
$this->db->from('projects');
$this->db->join('customers', 'projects.customer = customers.id', 'left');
$this->db->where('projects.date_complete BETWEEN $date_from AND $date_to', NULL, FALSE);
$this->db->order_by('projects.id', 'DESC');
$this->db->limit($num, $offset);
return $this->db->get();
}
I have used the last_query function to exho the query being used, which works if I manually give it values in phpmyadmin, it's just not pulling the variables from the posted form. Any and all help is very much appreciated.
