PDOException when i try to do fetch(PDO::FETCH_ASSOC)

Question

I got a page in PHP that was doing the job fine, but now I want to display the result with a dynamic interaction:

When the user selects a value in the dropdown I limit the request to the value selected.

But when I try I got:

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

My js is working, but the PHP is now broken, I don't no why.

Can somebody see the problem?

$bdd = dbConnect();` 
$demand_ecard = $bdd->prepare("SELECT * 
                               FROM mes_ecards 
                               JOIN reseaux_socecard ON (mes_ecards.id_mesecard = reseaux_socecard.id_ecard)
                               WHERE mes_ecards.id_mesecard IN (SELECT id_ecard 
                                                                FROM demande_keepro 
                                                                WHERE etat_demande = 0)
                               LIMIT :limite");
$demand_ecard->execute(array(':limite'=>$_GET["limite"],));
echo ('<form>');

while ($tabl_result = $demand_ecard->fetch(PDO::FETCH_ASSOC) ){

    echo('<br><hr style="height: 2px; color: #000000; background-color: #000000; border: none;">
          <div id="'.$tabl_result['id_user'].'" class="count">
              <span class="ecardvaleur"  class="ecardvaleur">login : '.$tabl_result['login_user'].'</span class="ecardvaleur" ><br>');

    if($tabl_result['prefix']!=''){
        echo ('<span class="ecardvaleur" >prefix : '.$tabl_result['prefix'].'</span class="ecardvaleur" ><br>');
    }`

    /* many other if */

    echo ('
    <br>
    <input class="valide" type="radio" id="'.$tabl_result['id_user'].'v" name="'.$tabl_result['id_user'].'" value="confirmer">
    <label for="'.$tabl_result['id_user'].'v">confirmer</label>
    <input class="denied" type="radio" id="'.$tabl_result['id_user'].'r" name="'.$tabl_result['id_user'].'" value="refuser">
    <label for="'.$tabl_result['id_user'].'r">refuser</label>
    ');
    echo('</div>');  
}

echo ('</form> 
       </div>
       </div>');

mysql_close($connexion);

Masivuye Cokile · Accepted Answer · 2018-06-21T14:17:31.810

0

When you use execute(array()) PDO treats every parameter as a string. As a result, the prepared LIMIT ?,? query becomes LIMIT 'number' which is invalid syntax that causes query to fail.

You can solve by two ways :

turning emulation off (as MySQL can sort all placeholders properly). To do so one can run this code:

$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

Then you can use your execute(array());

Or

bind variables explicitly while setting the proper param type:

$demand_ecard->bindParam(":limite", $_GET["limite"],PDO::PARAM_INT);

edited Jun 21 '18 at 14:17

answered Jun 21 '18 at 14:14

Masivuye Cokile

4,754
3
19
34

I thought this was the case, but I couldn't actually reproduce the error on PHP 7.1. Maybe this only affects earlier versions. – Devon Bessemer Jun 21 '18 at 14:15
@Devon could be... or with the op the GET is not an integer – Masivuye Cokile Jun 21 '18 at 14:18
Yeah, with 7.1 even a string `'1'` works. So it would have to be a non-numeric string. – Devon Bessemer Jun 21 '18 at 14:20
haven't really tested with php 7 @Devon i've been cautions from the start – Masivuye Cokile Jun 21 '18 at 14:24
1

Thank you the first method work very well ! – B.E2s Jun 21 '18 at 14:32

PDOException when i try to do fetch(PDO::FETCH_ASSOC)

1 Answers1