TUX.RE Forum • How PHP Connects to MySQL

Page 1 of 1

How PHP Connects to MySQL

Posted: Sat Apr 25, 2026 6:42 pm

by MegaTux

How PHP Connects to MySQL Securely (Step-by-Step)

Many dynamic websites use a database to store information such as users, posts, comments, products, settings or logs.

PHP can connect to MySQL or MariaDB and work with this data. In this guide we use PDO, because it supports prepared statements and is a good modern choice for secure PHP database access.

1. What You Need

A web server such as Apache or Nginx
PHP installed
MySQL or MariaDB installed
The PHP MySQL module
A database name
A database user with limited permissions

2. Install PHP MySQL Support

On Debian or Ubuntu:

Code: Select all

sudo apt update
sudo apt install php-mysql

Restart Apache:

Code: Select all

sudo systemctl restart apache2

If you use PHP-FPM:

Code: Select all

sudo systemctl restart php8.4-fpm

3. Create a Database and Limited User

Log in to MySQL or MariaDB:

Code: Select all

sudo mysql

Create the database:

Code: Select all

CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create a database user:

Code: Select all

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'CHANGE_THIS_TO_A_LONG_RANDOM_PASSWORD';

Give only the permissions needed for a normal application:

Code: Select all

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Important:
Do not use the MySQL root user for a website. A web application should only have the permissions it really needs.

4. Create a Simple Table

Log in with the new user:

Code: Select all

mysql -u testuser -p testdb

Create a table:

Code: Select all

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5. Store Database Config Safely

For simple learning, you can use a config file. In production, this file should not be inside the public web directory.

Good example:

Code: Select all

/var/www/example.com/config/db.php
/var/www/example.com/public/index.php

Bad example:

Code: Select all

/var/www/example.com/public/db.php

The public folder should contain only files that visitors are allowed to access.

6. Secure PDO Connection

Create:

Code: Select all

config/db.php

Example:

Code: Select all

<?php
// Database configuration
$dbHost = 'localhost';
$dbName = 'testdb';
$dbUser = 'testuser';
$dbPass = 'CHANGE_THIS_TO_A_LONG_RANDOM_PASSWORD';

try {
    // Create a secure PDO connection
    $pdo = new PDO(
        "mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4",
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
} catch (PDOException $e) {
    // Log the real error, but do not show it to visitors
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database connection failed.');
}

Why these PDO options matter:

ERRMODE_EXCEPTION makes errors easier to handle.
FETCH_ASSOC returns clean associative arrays.
ATTR_EMULATE_PREPARES false uses native prepared statements when possible.
utf8mb4 supports full Unicode, including special characters and emojis.

7. Insert Data Safely

Example:

Code: Select all

<?php
require_once __DIR__ . '/../config/db.php';

// Example data
$username = 'MegaTux';
$email = 'megatux@example.com';

// Insert data with a prepared statement
$stmt = $pdo->prepare(
    "INSERT INTO users (username, email) VALUES (:username, :email)"
);

$stmt->execute([
    ':username' => $username,
    ':email' => $email,
]);

echo 'User inserted successfully.';

8. Read Data Safely

Example:

Code: Select all

<?php
require_once __DIR__ . '/../config/db.php';

// Select users
$stmt = $pdo->query(
    "SELECT id, username, email, created_at FROM users ORDER BY id DESC"
);

$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo htmlspecialchars($user['username'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo htmlspecialchars($user['email'], ENT_QUOTES, 'UTF-8') . '<br>';
}

Important:
Prepared statements protect database queries.

Code: Select all

htmlspecialchars()

protects browser output against XSS.

You normally need both.

9. Search with Prepared Statements

Example:

Code: Select all

<?php
require_once __DIR__ . '/../config/db.php';

$email = 'megatux@example.com';

// Search user by email
$stmt = $pdo->prepare(
    "SELECT id, username, email FROM users WHERE email = :email LIMIT 1"
);

$stmt->execute([
    ':email' => $email,
]);

$user = $stmt->fetch();

if ($user) {
    echo 'User found: ' . htmlspecialchars($user['username'], ENT_QUOTES, 'UTF-8');
} else {
    echo 'User not found.';
}

10. Update Data Safely

Example:

Code: Select all

<?php
require_once __DIR__ . '/../config/db.php';

$id = 1;
$newUsername = 'SuperTux';

// Update user
$stmt = $pdo->prepare(
    "UPDATE users SET username = :username WHERE id = :id"
);

$stmt->execute([
    ':username' => $newUsername,
    ':id' => $id,
]);

echo 'User updated.';

11. Delete Data Safely

Example:

Code: Select all

<?php
require_once __DIR__ . '/../config/db.php';

$id = 1;

// Delete user
$stmt = $pdo->prepare(
    "DELETE FROM users WHERE id = :id"
);

$stmt->execute([
    ':id' => $id,
]);

echo 'User deleted.';

12. Important Security Notes

Do not use the database root user for websites.
Give the database user only the permissions needed.
Use prepared statements for user input.
Escape output with htmlspecialchars().
Keep database config files outside the public web root.
Use long random passwords for database users.
Do not display detailed errors to visitors.
Keep PHP, MySQL/MariaDB and your server updated.

13. What About Forms?

If your PHP script receives data from an HTML form, also think about:

Input validation
CSRF protection
Rate limiting
Spam protection
Server-side checks

A prepared statement protects the SQL query, but it does not automatically make the whole form secure.

14. Common Errors

Error: could not find driver

The PHP MySQL module is missing.

Code: Select all

sudo apt install php-mysql

Error: Access denied for user

The database username, password or permissions are wrong.

Error: Unknown database

The database does not exist.

Error: Connection refused

MySQL or MariaDB may not be running.

Code: Select all

sudo systemctl status mariadb

or:

Code: Select all

sudo systemctl status mysql

Conclusion

PHP can connect to MySQL and MariaDB securely when you use the right basics:

PDO
Prepared statements
Limited database users
Escaped output
Config files outside the public directory
No detailed error messages for visitors

This is the correct foundation for contact forms, login systems, admin panels, blogs, forums and many other dynamic web applications.