SQL parameter in PDO PHP does not work

Question

The following code returns 11 records from the database:

$dbh = new PDO('sqlite:db_coleta.sqlite3');
$sth = $dbh->prepare('SELECT * FROM ROTA_VIEW WHERE usuario_id = 1 AND  0 = 0');
$sth->execute();
$red = $sth->fetchAll();
var_dump($red);

But the following code, using parameter, does not return any record (which is wrong):

$dbh = new PDO('sqlite:db_coleta.sqlite3');
$sth = $dbh->prepare('SELECT * FROM ROTA_VIEW WHERE usuario_id = ? AND  0 = 0');
$sth->execute(array(1));
$red = $sth->fetchAll();
var_dump($red);

I would like to know what I am doing wrong, because I am following examples of php documentation, all the same, only for my problem. I cannot understand why this does not work, since it is the same thing as the following example (taken from http://php.net/manual/en/pdo.prepare.php):

/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red = $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow = $sth->fetchAll();

Answer 1

The parameters you do not pass in the method execute(). You play the method bindValue().

Follow an example:

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Reference

Answer 2

Try making a bindParam instead of passing the array of parameters in the execute:

$dbh = new PDO('sqlite:db_coleta.sqlite3');

$usuario_id = 150;

$sth = $dbh->prepare('SELECT * FROM ROTA_VIEW WHERE usuario_id = :usuario_id AND 0 = 0');
$sth->bindParam(':usuario_id', $usuario_id, PDO::PARAM_INT);
$sth->execute();

$red = $sth->fetchAll();

var_dump($red);

---

An attention to what the @Matheuspicioli referred to, make bindValue and bindParam is different! The bindValue is assessed at the time it is assigned to the parameter, while the bindParam is the moment when the execute is evoked.

With bindValue:

$usuario_id = 150;

$sth = $dbh->prepare('SELECT * FROM ROTA_VIEW WHERE usuario_id = :usuario_id AND 0 = 0');
$sth->bindValue(':usuario_id', $usuario_id, PDO::PARAM_INT);

$usuario_id = 200;

$sth->execute();     // executado com 'WHERE usuario_id = 150'

With bindParam:

$usuario_id = 150;

$sth = $dbh->prepare('SELECT * FROM ROTA_VIEW WHERE usuario_id = :usuario_id AND 0 = 0');
$sth->bindParam(':usuario_id', $usuario_id, PDO::PARAM_INT);

$usuario_id = 200;

$sth->execute();     // executado com 'WHERE usuario_id = 200'