PHP PDO Library - Binding Columns (bindColumn)

Binding Columns to Variables (bindColumn)

The bindColumn() method provides an elegant way of fetching data, where you bind a specific column from the database directly to a PHP variable. Once you execute the query and loop through the results using the fetch() method, the values in these variables are automatically updated.

EXAMPLE

<?php
class Database {
    private $server = "localhost";
    private $username = "root";
    private $password = "";
    private $db_name = "pdo_db";
    public $connection;

    public function __construct() {
        try {
            // Added charset=utf8mb4 for character support
            $dsn = "mysql:host=" . $this->server . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $this->connection = new PDO($dsn, $this->username, $this->password);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

    public function displayUsers() {
        $sql = "SELECT first_name, last_name FROM users";
        $statement = $this->connection->prepare($sql);
        $statement->execute();

        // Database columns are directly bound to PHP variables
        $statement->bindColumn('first_name', $first_name);
        $statement->bindColumn('last_name', $last_name);

        // fetch(PDO::FETCH_BOUND) automatically fills the variables bound above
        while ($statement->fetch(PDO::FETCH_BOUND)) {
            echo "User: " . $first_name . " " . $last_name . "<br>";
        }
    }
}

// USAGE:
$db = new Database();
$db->displayUsers();
?>

RESULT

The result will be displayed here...

This greatly improves code readability, as you avoid constantly referencing associative array keys and work directly with the names you have defined.

The bindColumn() method binds a column number or name to a PHP variable.

Useful when outputting larger amounts of data where we want clean and transparent code.

Variables are automatically updated with every call to the fetch() function.