php bind_param for limit

Question

I have problem with binding values to mysql query in php.

$this->conn->prepare("SELECT * FROM tablename LIMIT ? , ? ");
$pageStart = 11; 
$pageEnd = 20 ;
$stmt->bind_param("ii" , $pageStart , $pageEnd );
$stmt->execute();

This is returning 20 rows. Any help?

How many rows are you expecting? Please edit your question with more details! — Tobbe, Aug 27 '15 at 07:10
You get 20 tows because you tell mysql to get 20 rows after the 11th row — Charlotte Dunois, Aug 27 '15 at 07:12

score 3 · Accepted Answer · answered Aug 27 '15 at 07:11

3

This is correct behaviour.

From the documentation:

...
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
...

the number after the comma is rowcount.

If you want 10 records, just tell that:

SELECT * FROM tablename LIMIT 11 , 10;

answered Aug 27 '15 at 07:11

Bart Friederichs

33,050
15
95
195

Oh thanks I was forgot the parameters. Thank you. Saved my time. – manitaz Aug 27 '15 at 07:14
@manitaz next time you run into something like this, check the documentation first before asking a question. – Bart Friederichs Aug 27 '15 at 07:15
for binding the parameters instead of hard-coding them, see also this "trap" https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause – cuda12 Jul 24 '19 at 16:20

Manwal · Answer 2 · 2015-08-27T07:14:53.377

2

Query is executing correctly. Your parameters having incorrect values as per expected output.

If you want records from 11-20 query should be:

SELECT * FROM tablename LIMIT 11 , 10

as you are passing 20 as second parameter its returning 20 rows

You can use LIMIT offset, row_ count or LIMIT row_count OFFSET offset

See documentation http://dev.mysql.com/doc/refman/5.0/en/select.html

edited Aug 27 '15 at 07:14

answered Aug 27 '15 at 07:11

Manwal

23,450
12
63
93

I need to change values dynamically. – manitaz Aug 27 '15 at 07:13
Yes query is executing perfectly. @manitaz you need to change values dynamically. – Manwal Aug 27 '15 at 07:13

score 0 · Answer 3 · answered Aug 27 '15 at 07:12

0

$pageStart = 11; Means the result start for 11 and $pageEnd = 20 ; means 20 result after 11

So you get 20 result now.

To get result up to 10 you need to set $pageEnd = 10. It will return you result 11 to 21

answered Aug 27 '15 at 07:12

Saty

22,443
7
33
51

score 0 · Answer 4 · answered Aug 27 '15 at 07:14

Regarding the names of your variables you should change

$stmt->bind_param("ii" , $pageStart , $pageEnd );

to

$stmt->bind_param("ii" , $pageStart , $pageEnd - $pageStart + 1);

because the second parameter of LIMIT is the amount of rows to be selected. The first parameter is the offset.

php bind_param for limit

4 Answers4