CodeIgniter Forums
Dates Form for Reporting - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Dates Form for Reporting (/thread-18993.html)



Dates Form for Reporting - El Forum - 05-24-2009

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


Dates Form for Reporting - El Forum - 05-24-2009

[eluser]mdcode[/eluser]
Never mind guys. This has been solved.