PHP MySQL Connection

Our Student Management API needs a database to store student information. PHP acts as the backend and communicates with MySQL.

React Native
      ↓
PHP REST API
      ↓
MySQL Database

A database connection creates a communication link between the PHP application and the MySQL server.

PHP
 ↓
Database Connection
 ↓
MySQL

After the connection is established, PHP can execute database operations.

MySQL is the database server that will store the data for our REST API.

For example, our database can contain a table named:

students

The table can contain student names, emails, mobile numbers, and courses.

PHP needs some information to connect to MySQL.

• Database host
• Database name
• Database username
• Database password

These values depend on your local or production database configuration.

The database host identifies the computer where MySQL is running.

For a typical local development environment, it may be:

localhost

Another server may use a different hostname or IP address.

The database name identifies the specific MySQL database used by our application.

For example:

student_api

The database must exist before the application can use its tables.

PHP needs a MySQL username with permission to access the database.

For a typical local development setup, the username may be:

root

Production servers normally use a dedicated database account.

PHP may also need the password for the MySQL user.

A local development installation may have an empty password, depending on how MySQL was configured.

$password = "";

Production databases should use strong credentials and should not expose them publicly.

Create a file named database.php inside the config folder.

student_api/
│
└── config/
    └── database.php

This file will contain our database connection code.

A basic PDO connection can be created like this:

<?php

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

$pdo = new PDO(
    "mysql:host=$host;dbname=$dbname",
    $username,
    $password
);

This creates a PDO object that can communicate with MySQL.

The first argument passed to PDO is called the Data Source Name (DSN).

mysql:host=localhost;dbname=student_api

It tells PDO which database driver, host, and database should be used.

The DSN begins with:

mysql:

This tells PDO that the application is connecting to a MySQL database.

The host can be specified inside the DSN.

mysql:host=localhost

For a local MySQL server, localhost is commonly used.

The database name can also be specified in the DSN.

mysql:host=localhost;dbname=student_api

Here, PHP will connect to the student_api database.

It is useful to specify the character set for the database connection.

mysql:host=localhost;dbname=student_api;charset=utf8mb4

Using an appropriate character set helps the application handle a wide range of text correctly.

<?php

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

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

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

This creates the connection using the MySQL driver and UTF-8 compatible character encoding.

PDO can be configured to throw exceptions when database errors occur.

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

Exceptions make database errors easier to detect and handle during development.

<?php

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

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

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

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

A try-catch block can be used to handle connection exceptions.

try {

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

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

} catch (PDOException $e) {

    die("Database connection failed.");

}

For production APIs, error responses should be designed carefully so that sensitive database details are not exposed to clients.

Our API endpoint can include the database connection file.

require_once '../config/database.php';

After including the file, the API can use the PDO connection.

Client Request
      ↓
students.php
      ↓
database.php
      ↓
PDO Connection
      ↓
MySQL
      ↓
Query Result
      ↓
JSON Response

This is the basic database communication flow of our REST API.

During development, you can temporarily test whether the connection was created successfully.

echo "Database connected successfully";

Do not leave debugging output inside a JSON API response in the final version.

student_api/
│
├── config/
│   └── database.php
│
└── api/
    └── students.php

The API file can reuse the connection defined in the config folder.

If multiple API files need database access, creating the connection separately in every file can lead to duplicated code.

Instead, keep the connection in one reusable file.

database.php
     ↓
students.php
courses.php
users.php

• Incorrect database name
• Incorrect username
• Incorrect password
• Incorrect host
• MySQL server is not running
• Incorrect relative path
• PDO MySQL driver is unavailable

Check these items when a PHP application cannot connect to MySQL.

Before testing the API locally, make sure the MySQL server is running.

If you are using a local development package such as XAMPP, start the required services before testing the database connection.

Database credentials should be protected.

• Do not expose passwords in API responses.
• Do not publish database credentials in public repositories.
• Use appropriate database permissions.
• Use secure configuration practices in production.
• Do not display detailed database exceptions to API users.

React Native
      ↓
HTTP Request
      ↓
students.php
      ↓
database.php
      ↓
PDO
      ↓
MySQL
      ↓
Student Data
      ↓
JSON Response
      ↓
React Native

A simple reusable connection file for our project can look like this:

<?php

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

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

try {

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

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

} catch (PDOException $e) {

    die("Database connection failed.");

}

PHP needs a database connection to communicate with MySQL. In our REST API project, we will keep the connection code inside config/database.php and reuse it from our API endpoints. PDO provides the connection mechanism that we will use to execute database operations.

PHP API
   ↓
PDO
   ↓
MySQL
   ↓
Database Tables