How to use PHP PDO to execute raw SQL queries

You will configure a PHP PDO connection to execute raw SQL queries and fetch results from a MySQL database. These steps target PHP 8.3.x and MySQL 8.0.x on Ubuntu 24.04. You will learn to use exec(), query(), and prepare() methods to run statements.

Prerequisites

• Ubuntu 24.04 or Debian 12 with PHP 8.3.x installed.
• A running MySQL or MariaDB server.
• A database and table created beforehand.
• PHP PDO extension enabled (php -m | grep pdo).
• Write permissions for the database user.

Step 1: Create a database and table

Create a test database and a table named users with an id and name column. This provides a target for your raw SQL queries.

mysql -u root -p -e "
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
"

Expected output shows the table created and rows inserted:

Database created.
Table created.
3 rows affected.

Step 2: Create the PDO connection

Define a function to create a PDO instance using a DSN string, username, and password. This centralizes connection logic and handles exceptions.