PDO->MySQL

[RobertP]

i am having an issue with a prepared statement. if i replace my '?' with a 4, it works as expected. only 1 issue is that the variable $max is dynamic.

 

example code

<?php

ini_set('display_errors',1);
ini_set('date.timezone','America/Toronto');

$connection = new PDO('mysql:host=127.0.0.1;port=3390;dbname=gludoe','user','pass');
$connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

$max = 4;

$stmt = $connection->prepare('select * from plugin_simsmanager_items order by add_date desc limit ?;');
$stmt->execute(array($max));

print_r($stmt->fetchAll());

?>

 

output

Array
(
)

 

expected output

Array
(
    [0] => Array
        (
            [item_id] => 1
            [item_name] => Test 1
            [image_id] => commons/plugins/simsManager/test1.jpg
            [add_date] => 0.0000
            [item_description] => 
            [file_id] => 
            [age_group] => 
        )

    [1] => Array
        (
            [item_id] => 2
            [item_name] => Test 2
            [image_id] => commons/plugins/simsManager/test2.jpg
            [add_date] => 0.0000
            [item_description] => 
            [file_id] => 
            [age_group] => 
        )

    [2] => Array
        (
            [item_id] => 3
            [item_name] => Test 3
            [image_id] => commons/plugins/simsManager/test3.jpg
            [add_date] => 0.0000
            [item_description] => 
            [file_id] => 
            [age_group] => 
        )

    [3] => Array
        (
            [item_id] => 4
            [item_name] => Test 4
            [image_id] => commons/plugins/simsManager/test4.jpg
            [add_date] => 0.0000
            [item_description] => 
            [file_id] => 
            [age_group] => 
        )

)

[PFMaBiSmAd]

When you supply the values via an array to the ->execute() method -

All values are treated as PDO::PARAM_STR.

 

This results in single quotes being added around the number, which is invalid for the LIMIT clause.

 

You need to specifically bind the value as an integer -

 

$stmt->bindParam(1, $max, PDO::PARAM_INT);

 

 

[RobertP]

thank you so much