Php Mysql Database

Introduction to MySQL Relational Databases with PHP

MySQL is the world's most popular open-source Relational Database Management System (RDBMS). PHP applications interact with MySQL databases using SQL (Structured Query Language) queries to perform CRUD (Create, Read, Update, Delete) operations.

PHP to MySQL Architecture

flowchart LR
    A["PHP Application"] -->|PDO / MySQLi| B["Database Driver"]
    B -->|TCP/IP Connection| C["MySQL Server"]
    C --> D["Database (Tables, Indexes, Rows)"]
    D --> C
    C --> A

Relational Database Core Concepts

1. Database: Logical container holding structured database tables.
2. Table: Grid collection of structured data rows and columns.
3. Primary Key: Unique identifier column (e.g. id INT AUTO_INCREMENT PRIMARY KEY).
4. Foreign Key: Column establishing relational links to primary keys in another table.

Practical Code Example (Creating SQL Schema)

-- Standard MySQL Schema Table Creation Script
CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE app_db;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Best Practices

• Use InnoDB Storage Engine: Ensures ACID compliance, transactional integrity, and foreign key support.
• Use utf8mb4 Character Set: Supports full 4-byte UTF-8 character encodings (including international characters and emojis).
• Index Frequently Searched Columns: Add database indexes to columns heavily queried in WHERE clauses.

Self-Check Challenge

What is the recommended MySQL character set for full UTF-8 and emoji support? (utf8mb4)