Welcome Guest, Not a member yet? Register   Sign In
A Database Error Occurred Error Number: 2014 Commands out of sync; you can't run th
#1

I am trying to run a query to find the total number of rows for my query for pagination and then another query for paging. I get the following error

A Database Error Occurred
Error Number: 2014
Commands out of sync; you can't run this command now
Call employee_list(5)
Filename: models/Db_model.php
Line Number: 6




Code:
$this->load->model('Db_model');
            
            $query = $this->Db_model->read_data('CALL sp_employee_list_limit('.$this->input->post('dept_id').', '.$this->uri->segment(3,0).', 20)');
            
            if($query != "") {
                // Creating the pagination menu on the webpage
                $this->load->library('pagination');
                $config['base_url'] = 'http://192.168.0.10/~greyhorseoutpost/index.php/employee_list/display_result';
                $config['total_rows'] = $this->Db_model->read_data('Call employee_list('.$this->input->post('dept_id').')');
                $config['per_page'] = 20;                
                $this->pagination->initialize($config);
                $data['paginate'] = $this->pagination->create_links();
            
                // building the table to diplay on the webpage
                $this->load->library('table');
                $table_template = array('table_open'=>'<table border="1" cellpadding="2" cellspacing="1" class="pure-table">',
                        'row_alt_start' => '<tr class="pure-table-odd">'
                );
                $this->table->set_template($table_template);
                // This query displays on the web page paginates the records in groups of 20
                echo 'CALL sp_employee_list_limit('.$this->input->post('dept_id').', '.$this->uri->segment(3,0).', 20)';
                    
                $data['result'] = $this->table->generate($query2);
                
                
                
                
                $this->load->view('employee_list', $data);
            }

The Db_model function is just returning a simple $this->db->query(query);

If tried just writing out the queries and still get the same error. Earlier versions I could do this with no problem?

Any ideas?
Reply
#2

You need:

$query->next_result();
$query->free_result();

Because stored procedure.
Reply
#3

(11-08-2015, 06:10 PM)skunkbad Wrote: You need:

$query->next_result();
$query->free_result();

Because stored procedure.

free_result() for some reason didn't work?????


BUT!!! I did find a work around

Code:
$this->load->model('Db_model');

//Used this code to get the total number of rows            
$this->db->where('department_id', $dept_id);
$this->db->from('employees');
$numCount = $this->db->count_all_results();

$total = $numCount;

// Creating the pagnation menu on the webage
$this->load->library('pagination');
$config['base_url'] = 'http://192.168.0.10/~greyhorseoutpost/index.php/employee_list/display_result/'.$dept_id;
$config['total_rows'] = $total;
$config['per_page'] = 20;
$this->pagination->initialize($config);
$data['paginate'] = $this->pagination->create_links();
            
            
$query = $this->db->query('CALL sp_employee_list_limit('.$dept_id.', '.$this->uri->segment(4,0).', 20)');
//print_r($query);
                    
// building the table to diplay on the webpage
$this->load->library('table');
$table_template = array('table_open'=>'<table border="1" cellpadding="2" cellspacing="1" class="pure-table">',
    'row_alt_start' => '<tr class="pure-table-odd">'
);
$this->table->set_template($table_template);
// This query displays on the web page paginates the records in groups of 20
            
$data['result'] = $this->table->generate($query);
Reply




Theme © iAndrew 2016 - Forum software by © MyBB