SQL Parameter in PDO PHP does not work

Navigation
2

The following code returns 11 bank records: 

$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, as I am following examples of the php documentation, everything is the same, except for my problem. I can not understand why this does not work, as it is the same as the following example (taken from link ): 

/* 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();
    
asked by anonymous 07.08.2018 / 14:42

2 answers

1

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

Here's 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

    
07.08.2018 / 14:58
1

Try to make a bindParam instead of passing the array of parameters in 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);

Pay attention to what @MatheusPicioli referred, doing bindValue and bindParam is different ! The bindValue is evaluated at the time it is assigned to the parameter, whereas the bindParam is at the time 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'
    
07.08.2018 / 15:11
Search for similar items when using a brokerage table? How to transform a character into an integer in C? [duplicate]