PHP
Binding values with bindValue()
Learn to bind a value to a parameter with bindValue() and how it differs from bindParam.
bindValue() binds the value now
bindValue() binds a value to a placeholder immediately. Unlike bindParam(), it takes a copy of the value at bind time, not a reference. So even if you change the variable after binding, the query still uses the old value.
The syntax is bindValue(1, 30, PDO::PARAM_INT); the first argument is an index or ':name', the second is the value, the third is a type constant.
For one-off queries with fixed values, bindValue() is more convenient.
bindParam vs bindValue
The two methods look similar but behave differently. bindParam() binds a variable by reference; the value is read when execute() runs. You can update the variable in a loop to insert repeatedly.
bindValue() copies the value at bind time. Even if the variable changes later, the query uses the first value. It is suited for one-off queries with fixed values.
Picking the wrong method can cause surprising results; the task decides which one fits.
Exercise Filter by price with bindValue
Build the template 'SELECT name FROM products WHERE price >= ?' with prepare(). Bind the value immediately with bindValue(1, 30, PDO::PARAM_INT). Call execute(), fetch all rows with fetchAll(PDO::FETCH_ASSOC) and print the name field of each row.
Show example solution
:PARAM_INT). Run $stmt->execute(). Get $rows = $stmt->fetchAll(PDO::FETCH_ASSOC). Print with foreach ($rows as $row) { echo $row['name'] . "\n"; }.
Example solution:
<?php
// Bind the value immediately with bindValue (not by reference)
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)");
$pdo->exec("INSERT INTO products (name, price) VALUES ('Pen', 25)");
$pdo->exec("INSERT INTO products (name, price) VALUES ('Notebook', 40)");
$stmt = $pdo->prepare("SELECT name FROM products WHERE price >= ?");
$stmt->bindValue(1, 30, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['name'] . "\n";
}
Test yourself
-
Complete the bindValue() call
$stmt->____(1, 30, PDO::PARAM_INT);
Show answer
$stmt->bindValue(1, 30, PDO::PARAM_INT);
-
What is the key difference between bindValue() and bindParam()?
- They are completely identical
- bindValue takes a reference, bindParam a value
- bindValue only works with numbers
- bindValue copies the value now, bindParam holds a reference
Show answer
bindValue copies the value now, bindParam holds a reference — Correct! bindValue() copies the value now; bindParam() holds a reference and reads it at execute() time.
-
Match bindValue and bindParam differences
- bindValue()
- Copies the value immediately
- bindParam()
- Holds reference, reads later
- PDO::PARAM_INT
- Integer type
- PDO::PARAM_BOOL
- Boolean type
Next lesson: SQL injection and prevention
