CODEWITHSUNDEEP
sqlselectpaginationlimitcodeigniter-3
kev_m
kev_m

Reputation: 325

MySQL limit and offset error

I am making this pagination and I have this error on my query 

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0 , 10' at line 9

SELECT job_availability.JOB_CODE, job_titles.JOB_NAME, job_availability.AVAILABILITY FROM job_titles LEFT JOIN job_availability ON job_availability.JOB_CODE = job_titles.JOB_CODE ORDER BY job_titles.JOB_NAME, LIMIT 0 , 10

I am trying to do this 

function get_department_list($limit, $start)
{
    $sql = 'select var_dept_name, var_emp_name from tbl_dept, tbl_emp where tbl_dept.int_hod = tbl_emp.int_id order by var_dept_name limit ' . $start . ', ' . $limit;
    $query = $this->db->query($sql);
    return $query->result();
}

my code is 

public function get_list($limit , $start){

    $sql = ('SELECT 
                job_availability.JOB_CODE, 
                job_titles.JOB_NAME, 
                job_availability.AVAILABILITY
            FROM job_titles
            LEFT JOIN job_availability
            ON job_availability.JOB_CODE = job_titles.JOB_CODE
            ORDER BY job_titles.JOB_NAME,
            LIMIT ? , ?');
    $data = array($start, $limit);
    $query =  $this->db->query($sql, $data);
}

I don't think that I have some problem with my code, so I'm not sure what the error is. My MySQL version is 5.6.24.

Upvotes: 2

Views: 1500

Answers (1)

Mureinik
Mureinik

Reputation: 311053

You have a redundant comma (,) at the end of your order by clause before the limit clause. Just remove it, and you should be fine:

SELECT 
    job_availability.JOB_CODE, 
    job_titles.JOB_NAME, 
    job_availability.AVAILABILITY
FROM job_titles
LEFT JOIN job_availability
ON job_availability.JOB_CODE = job_titles.JOB_CODE
ORDER BY job_titles.JOB_NAME
-- Comma removed here ------^
LIMIT ? , ?

Upvotes: 3

Related Questions