MySQL Database

Why Applications Need a Database

In simple terms: Most web applications must remember things: user accounts, products, orders, blog posts, and comments. Storing them in files becomes unmanageable as soon as data grows, many users write at the same time, or you need to search and combine information ("all orders from Chennai customers this month"). A database stores data in an organized way and answers such questions quickly and safely.

Technically: MySQL is an open-source relational database management system (RDBMS). It stores data in tables made of rows and columns, enforces rules (data types, unique values, relationships), and is queried with SQL (Structured Query Language). PHP communicates with MySQL through the MySQLi or PDO extensions. MariaDB, a community fork of MySQL, is largely compatible and works with the same PHP code.

Why MySQL With PHP?

Relational Database Concepts

Database: gocourse_shop
│
├── Table: customers                    ├── Table: orders
│   ┌────┬───────┬──────────────────┐   │   ┌─────┬─────────────┬─────────┬────────┐
│   │ id │ name  │ email            │   │   │ id  │ customer_id │ status  │ total  │
│   ├────┼───────┼──────────────────┤   │   ├─────┼─────────────┼─────────┼────────┤
│   │ 1  │ Asha  │ asha@example.com │◄──┼───│ 501 │ 1           │ paid    │ 380.00 │
│   │ 2  │ Bala  │ bala@example.com │◄──┼───│ 502 │ 2           │ pending │ 1299.00│
│   └────┴───────┴──────────────────┘   │   └─────┴─────────────┴─────────┴────────┘

Instead of repeating the customer's name and email in every order, each order stores only customer_id. This avoids duplicated data (a principle called normalization) and keeps information consistent: changing Asha's email updates it everywhere.

The Main SQL Statements

MySQLi vs PDO

PHP offers two extensions for MySQL:

Both are secure when used with prepared statements. This section first teaches MySQLi (lessons 193–207), then PDO (lessons 208–213). For new projects, PDO is usually the preferred choice because the same API works with other databases.

Note: The old mysql_* functions (mysql_connect(), mysql_query()) were removed in PHP 7.0. Tutorials using them are outdated and insecure.

Setting Up MySQL for Development

Option 1: An All-in-One Package

Packages such as XAMPP, Laragon, or MAMP install Apache, PHP, and MySQL/MariaDB together, with phpMyAdmin for managing databases in the browser.

Option 2: Docker

docker run --name gocourse-mysql \
  -e MYSQL_ROOT_PASSWORD=change-this-root-password \
  -e MYSQL_DATABASE=gocourse_shop \
  -e MYSQL_USER=gocourse_app \
  -e MYSQL_PASSWORD=change-this-app-password \
  -p 3306:3306 \
  -d mysql:8.4

This starts a MySQL 8.4 server (a long-term-support release), creates the gocourse_shop database, and creates an application user with access to it.

Option 3: Native Installation

On Ubuntu: sudo apt install mysql-server (or mariadb-server), then secure the installation with sudo mysql_secure_installation.

Checking the PHP Extensions

<?php
var_dump(extension_loaded('mysqli'));
var_dump(extension_loaded('pdo_mysql'));

Output (when both are installed)

bool(true)
bool(true)

If an extension is missing, install it (for example, sudo apt install php8.4-mysql on Ubuntu, which provides both) and restart PHP-FPM or the web server.

A Dedicated Database User

Applications should never connect as root. Create a user that has only the permissions the application needs:

CREATE USER 'gocourse_app'@'localhost' IDENTIFIED BY 'a-long-random-password';

GRANT SELECT, INSERT, UPDATE, DELETE ON gocourse_shop.* TO 'gocourse_app'@'localhost';

Why Least Privilege Matters

If an attacker ever finds a way to run SQL through your application, a restricted user cannot drop tables, read other databases, or create new users. Schema changes (creating and altering tables) are performed by a separate account during deployment, not by the running application.

The Example Database for This Section

The following lessons build and use a small shop database:

Creating these tables is shown step by step in MySQL Create DB and MySQL Create Table.

How PHP Talks to MySQL

Browser ──HTTP──► PHP script ──SQL over a connection──► MySQL server
                      ▲                                      │
                      └──────────── result rows ◄────────────┘

1. PHP opens a connection to the MySQL server using a host, port, username, password, and database name.
2. It sends SQL statements — always with prepared statements when user input is involved.
3. MySQL returns results: rows for SELECT, affected-row counts for INSERT/UPDATE/DELETE.
4. PHP turns the rows into arrays or objects and generates the response.

Common Mistakes

Using the Removed mysql_* Functions

Effect: Fatal "Call to undefined function" errors in PHP 7+.

Fix: Use MySQLi or PDO.

Connecting as root

Effect: A single vulnerability can compromise the entire database server.

Fix: Create a dedicated user with minimal privileges.

Building SQL From User Input by Concatenation

Effect: SQL injection (see SQL Injection Prevention).

Fix: Always use prepared statements.

Storing Everything in One Big Table

Effect: Duplicated, inconsistent data.

Fix: Design related tables connected by keys.

Best Practices

• Use MySQL 8.x or a current MariaDB release, with the utf8mb4 character set.
• Choose PDO for new projects; understand MySQLi for existing code.
• Give the application its own database user with least privilege.
• Use prepared statements for every query that includes external values.
• Keep database credentials in environment variables, never in code.

Official reference: https\://www.php.net/manual/en/book.mysqli.php