What is the differences and advantages between bindParam(), bindValue() and execute(array())

Question

<?php

    $db = new PDO($dsn,$username,$password);
    $uname='avi';
    $age=19;
    $stmt = $db->prepare('INSERT INTO table(uname,age) VALUES(:uname,:age)');
    $stmt->execute(array(':uname'=>$uname,':age'=>$age));

    $stmt = $db->prepare('INSERT INTO table(uname,age) VALUES(?,?)');
    $stmt->execute(array($uname,$age));

    $stmt = $db->prepare('INSERT INTO table(uname,age) VALUES(:uname,:age)');
    $stmt->bindValue(':uname',$uname); //can be $uname or just 'avi'
    $stmt->binParam(':age',$uname); //cannot be 'avi' or value only
    $stmt->execute();

?>

When should we use bindParam()? All the previous methods seem to be easier and require less lines of code.

Whats the benefit of using bindParam() over other methods(bindValue(), execute())?

Answer 1

bindParam() binds the parameter by reference, so it will be evaluated at $stmt->execute(), which is unlike bindValue() which evaluates at the call of the function itself.

So as an example:

bindParam:

<?php

    try {

        $dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $dbh->prepare("SELECT * FROM test WHERE number = ?");
        $stmt->bindParam(1, $xy, PDO::PARAM_INT);
        $xy = 123;  //See here variable is defined after it has been bind
        $stmt->execute();

        print_r($stmt->fetchAll());

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

?>

works great!

bindValue:

<?php

    try {

        $dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $dbh->prepare("SELECT * FROM test WHERE number = ?");
        $stmt->bindValue(1, $xy, PDO::PARAM_INT);
        $xy = 123;  //See here variable is defined after it has been bind
        $stmt->execute();

        print_r($stmt->fetchAll());

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

?>

output:

Notice: Undefined variable: xy

Also a few other differences:

• bindParam() also has the argument length which can(must) be used if you call a IN&OUT procedure to store the output back in the variable (Which also requires to append PDO::PARAM_INPUT_OUTPUT with an OR statement to the type argument)
• With bindParam() & bindValue() you can specify the type of the value, which you can't do in the execute(), there everything is just a string (PDO::PARAM_STR)