Passing OFFSET value as a string into a CodeIgniter SELECT query emits a syntax error

Question

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 ''3'' at line 1

SELECT * FROM tbl_testimoni order by id DESC limit 3 offset '3'

Filename: D:\wamp\www\obatrohanifinalcopy\system\database\DB_driver.php

Line Number: 330

I'm having problem with my query syntax. I think the problem is in limit and offset query. Because I have tried without limit and offset and my script can work properly. But the pagination function doesn’t work according to my wish.

This is my controller:

$url = $this->uri->segment(3, 0);
$this->load->library('pagination');
$config['base_url'] = site_url() . '/testimonial/all/';
$config['total_rows'] = $this->db->get('tbl_testimoni')->num_rows();
$config['per_page'] = 3;
$this->pagination->initialize($config);
$data['page']=$this->pagination->create_links();
        
$sql = " SELECT * FROM tbl_testimoni order by id DESC limit 3 offset ? ";
$binds = array($url);
$query = $this->db->query($sql, $binds);
$artikel = $query->result_array();
$data['action'] = 'testimonial/all';
$data['artikel'] = $artikel;
$data['content'] = 'content/testimoni';
$this->load->view('template/default', $data);

and in my view the pagination was called using this code:

<?php if(!empty($page)) echo $page; ?>

and my code doesn’t work properly.

Note: I was using this controller and view in my previous website but there I was using PostgreSQL and it worked.

irs8925 · Accepted Answer · 2014-02-11 01:54:38Z

1

Try typecasting the $binds variable as in $this->db->query($sql, intval($binds));

Also, by the documentation $this->uri->segment() may return boolean, so you might want to handle that as well.

answered Feb 11, 2014 at 1:54

irs8925

1861 silver badge6 bronze badges

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

1 Comment

ranggatry Over a year ago

still same like before sir. the result on next pagination still same like first page. not appears the next data

Minhaz · Accepted Answer · 2014-02-11 00:34:44Z

0

The offset should be a number not string '3'

when you pass the $binds in $this->db->query($sql, $binds); the DB Driver think its a value and replace your ? with '3' where it should be OFFSET 3

answered Feb 11, 2014 at 0:34

Minhaz

4565 silver badges7 bronze badges

2 Comments

ranggatry Over a year ago

results are not performing well. just called multiple databases and when I click on the pagination. the result is the same as the start page appears

Minhaz Over a year ago

If its give always same result then the problem could be in the url segment, can you check what value you are getting from your uri segment also you can echo $this->db->last_query() to see the last query what exactly its executing.

Collectives™ on Stack Overflow

Passing OFFSET value as a string into a CodeIgniter SELECT query emits a syntax error

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related