PDO issues Syntax error with LIMIT

Question

i have a sintax error with PDO

when i try a prepared query with limit like this :

LIMIT :myLimit'); i have an error in mysql :' (

what's wrong whith this?

when i just put the number it's ok, like this : Limit 2

<?php 
try{
    $repFooterNews = $bdd->prepare('SELECT * FROM newss ORDER BY id DESC LIMIT :myLimit'); 
    $myLimit = 2;
    $repFooterNews->execute(array('myLimit'=>$myLimit));
    $repFooterNews->setFetchMode(PDO::FETCH_OBJ);
        while ($newBas = $repFooterNews->fetch() ){
            echo "<div class=\"newsfoot\">";
            echo "<h2> $newBas->title</h2>";
            echo "<p>$newBas->short</p>";
            echo "</div>";
            }
    }catch (PDOException $e){
    echo "Error !: " . $e->getMessage() . "<br/>"; 

    }
?>

Looks like it could be [this issue](http://stackoverflow.com/questions/2269840/php-pdo-bindvalue-in-limit), try casting `$myLimit` to an integer. Did it come from user input before you hard coded it to 2 to test it? — bcmcfc, Oct 05 '14 at 09:23

fsperrle · Accepted Answer · 2014-10-05T09:32:09.083

1

From the PDO manual:

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

Try binding the limit parameter like this instead of in the execute function:

$repFooterNews->bindParam(':myLimit', $myLimit, PDO::PARAM_INT);
$repFooterNews->execute();

edited Oct 05 '14 at 09:32

answered Oct 05 '14 at 09:26

fsperrle

1,262
1
14
26

Don't work, Erreur !: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined :' ( – jmercier Oct 05 '14 at 09:30
Ty it works but imagine i have another value (string etc, can i put in array $repFooterNews->execute(array()); the values ? bind is just for limit or for number ? – jmercier Oct 05 '14 at 09:34
1

Whenever PDO requires your parameter to be anything else than String you have to bind it separately. – fsperrle Oct 05 '14 at 09:39
TY for your effort, help me a lot for understand this – jmercier Oct 05 '14 at 09:40
The typo was the whole problem. – EternalHour Oct 05 '14 at 09:43
last things i don't understand, why when i try this : WHERE id<=:myLimit'); $myLimit = 14; $repFooterNews->execute(array('myLimit'=>$myLimit )); i dont need to bind it, and it works; it's a number no ? – jmercier Oct 05 '14 at 09:50
1

@jmercier This is because your `:myLimit` in this case is treated as a string and `execute(array(...everything here is treated as a string...))` – Yang Oct 05 '14 at 10:20

score 0 · Answer 2 · answered Oct 05 '14 at 09:29

0

I believe the issue is because you have not specified the placeholder properly in your execute(), try this.

$repFooterNews->execute(array(':myLimit'=>$myLimit));

answered Oct 05 '14 at 09:29

EternalHour

8,308
6
38
57

PDO issues Syntax error with LIMIT

2 Answers2