PHP PDO Connection

PDO means PHP Data Objects. It is a PHP extension used to work with databases.

PDO can be used to connect PHP with MySQL and other supported database systems.

PHP
 ↓
PDO
 ↓
MySQL

PDO provides a standard interface for connecting to databases and executing SQL statements.

Some benefits include:

• Reusable database connection
• Prepared statements
• Exception handling
• Cleaner database code
• Better security when used correctly

Our REST API uses MySQL as the database and PDO as the PHP database access interface.

React Native
      ↓
PHP REST API
      ↓
PDO
      ↓
MySQL
      ↓
student_api

Before creating a PDO connection, we need the database connection details.

$host = "localhost";
$dbname = "student_api";
$username = "root";
$password = "";

These values are typical for a local XAMPP development environment.

The host identifies the server where MySQL is running.

$host = "localhost";

When MySQL is running on the same computer as PHP, localhost is commonly used.

The database name tells PDO which MySQL database should be used.

$dbname = "student_api";

This is the database we created in the previous lessons.

The username is required to authenticate with the MySQL server.

$username = "root";

The default local XAMPP MySQL username is commonly root. Production servers usually use a dedicated database user.

The password is used along with the MySQL username.

$password = "";

In some local development environments the root account may have an empty password. Never assume this configuration is appropriate for production.

DSN stands for Data Source Name. It contains information about the database connection.

$dsn = "mysql:host=localhost;dbname=student_api";

The DSN tells PDO that we are connecting to MySQL on localhost and using the student_api database.

We can specify UTF-8 character encoding in the DSN.

$dsn = "mysql:host=localhost;dbname=student_api;charset=utf8mb4";

Using utf8mb4 provides broad Unicode support.

The PDO object can be created by passing the DSN, username, and password.

$pdo = new PDO(
    $dsn,
    $username,
    $password
);

The variable $pdo will represent the database connection.

A simple PDO connection can look like this:

<?php

$host = "localhost";
$dbname = "student_api";
$username = "root";
$password = "";

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

$pdo = new PDO($dsn, $username, $password);

?>

When the PDO object is created, PHP attempts to connect to the MySQL server.

PHP
 ↓
PDO Connection
 ↓
MySQL Server
 ↓
student_api Database

If the connection is successful, PHP can execute database queries.

Database connections can fail. We can use try and catch to handle connection exceptions.

try {

    $pdo = new PDO($dsn, $username, $password);

} catch (PDOException $e) {

    echo "Database connection failed.";

}

PDOException is used to catch PDO-related exceptions.

catch (PDOException $e) {

    echo "Database connection failed.";

}

This allows the application to handle database connection problems.

We can configure PDO to throw exceptions when database errors occur.

$pdo->setAttribute(
    PDO::ATTR_ERRMODE,
    PDO::ERRMODE_EXCEPTION
);

This makes database errors easier to detect and handle.

<?php

$host = "localhost";
$dbname = "student_api";
$username = "root";
$password = "";

try {

    $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

    $pdo = new PDO($dsn, $username, $password);

    $pdo->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

} catch (PDOException $e) {

    die("Database connection failed.");

}

?>

It is better to keep the database connection in a separate file instead of writing the connection code in every API file.

For example:

config/
    database.php

The API files can include this file whenever a database connection is required.

A reusable database connection file can contain:

<?php

$host = "localhost";
$dbname = "student_api";
$username = "root";
$password = "";

try {

    $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

    $pdo = new PDO($dsn, $username, $password);

    $pdo->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

} catch (PDOException $e) {

    die("Database connection failed.");

}

An API file can include the database connection file.

require_once "config/database.php";

After including the file, the $pdo connection can be used by the API.

Once the PDO connection is available, PHP can execute SQL queries.

$stmt = $pdo->query(
    "SELECT * FROM students"
);

The query retrieves records from the students table.

PDO supports prepared statements. Prepared statements are important when working with values supplied by users or mobile applications.

$stmt = $pdo->prepare(
    "SELECT * FROM students WHERE id = ?"
);

The value can then be supplied separately.

After preparing a statement, values can be supplied using execute().

$stmt = $pdo->prepare(
    "SELECT * FROM students WHERE id = ?"
);

$stmt->execute([$student_id]);

This separates the SQL statement from the supplied value.

PDO becomes the database layer between PHP and MySQL in our REST API.

React Native
      ↓
HTTP Request
      ↓
PHP REST API
      ↓
PDO
      ↓
MySQL
      ↓
JSON Response
      ↓
React Native

• MySQL server is not running
• Incorrect database name
• Incorrect username
• Incorrect password
• Incorrect host
• PDO MySQL driver is unavailable
• Database does not exist

Check the connection settings when PDO cannot connect to MySQL.

For learning purposes, you can test whether the connection was successful.

try {

    $pdo = new PDO($dsn, $username, $password);

    $pdo->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    echo "Database connected successfully.";

} catch (PDOException $e) {

    echo "Database connection failed.";

}

rest_api/
│
├── config/
│   └── database.php
│
├── api/
│   ├── students.php
│   └── users.php
│
└── index.php

The exact structure can grow as more API features are added.

Database credentials should not be exposed to the mobile application.

React Native
     ↓
Public API
     ↓
PHP Server
     ↓
Database Credentials
     ↓
MySQL

The database username and password should remain on the server.

$host
   ↓
$dbname
   ↓
$username
   ↓
$password
   ↓
$dsn
   ↓
new PDO()
   ↓
$pdo
   ↓
SQL Queries

This connection will be reused by the PHP REST API files.

PDO provides a clean way for PHP to connect with MySQL. In our REST API project, we use PDO to connect to the student_api database. The connection can be placed in a reusable database.php file and included in API files whenever database access is required.

PHP
 ↓
PDO
 ↓
student_api
 ↓
students
 ↓
REST API