Prepared SQL query, UNION and LIMIT - syntax

Question

i am having an issue with an SQL query, using limits. I'd like to have a variable limit parameters. As i've done successfully elesewhere in my site, i wanted to use something like LIMIT number = :number then specify 'number'=>$numberResults.

Somehow, this doesn't work.. (i've tried, if i write out LIMIT 20 in my query it works fine, but that isn't the issue.

Here's my code :

connexion_bdd.php

<?php
        try
        {
            $bdd = new PDO('mysql:host=localhost;dbname=mise_a_quai_tardive', 'root', '');
        }
        catch (Exception $e)
        {
                die('Erreur : ' . $e->getMessage());

        }
 ?>

mise_a_quai_tardive.php That is giving me issues

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <link rel="stylesheet" href="style.css" />
    <title>Module Suivi des Mises à Quai Tardives</title>
</head>

<body>
<?php include("connexion_bdd.php"); ?>
<?php
  $resultatsParPage = 15;
$reponse = $bdd->prepare("SELECT id_dossier, type_sortie, date_incident, no_train,    commentaire FROM sortie_prevue
        UNION 
        SELECT id_dossier, type_sortie, date_incident, no_train, commentaire FROM sortie_non_prevue
        ORDER BY date_incident DESC LIMIT limit=:limit")

$reponse -> execute(array('limit'=> $resultatsParPage));

while ($donnees = $reponse->fetch())
{

/* i display the results here, they display fine with a 'static' SQL query, but nothing shows up with my query at the moment.. */

}

</body>
</html>

thanks a lot for your help ! I'm sure there is a simple explanation to this ! :)

http://stackoverflow.com/questions/10014147/limit-keyword-on-mysql-with-prepared-statement-maybe-still-a-bug — Mihai, Sep 23 '13 at 14:34
try this, $response->bindParam(':limit', $resultatsParPage, PDO::PARAM_INT); $response->execute(); — wonde, Sep 23 '13 at 14:36
@wonde : just tried it, no sql error, but still no results showing up... :( — Malcoolm, Sep 23 '13 at 14:41
mmm instead of the while loop try this, $result = $response->fetchAll(); print_r($result); — wonde, Sep 23 '13 at 14:46
`LIMIT limit=:limit` is also wrong syntax... `LIMIT 20` will work. See the comment by Mihai for a proper solution! — Marty McVry, Sep 23 '13 at 14:55

score 0 · Accepted Answer · answered Sep 23 '13 at 15:56

strangely enough, this seems to work... i execute the query directly, with no preparation.

$reponse = $bdd->query("SELECT id_dossier, type_sortie, date_incident, no_train, commentaire FROM sortie_prevue
        UNION 
        SELECT id_dossier, type_sortie, date_incident, no_train, commentaire FROM sortie_non_prevue
        ORDER BY date_incident DESC LIMIT $resultatsParPage");

if someone has a comment to explain this, he would be very welcome !

Prepared SQL query, UNION and LIMIT - syntax

1 Answers1