Whats wrong with my PDO mysql query

Question

Here is my code

$sql3= "select * 
        from comments 
        where status=:status  
        limit=:limit 
        offset=:offset 
        order by time desc";
$query3= $pdo->prepare($sql3);
$query3->bindValue(":status",'n');
$query3->bindValue(":limit",$per_page);
$query3->bindValue(":offest",$offset);
$query3->execute();
$comments=$query3->fetchall();

Here comments is my table name status and time is two column in my table . Whenever I run this code , It shows a warning

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in E:\XAMPP\htdocs\parlament\user\logged_in_area.php

What does this mean ?

For starters, you're missing `AND` or `OR` keyword between your conditions — asprin, May 28 '14 at 07:13
what kind of query is that? 1.limit is not in the end. 2.what is offset? — Bojan Kovacevic, May 28 '14 at 07:16
Don't use limit=:limit change it to limit :limit and same as for offset.. — Jitendra Yadav, May 28 '14 at 07:16
You guys need to read the manual ~ http://dev.mysql.com/doc/refman/5.0/en/select.html — Phil, May 28 '14 at 07:17
aha,those are fields in db. Now i get it. So you are missing AND as asprin said, + limit is reserved word in SQL. — Bojan Kovacevic, May 28 '14 at 07:18
One more thing that seemingly hasn't been mentioned: your query uses a placeholder `:offset`, but you are binding a value to `:offest` instead. — DCoder, May 28 '14 at 07:20
Durn, conused the link. http://stackoverflow.com/questions/5874383/invalid-parameter-number-parameter-was-not-defined-inserting-data it have to be. Not a big deal but for sake of clarity — Your Common Sense, May 28 '14 at 07:34

zessx · Accepted Answer · 2014-05-28T07:29:50.277

2

The answer depends of what are limit and offset.

If they're columns names...

You can't use these reserved keywords for column names without backticks
You need to add a AND / OR operator between your lines

$sql3= "select * 
        from comments 
        where status=:status  
        and `limit`=:limit 
        and `offset`=:offset 
        order by time desc";
$query3= $pdo->prepare($sql3);
$query3->bindValue(":status", 'n');
$query3->bindValue(":limit", $per_page);
$query3->bindValue(":offest", $offset);
$query3->execute();
$comments=$query3->fetchall();

If they're keywords...

The syntax is LIMIT <n>, not LIMIT = <n> (same for OFFSET)
It better to specify their type with PDO::PARAM_INT (same for OFFSET)
ORDER BY must be added before LIMIT and OFFSET

$sql3= "select * 
        from comments 
        where status=:status 
        order by time desc 
        limit :limit 
        offset :offset";
$query3= $pdo->prepare($sql3);
$query3->bindValue(":status", 'n');
$query3->bindValue(":limit", (int)$per_page, PDO::PARAM_INT);
$query3->bindValue(":offset", (int)$offset, PDO::PARAM_INT);
$query3->execute();
$comments=$query3->fetchall();

edited May 28 '14 at 07:29

answered May 28 '14 at 07:23

zessx

68,042
28
135
158

if they are keywords, should not limit and offset go after ORDER BY? – Bojan Kovacevic May 28 '14 at 07:28
@BojanKovacevic You're right, I just realised it. Thanks ! – zessx May 28 '14 at 07:30
@zessx np, i know c/p problems :) – Bojan Kovacevic May 28 '14 at 07:31
@Phil when i put order by after limit in my queries i always get sql error. maybe it changed in some new versions :D – Bojan Kovacevic May 28 '14 at 07:32
@BojanKovacevic Just tested, you are right. `ORDER BY` **must** come before `LIMIT` – Phil May 28 '14 at 07:33
in face, in single query as i know LIMIT goes after all other parts (WHERE, ORDER BY,GROUP BY,HAVING ...) – Bojan Kovacevic May 28 '14 at 07:35
Your code works perfectly thanks – user3682521 May 28 '14 at 07:55

score 1 · Answer 2 · answered May 28 '14 at 07:29

Adding my answer because nobody has mentioned this specific part yet...

MySQL is very picky about the data type of LIMIT parameters. You pretty much need to use bindParam(':limit', $per_page, PDO::PARAM_INT). I assume the same for OFFSET.

So, in summary

// because E_WARNING level errors are insufficient
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM `comments` WHERE `status` = :status ORDER BY `time` DESC LIMIT :limit OFFSET :offset');
$stmt->bindValue(':status', 'n');
$stmt->bindParam(':limit', $per_page, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT); // spelt "offset"
$stmt->execute();
$comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

score 0 · Answer 3 · answered May 28 '14 at 07:22

0

Try this->

$sql3= "select * 
        from comments 
        where status = ?  
        limit ?
        offset ? 
        order by time desc";
$query3= $pdo->prepare($sql3);
$query3->execute(array('n',$per_page,$offset));
$comments=$query3->fetchall();

answered May 28 '14 at 07:22

Jitendra Yadav

896
1
6
14

score -1 · Answer 4 · answered May 28 '14 at 07:20

-1

Not sure about that but I think that PDO::bindValue / bindParam works with variable references. You cannot set a static value as parameter. Try to replace

$query3->bindValue(":status",'n');

by

$n_value = 'n';
$query3->bindValue(":status",$n_value);

You also forgot the "AND" Keyword between your conditions

answered May 28 '14 at 07:20

Steven

313
3
10

Though correct, this is not the full answer as the query will still fail – asprin May 28 '14 at 07:20
2

Actually, this is **not** correct. `bindValue` specifically works with non-references – Phil May 28 '14 at 07:21
@Phil Ahh..right...didn't notice the `bindValue` part – asprin May 28 '14 at 07:22

score -2 · Answer 5 · answered May 28 '14 at 07:15

-2

You're missing the AND or OR keyword in statement. Also, LIMIT is a reserved keyword, you'll need to backtick it or rename it to something else if you don't want to do that.

answered May 28 '14 at 07:15

MisterBla

2,355
1
18
29

I agree -- explain. He is right. The way OP has formatted this, limit is just another column in his table. – Mave May 28 '14 at 07:18
I'd say OP is using `LIMIT` as per standard use – Phil May 28 '14 at 07:18
1

I assumed that `limit` in this case was a column, since he also has an `offset` column. If the limit was at the very end, I wouldn't have put said what I did. – MisterBla May 28 '14 at 07:20
3

Check the manual. `LIMIT ... OFFSET` is completely valid – Phil May 28 '14 at 07:21
1

@Phil I'll be damned. You're right. – MisterBla May 28 '14 at 07:21

score -4 · Answer 6 · edited May 28 '14 at 07:18

-4

$sql3= "select * 
        from comments 
        where status = ?  
        limit= ?
        offset= ? 
        order by time desc";
$query3= $pdo->prepare($sql3);
$query3->bindValue(1,'n');
$query3->bindValue(2,$per_page);
$query3->bindValue(3,$offset);
$query3->execute();
$comments=$query3->fetchall();

edited May 28 '14 at 07:18

zessx

68,042
28
135
158

answered May 28 '14 at 07:17

user3460377

13
1
7

1

You're treating limit as just another column inside the table. whilst you probably meant that it's the `LIMIT` keyword meant to limit results. – Mave May 28 '14 at 07:19

Whats wrong with my PDO mysql query

6 Answers6

If they're columns names...

If they're keywords...