PHP
Binding parameters with bindParam()
Learn to bind a variable reference to a parameter with bindParam().
bindParam() binds by reference
bindParam() binds a variable to a placeholder by reference. It reads the value when execute() runs, not at bind time. So you can bind first, change the variable later, and run multiple inserts with the same $stmt.
For positional placeholders the first argument is the index (1, 2, …); for named ones it is the ':name' string. The third argument is a type constant.
This method is especially useful when inserting repeatedly inside a loop.
Type constants and result methods
bindParam() and bindValue() take a type constant as the third argument: PDO::PARAM_STR for strings, PDO::PARAM_INT for integers, PDO::PARAM_BOOL for booleans, PDO::PARAM_NULL for null.
After an INSERT two methods are useful: $pdo->lastInsertId() returns the id of the last inserted row; $stmt->rowCount() returns the number of affected rows.
These methods let you confirm the insert succeeded and read the new record's id.
Exercise INSERT with bindParam
Build the template 'INSERT INTO users (name, email) VALUES (?, ?)' with prepare(). Bind $name and $email with bindParam(1, $name, PDO::PARAM_STR) and bindParam(2, $email, PDO::PARAM_STR). Assign values ('Alex' and 'alex@example.com'), call execute(). Print $pdo->lastInsertId() and $stmt->rowCount() on separate lines.
Show example solution
:PARAM_STR) and $stmt->bindParam(2, $email, PDO::PARAM_STR). Assign $name = 'Alex' and $email = 'alex@example.com'. Call $stmt->execute(). Print echo $pdo->lastInsertId() . "\n" and echo $stmt->rowCount() . "\n".
Example solution:
<?php
// Bind a variable by reference with bindParam and run an INSERT
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
$name = '';
$email = '';
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bindParam(1, $name, PDO::PARAM_STR);
$stmt->bindParam(2, $email, PDO::PARAM_STR);
$name = 'Alex';
$email = 'alex@example.com';
$stmt->execute();
echo $pdo->lastInsertId() . "\n";
echo $stmt->rowCount() . "\n";
Test yourself
-
Match the bindParam concepts
- bindParam()
- Binds a variable by reference
- PDO::PARAM_STR
- String type constant
- PDO::PARAM_INT
- Integer type constant
- 1, 2, ...
- Positional parameter index
-
When does bindParam() read the variable's value?
- Right away, when bound
- When execute() is called
- When prepare() is called
- When fetch() is called
Show answer
When execute() is called — Correct! bindParam() holds a reference and reads the value at execute() time.
-
Complete the bindParam() call
$stmt->____(1, $name, PDO::PARAM_STR);
Show answer
$stmt->bindParam(1, $name, PDO::PARAM_STR);
Next lesson: Binding values with bindValue()
