PDO fetchAll命令中未返回值

Question

I want to retrieve first four rows, the second grooup of four rows and so on until the end of the MySQL table named Productos. If I make queries from PHPMyAdmin values are retrieved correctly. But when I try to retrieve them from PHP no values are retrieved.

The code:

$this -> minimo = $_GET["minimo"];
echo "</br>" . $this -> minimo;

$this -> qry = "SELECT nombre,descripcion,precio,foto FROM productos       
LIMIT ?,4";
$this -> sqry = $this -> conexion -> prepare($this -> qry);
$this->sqry->bindParam(1,$this -> minimo,PDO::PARAM_INT);
$this -> sqry -> execute();

echo "</br>"; var_dump($this -> sqry);

$this -> resul = $this -> sqry -> fetchAll();

echo "Resul es " ; var_dump($this -> resul);

After running the script I watch the following:

0

object(PDOStatement)#4 (1) { 
        ["queryString"]=> string(62) "SELECT nombre,descripcion,precio,foto FROM productos LIMIT ?,4" }

Resul es array(0) { } 

How can I retrive values?

Thanks

Answer 1

Because you are using lazy loading i.e. an array in the execute parameter

$this -> sqry -> execute(array($this -> minimo));

All parameters are treated as strings, which will generate

LIMIT '1', 4

And '1' is illegal syntax

So you have to specifiy the type like this

$this->minimo = $_GET["minimo"];
$this->qry = "SELECT nombre,descripcion,precio,foto FROM productos LIMIT ?,4";
$this->sqry = $this->conexion->prepare($this->qry);
$this->sqry->bindParam(1, $this->minimo, PDO::PARAM_INT);
$this->sqry->execute();
$this->resul = $this->sqry->fetchAll();

// now $this->resul should be an array of arrays unless 
// you have set PDO up to return Object by default
// so a simple print_r() should show you whats in the returned data
print_r($this->resul);

Alternatively you can turn off Emulated Prepares as part of your connection and continue using the Lazy Load method

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

$this->minimo = $_GET["minimo"];
$this->qry = "SELECT nombre,descripcion,precio,foto FROM productos LIMIT ?,4";
$this->sqry = $this -> conexion -> prepare($this -> qry);
$this->sqry->execute(array($this -> minimo));

$this->resul = $this -> sqry -> fetchAll();

If you just want the first 4 rows you could use LIMIT 4 but it would probably be a good idea to add an ORDER BY to make sure you get the correct first 4 rows maybe like this LIMIT 4 ORDER BY id