CODEWITHSUNDEEP
phpmysqlpdo
arielnmz
arielnmz

Reputation: 9145

PDO bindParam() with prepared statement isn't working

Ok, this is the problem:

This works:

$STH = $DBH->prepare("SELECT * FROM juegos WHERE id = 1");
$STH->execute();

This doesn't:

$STH = $DBH->prepare("SELECT * FROM juegos WHERE id = :id");
$STH->bindParam(':id', '1', PDO::PARAM_STR);
$STH->execute();

What in the world am I doing wrong? It doesn't even throw an exception

Thank you everyone!

Also, this is the whole code

<?php
    try {
        $DBH = new PDO("everything is", "ok", "here");

        $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

        $STH = $DBH->prepare("SELECT * FROM juegos WHERE id = :id");
        $STH->bindParam(':id', '1', PDO::PARAM_STR);
        $STH->execute();

        $STH->setFetchMode(PDO::FETCH_ASSOC);

        while($row = $STH->fetch()) {
            echo $row['nombre']."<br/>";
        }

        $DBH = null;

        echo "Todo salió bien";

    } catch (PDOException $e) {
        echo "Error";
    }

?>

Upvotes: 21

Views: 68664

Answers (4)

Cristo Rutazihana
Cristo Rutazihana

Reputation: 121

PHP bindParam() binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

The correct way to use bindParam is:

$id = 1;
$sth = $DBH->prepare("SELECT * FROM juegos WHERE id = :id");
$sth->bindParam(':id', $id, PDO::PARAM_INT);// use bindParam to bind the variable
                          // ^ PDO::PARAM_INT - the value of the variable $id should be an int
                     // ^ $id - the variable being represented by ':id',
              // ^ :id - represents the variable
              // $id - the variable being represented by ':id',

PHP bindValue() binds a value to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

$id=10;
$name=roadkill;
$sth = $dbh->prepare('SELECT *
    FROM juegos
    WHERE id < :id AND name = :name');
$sth->bindValue(':id', $id, PDO::PARAM_INT);// use bindValue to bind the variable's value
$sth->bindValue(':name', $name, PDO::PARAM_STR);// use bindValue to bind the variable's value

The key difference between these two methods is that unlike PDOStatement::bindValue(), with bindParam() the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Upvotes: 8

david strachan
david strachan

Reputation: 7228

Using bindParam() the variable is bound as a reference.

A string can't be passed by reference.

The following things can be passed by reference:

Variables, i.e. foo($a)

New statements, i.e. foo(new foobar())

References returned from functions

Try using bindValue()

$STH->bindValue(':id', '1', PDO::PARAM_STR);

Upvotes: 28

SAR
SAR

Reputation: 1845

do not pass the value directly to BindParam.

try {
       // $DBH = new PDO("everything is", "ok", "here");
        $DBH = new PDO("mysql:host=localhost;dbname=test", 'root', '');
        $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $STH = $DBH->prepare("SELECT * FROM statstracker WHERE SrNo = :id");
        $id = 1; // here you should keep it as variable and pass it to param
        $STH->bindParam(':id', $id, PDO::PARAM_STR);
        $STH->execute();

        $STH->setFetchMode(PDO::FETCH_ASSOC);

        while($row = $STH->fetch()) {
            echo $row['SrNo']."<br/>";
        }

        $DBH = null;

        echo "Todo salió bien";

    } catch (PDOException $e) {
        echo "Error";
    }

Upvotes: 1

hackedd
hackedd

Reputation: 343

The value for the :tabla parameter will be automatically quoted and escaped by PDO. The query executed would become:

SELECT * FROM 'juegos'

which is not valid SQL.

Upvotes: 3

Related Questions