Connecting PHP to MySQL - CRUD, PDO, and SQL Injection

What is MySQL and How to Access It from PHP

MySQL is a relational database management system (RDBMS). This means it stores data in tables (rows and columns) and provides the SQL language to create tables, insert, query, update, and delete data. MySQL runs as a server (a separate process), and your PHP application connects to it over the network (usually at localhost, port 3306 on your machine).

Essential Terms (Brief)

MySQL Server - the program that runs and manages all databases.
Database - a logical “container” grouping tables (e.g., curs_php).
Table - a collection of rows with the same column structure (e.g., users with columns id, name, email).
Row (record) - a complete “object”/entity (e.g., a user).
Column (field) - a property of the entity (e.g., email is a field).
Primary Key (PRIMARY KEY) - a unique identifier of the row (usually id with auto-increment).
Foreign Key (FOREIGN KEY) - links rows from different tables (e.g., posts.user_id → users.id).
Index - speeds up searches on one or more columns (like a “table of contents”).
SQL Query - a command sent to the server (e.g., SELECT ...).

What is SQL (in simple terms)

SQL (Structured Query Language) is the standard language for working with relational data. It has commands for:

DDL (Data Definition Language) - schema definition: CREATE, ALTER, DROP
DML (Data Manipulation Language) - data manipulation: INSERT, SELECT, UPDATE, DELETE

Ultra-simple SQL examples

-- Create a database 
CREATE DATABASE curs_php CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Select the database
USE curs_php;

-- Create a table
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Read all users
SELECT id, name, email FROM users;

Why MySQL for PHP projects?

Popular, widely supported on hosting and local setups (XAMPP, MAMP, Laragon).
Fast for common workloads (websites, CRUD applications).
Open-source, large community, extensive documentation.

Installation & Startup (brief)

Install an all-in-one package (e.g., XAMPP / MAMP / Laragon).
Start the MySQL service from the control panel.
Access phpMyAdmin at http://localhost/phpmyadmin (optional, web UI for MySQL).
Typical local connection: host = localhost, port = 3306, user = root, password = '' (default in XAMPP). Recommended: set a password and/or create a dedicated user for your application.

How to access MySQL from PHP (options)

mysqli (procedural or OOP) - only for MySQL.
PDO (PHP Data Objects) - unified interface for multiple DBMS (MySQL, PostgreSQL, etc.), with prepared statements and modern options.

Recommendation: use PDO for portability, security, and a consistent API.

Connecting to MySQL with PDO (recommended)

The DSN format for MySQL: mysql:host=HOST;dbname=DBNAME;charset=UTF8. We also set options for error handling and fetch mode.

<?php
$host = '127.0.0.1'; // or 'localhost'
$db   = 'curs_php'; // database name
$user = 'root'; // MySQL user
$pass = ''; // password (usually empty in XAMPP)
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // fetch as associative array
    PDO::ATTR_EMULATE_PREPARES => false, // use real prepared statements
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "✅ Successfully connected to the database!";
} catch (PDOException $e) {
    echo "❌ Connection error: " . $e->getMessage();
}
?>

Separating credentials (separate config)

Do not scatter your database credentials throughout the code. Use a config.php file and include it wherever needed.

config.php

<?php
return [
    'db' => [
        'host' => '127.0.0.1',
        'name' => 'curs_php',
        'user' => 'root',
        'pass' => '',
        'charset' => 'utf8mb4',
    ],
];
?>

db.php (connection factory)

<?php
$config = require __DIR__ . '/config.php';
$db = $config['db'];

$dsn = "mysql:host={$db['host']};dbname={$db['name']};charset={$db['charset']}";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $db['user'], $db['pass'], $options);
} catch (PDOException $e) {
    exit('DB Connection Error: ' . $e->getMessage());
}
?>

test-conn.php (quick check)

<?php
require __DIR__ . '/db.php';

$version = $pdo->query('SELECT VERSION()')->fetchColumn();
echo "Connected to MySQL version: " . $version;
?>

Common errors & how to fix them

Access denied - wrong user/password or the user lacks privileges on the DB. Check in phpMyAdmin or create a dedicated user.
Unknown database - the database does not exist. Run CREATE DATABASE ... or check the name.
Connection refused - MySQL server is not running or the port differs. Start the service and verify the port.
Garbled characters - you did not set charset=utf8mb4 in DSN or the collation on DB/tables.
Silent errors - you did not enable PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION. Enable it for proper debugging.

Basic best practices (security)

Do not use root in production. Create a user with only the necessary privileges.
Do not store credentials in a public repository. Keep config.php outside the webroot or use environment variables.
Always use Prepared Statements (with $pdo->prepare()) for any user input → prevents SQL Injection.
Always set utf8mb4 for full Unicode support (including emojis).

Connecting to the database using PDO

To work with a MySQL database from PHP, the safest and most flexible method is PDO (PHP Data Objects). It provides a unified interface for multiple database types (not just MySQL), supports prepared statements (which protect against SQL Injection attacks), and provides advanced database handling methods.

Basic connection syntax

A PDO connection requires: the server name (host), database name (dbname), username, and password. It is recommended to use try...catch blocks to catch any connection errors.


// Connection credentials
$host = "localhost";
$dbname = "testdb";
$username = "root";
$password = "";

try {
    // Create PDO object
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    
    // Set error reporting mode
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "✅ Database connection successful!";
} catch (PDOException $e) {
    echo "❌ Connection failed: " . $e->getMessage();
}

Explanations

new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password) - establishes the connection to MySQL.
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) - configures PDO to throw exceptions on errors (makes it easier to debug issues).
try...catch - if an error occurs (e.g., wrong password), it will be caught and displayed via $e->getMessage().

Concrete Example: Creating the Database and Tables

Before you can store information in a PHP application, you need a database. We will use MySQL, which is already installed in the XAMPP package.

1. Starting the MySQL server

- Open the XAMPP Control Panel. - Start the Apache and MySQL modules. Now your web server and database server are running on your computer.

2. Creating the database (simple method via phpMyAdmin)

Open http://localhost/phpmyadmin in your browser. There you have a graphical interface where you can click the Databases tab and create the database traffice-test manually. This is the easiest method for beginners.

3. Creating the database from PHP

If you want to create the database directly from a PHP script, do it like this:

<?php
try {
    // Connect to MySQL (without specifying the database yet)
    $pdo = new PDO("mysql:host=localhost", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL command to create the database
    $sql = "CREATE DATABASE IF NOT EXISTS traffice-test";
    $pdo->exec($sql);

    echo "✅ Database 'traffice-test' has been successfully created.";
} catch (PDOException $e) {
    echo "❌ Error creating the database: " . $e->getMessage();
}
?>

Where to put this code?

1. Open the htdocs folder in your XAMPP directory (e.g., C:\xampp\htdocs).
2. Create a new file, for example create_db.php.
3. Paste the PHP code from above into this file.
4. In your browser, open http://localhost/create_db.php.
If everything is correct, you should see the message Database 'traffice-test' has been successfully created.

4. Verifying the database

To confirm that the database exists, go to phpMyAdmin (http://localhost/phpmyadmin) and you should see traffice-test in the left-hand list.

Creating the `users` table in the database

Once we have the traffice-test database, the next step is to create a table to store information. We will create a simple table called users with the following columns:

id - unique identifier for each user (primary key, auto-increment)
name - user's name (text)
email - email address (text, unique)

1. SQL command to create the table

This is the standard SQL command:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

2. Creating the table via a PHP script

We can execute the SQL command from a PHP file. Create a new file, for example create_table.php inside htdocs:

<?php
try {
    // Connect to the traffice-test database
    $pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Create the users table
    $sql = "CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) NOT NULL UNIQUE
            )";

    $pdo->exec($sql);

    echo "✅ The 'users' table has been successfully created.";
} catch (PDOException $e) {
    echo "❌ Error creating the table: " . $e->getMessage();
}
?>

3. How to run the script?

1. Save the file create_table.php in C:\xampp\htdocs.
2. Open your browser and navigate to http://localhost/create_table.php.
3. If everything went well, you should see the message The 'users' table has been successfully created.

4. Verifying the table

Go back to phpMyAdmin → the traffice-test database, and you should see the users table in the left-hand list. Click on it to view the columns: id, name, and email.

Explanations

AUTO_INCREMENT - each user gets a unique ID automatically.
NOT NULL - the field cannot be left empty.
UNIQUE - each email must be unique in the table (no two accounts with the same email).
IF NOT EXISTS - prevents an error if the table already exists.

CRUD Operations in PHP & MySQL

CRUD is the acronym for the four fundamental operations we can perform on data in a database: Create, Read, Update, and Delete.

1. Create

The CREATE operation means adding new records (rows) to a table. In PHP, this is done using an SQL INSERT INTO query. For example, we can add a new user to the users table with a name and email.

2. Read

The READ operation means reading and displaying existing data. In PHP, we use the SELECT query, which can return all users or only certain columns/filtered records.

3. Update

The UPDATE operation is used to modify existing data. We can update a user's name or email using an SQL UPDATE query with a WHERE condition.

4. Delete

The DELETE operation removes a record from a table. It is very important to always use a WHERE condition to avoid deleting all data.

CRUD Operations in PHP & MySQL (the `users` table)

Assume we already have the traffice-test database and the users table created. Below we demonstrate the four fundamental operations: INSERT (Create), SELECT (Read), UPDATE, DELETE. All the code is in a single file crud_demo.php.

<?php
try {
    // 1. Connect to the database

    $pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "<h2>CRUD on the users table</h2>";

    // CREATE - Add new users
    $pdo->exec("INSERT INTO users (name, email) VALUES ('John Smith', 'ion@example.com')");
    $pdo->exec("INSERT INTO users (name, email) VALUES ('Mary Ionella', 'maria@example.com')");
    echo "<p>✅ Users added.</p>";

    // READ - Display all users
    echo "<h3>Users list:</h3>";
    $stmt = $pdo->query("SELECT * FROM users");
    foreach ($stmt as $row) {
        echo $row['id'] . " - " . $row['name'] . " (" . $row['email'] . ")<br>";
    }

    // UPDATE - Change John's email
    $pdo->exec("UPDATE users SET email='ion.popescu@nou.com' WHERE name='John Smith'");
    echo "<p>✅ John's email has been updated.</p>";

    // Read again after UPDATE
    echo "<h3>After Update:</h3>";
    $stmt = $pdo->query("SELECT * FROM users");
    foreach ($stmt as $row) {
        echo $row['id'] . " - " . $row['name'] . " (" . $row['email'] . ")<br>";
    }

    // DELETE - Remove user Mary
    $pdo->exec("DELETE FROM users WHERE name='Mary Ionella'");
    echo "<p>✅ Mary has been deleted.</p>";

    // Read after DELETE
    echo "<h3>After Delete:</h3>";
    $stmt = $pdo->query("SELECT * FROM users");
    foreach ($stmt as $row) {
        echo $row['id'] . " - " . $row['name'] . " (" . $row['email'] . ")<br>";
    }

} catch (PDOException $e) {
    echo "❌ Error: " . $e->getMessage();
}
?>

Practical CRUD Example (HTML + PHP)

Below is a complete example of a simple CRUD application for the users table. It allows adding new users, displaying the user list, updating, and deleting them.

🔧 Complete example of a simple CRUD app for the users table

<?php
// db.php - database connection
$pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "traffice_php", "!@y3Ge-Z!WQI");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Add user
if (isset($_POST['add'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute([$name, $email]);
}

// Delete user
if (isset($_GET['delete'])) {
    $id = $_GET['delete'];
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$id]);
}

// Update user
if (isset($_POST['update'])) {
    $id = $_POST['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    $stmt = $pdo->prepare("UPDATE users SET name=?, email=? WHERE id=?");
    $stmt->execute([$name, $email, $id]);
}

// Read users
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html>
<head>
    <title>CRUD Demo</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        table { border-collapse: collapse; width: 60%; margin-top: 20px; }
        th, td { border: 1px solid #ccc; padding: 8px; text-align: center; }
        form { margin-bottom: 20px; }
    </style>
</head>
<body>
    <h2>Add User</h2>
    <form method="post">
        <input type="text" name="name" placeholder="Name" required>
        <input type="email" name="email" placeholder="Email" required>
        <button type="submit" name="add">Add</button>
    </form>

    <h2>User List</h2>
    <table>
        <tr>
            <th>ID</th><th>Name</th><th>Email</th><th>Actions</th>
        </tr>
        <?php foreach ($users as $user): ?>
        <tr>
            <td><?= $user['id'] ?></td>
            <td><?= $user['name'] ?></td>
            <td><?= $user['email'] ?></td>
            <td>
                <!-- Update form -->
                <form method="post" style="display:inline;">
                    <input type="hidden" name="id" value="<?= $user['id'] ?>">
                    <input type="text" name="name" value="<?= $user['name'] ?>" required>
                    <input type="email" name="email" value="<?= $user['email'] ?>" required>
                    <br><br>
                    <button type="submit" name="update">Update</button>
                </form>
                <!-- Delete link -->
                <br>
                <a href="?delete=<?= $user['id'] ?>" onclick="return confirm('Are you sure you want to delete?')">Delete</a>
            </td>
        </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>

Prepared Statements in PHP (Preventing SQL Injection)

A common issue in web applications is SQL Injection, where malicious SQL code is intentionally inserted into input fields to manipulate the database. If we do not validate and secure user-submitted data, the application can be compromised.

Example of a vulnerable query (Do NOT do this!)

<?php
// Unsafe example (DO NOT use!)
$name = $_POST['name'];
$sql = "SELECT * FROM users WHERE name = '$name'";
$stmt = $pdo->query($sql); // Vulnerable to SQL Injection
?>

If a user enters the value ' OR '1'='1 in the name field, the query becomes:


SELECT * FROM users WHERE name = '' OR '1'='1';

Result? All users in the database will be returned! ⚠️

The solution: Prepared Statements

To prevent SQL Injection attacks, use prepared statements. These separate the SQL logic from user-provided data. Values are treated strictly as data, not SQL instructions.

Correct example with PDO (anonymous parameters)

<?php
// Using PDO with ? placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$_POST['name']]);
$results = $stmt->fetchAll();

foreach ($results as $row) {
    echo $row['id'] . " - " . $row['name'] . " - " . $row['email'] . "<br>";
}
?>

Correct example with PDO (named parameters)

<?php
// Using PDO with named placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
$stmt->execute();
$results = $stmt->fetchAll();

foreach ($results as $row) {
    echo $row['id'] . " - " . $row['name'] . " - " . $row['email'] . "<br>";
}
?>

Notice that now, even if a user tries to input malicious text, it will be treated as plain text and cannot alter the SQL query.

Complete CRUD example with Prepared Statements (Table `users`)

Now that we understand the importance of security, we will implement all CRUD operations (Create, Read, Update, Delete) using Prepared Statements. The example uses the users table with the following fields: id (AUTO_INCREMENT, PRIMARY KEY), name, email.

PHP + HTML Code for CRUD


<?php
// Database connection
$pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// CREATE: Insert new user
if (isset($_POST['add'])) {
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
    $stmt->execute([
        ':name' => $_POST['name'],
        ':email' => $_POST['email']
    ]);
}

// UPDATE: Modify user
if (isset($_POST['update'])) {
    $stmt = $pdo->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id");
    $stmt->execute([
        ':name' => $_POST['name'],
        ':email' => $_POST['email'],
        ':id' => $_POST['id']
    ]);
}

// DELETE: Remove user
if (isset($_POST['delete'])) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
    $stmt->execute([':id' => $_POST['id']]);
}

// READ: Display all users
$stmt = $pdo->query("SELECT * FROM users ORDER BY id ASC");
$users = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>CRUD Demo</title>
    <style>
        table { border-collapse: collapse; width: 50%; margin-top: 20px; }
        table, th, td { border: 1px solid black; padding: 8px; text-align: left; }
        form { margin-top: 20px; }
    </style>
</head>
<body>
    <h2>User Management</h2>

    <!-- Add User Form -->
    <h3>Add User</h3>
    <form method="post">
        Name: <input type="text" name="name" required>
        Email: <input type="email" name="email" required>
        <button type="submit" name="add">Add</button>
    </form>

    <!-- Users Table -->
    <h3>Users List</h3>
    <table>
        <tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>
        <?php foreach ($users as $user): ?>
            <tr>
                <td><?= $user['id'] ?></td>
                <td><?= htmlspecialchars($user['name']) ?></td>
                <td><?= htmlspecialchars($user['email']) ?></td>
                <td>
                    <!-- UPDATE Form -->
                    <form method="post" style="display:inline;">
                        <input type="hidden" name="id" value="<?= $user['id'] ?>">
                        Name: <input type="text" name="name" value="<?= htmlspecialchars($user['name']) ?>" required>
                        Email: <input type="email" name="email" value="<?= htmlspecialchars($user['email']) ?>" required>
                        <button type="submit" name="update">Update</button>
                    </form>

                    <!-- DELETE Form -->
                    <form method="post" style="display:inline;">
                        <input type="hidden" name="id" value="<?= $user['id'] ?>">
                        <button type="submit" name="delete" onclick="return confirm('Delete this user?')">Delete</button>
                    </form>
                </td>
            </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>

🔧 PHP + HTML code for CRUD

<?php
// Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "traffice_php", "!@y3Ge-Z!WQI");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// CREATE: Insert new user
if (isset($_POST['add'])) {
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
    $stmt->execute([
        ':name' => $_POST['name'],
        ':email' => $_POST['email']
    ]);
}

// UPDATE: Update user
if (isset($_POST['update'])) {
    $stmt = $pdo->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id");
    $stmt->execute([
        ':name' => $_POST['name'],
        ':email' => $_POST['email'],
        ':id' => $_POST['id']
    ]);
}

// DELETE: Delete user
if (isset($_POST['delete'])) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
    $stmt->execute([':id' => $_POST['id']]);
}

// READ: Display all users
$stmt = $pdo->query("SELECT * FROM users ORDER BY id ASC");
$users = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>CRUD Demo</title>
    <style>
        table { border-collapse: collapse; width: 50%; margin-top: 20px; }
        table, th, td { border: 1px solid black; padding: 8px; text-align: left; }
        form { margin-top: 20px; }
    </style>
</head>
<body>
    <h2>User Management</h2>

    <!-- Add user form -->
    <h3>Add User</h3>
    <form method="post">
        Name: <input type="text" name="name" required>
        Email: <input type="email" name="email" required>
        <button type="submit" name="add">Add</button>
    </form>

    <!-- Users table -->
    <h3>User List</h3>
    <table>
        <tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>
        <?php foreach ($users as $user): ?>
            <tr>
                <td><?= $user['id'] ?></td>
                <td><?= htmlspecialchars($user['name']) ?></td>
                <td><?= htmlspecialchars($user['email']) ?></td>
                <td>
                    <!-- UPDATE form -->
                    <form method="post" style="display:inline;">
                        <input type="hidden" name="id" value="<?= $user['id'] ?>">
                        Name: <input type="text" name="name" value="<?= htmlspecialchars($user['name']) ?>" required>
                        Email: <input type="email" name="email" value="<?= htmlspecialchars($user['email']) ?>" required>
                        <button type="submit" name="update">Update</button>
                    </form>

                    <!-- DELETE form -->
                    <form method="post" style="display:inline;">
                        <input type="hidden" name="id" value="<?= $user['id'] ?>">
                        <button type="submit" name="delete" onclick="return confirm('Delete this user?')">Delete</button>
                    </form>
                </td>
            </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>

Table Relationships (Users - Posts)

In a real-world application, database tables are rarely isolated. Most of the time, they are related to each other via primary and foreign keys. For example, in a blog application, we have:

users - stores information about users (id, name, email);
posts - stores articles or messages published by users.

The relationship between these two tables is one-to-many (1:N) — one user can have multiple posts. This link is created through a user_id column in the posts table, which references the id column in the users table.

Creating the `posts` Table

You can create the table either in phpMyAdmin with simple SQL or directly via a PHP script. In phpMyAdmin, you can run the following code:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Or you can run it from a PHP file:

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "CREATE TABLE IF NOT EXISTS posts (
              id INT AUTO_INCREMENT PRIMARY KEY,
              user_id INT NOT NULL,
              title VARCHAR(100) NOT NULL,
              content TEXT NOT NULL,
              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )";
    $pdo->exec($sql);

    echo "✅ Table 'posts' has been created.";
} catch (PDOException $e) {
    echo "❌ Error: " . $e->getMessage();
}
?>

Inserting Test Data

In phpMyAdmin, run the following code:

First, insert the users into the users table:

INSERT INTO users (name, email) VALUES
('John Smith', 'ion.popescu@example.com'),
('Mary Ionella', 'maria.ionescu@example.com');

Then, insert the posts linked to those users:

INSERT INTO posts (user_id, title, content) VALUES
(1, 'First Article', 'This is the first post written by user 1.'),
(1, 'Second Article', 'User 1 has another post.'),
(2, 'Hello!', 'Message written by user 2.');

Displaying Posts Along with Users

You can use a JOIN to fetch both the user information and their posts in a single query.

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT posts.id, posts.title, posts.content, posts.created_at, users.name, users.email
            FROM posts
            INNER JOIN users ON posts.user_id = users.id
            ORDER BY posts.created_at DESC";
    $stmt = $pdo->query($sql);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<h4>" . htmlspecialchars($row['title']) . "</h4>";
        echo "<p>" . htmlspecialchars($row['content']) . "</p>";
        echo "<small>Written by: " . htmlspecialchars($row['name']) . " (" . htmlspecialchars($row['email']) . ") on " . $row['created_at'] . "</small><hr>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

🔧 PHP Example: Displaying Posts Along with Users

<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "traffice_php", "!@y3Ge-Z!WQI");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT posts.id, posts.title, posts.content, posts.created_at, users.name, users.email
            FROM posts
            INNER JOIN users ON posts.user_id = users.id
            ORDER BY posts.created_at DESC";
    $stmt = $pdo->query($sql);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<h4>" . htmlspecialchars($row['title']) . "</h4>";
        echo "<p>" . htmlspecialchars($row['content']) . "</p>";
        echo "<small>Written by: " . htmlspecialchars($row['name']) . " (" . htmlspecialchars($row['email']) . ") la " . $row['created_at'] . "</small><hr>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Live Demo: Users & Posts

This example shows how we can add users and posts and display them together.

<?php
// Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Add user
if(isset($_POST['add_user'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];
    if($name && $email) {
        $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
        $stmt->execute([$name, $email]);
        echo "<p style='color:green;'>✅ User added!</p>";
    }
}

// Delete user
if(isset($_GET['delete_user'])) {
    $id = (int)$_GET['delete_user'];
    $stmt = $pdo->prepare("DELETE FROM users WHERE id=?");
    $stmt->execute([$id]);
    echo "<p style='color:red;'>🗑️ User deleted!</p>";
}

// Add post
if(isset($_POST['add_post'])) {
    $user_id = $_POST['user_id'];
    $title = $_POST['title'];
    $content = $_POST['content'];
    if($user_id && $title && $content) {
        $stmt = $pdo->prepare("INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)");
        $stmt->execute([$user_id, $title, $content]);
        echo "<p style='color:green;'>✅ Post added!</p>";
    }
}

// Delete post
if(isset($_GET['delete_post'])) {
    $id = (int)$_GET['delete_post'];
    $stmt = $pdo->prepare("DELETE FROM posts WHERE id=?");
    $stmt->execute([$id]);
    echo "<p style='color:red;'>🗑️ Post deleted!</p>";
}

// Fetch users for the form
$users = $pdo->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_ASSOC);
?>

<h3 class="subtitle">Add New User</h3>
<form method="post">
    Name: <input type="text" name="name" required>
    Email: <input type="email" name="email" required>
    <button type="submit" name="add_user">Add User</button>
</form>

<h3 class="subtitle">Add New Post</h3>
<form method="post">
    User:
    <select name="user_id" required>
        <?php foreach($users as $u) { ?>
            <option value="<?= $u['id'] ?>"><?= htmlspecialchars($u['name']) ?></option>
        <?php } ?>
    </select>
    Title: <input type="text" name="title" required>
    Content: <textarea name="content" required></textarea>
    <button type="submit" name="add_post">Add Post</button>
</form>

<h3 class="subtitle">User List</h3>
<table>
    <tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>
    <?php
    $stmt = $pdo->query("SELECT id, name, email FROM users");
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr><td>".$row['id']."</td><td>".htmlspecialchars($row['name'])."</td><td>".htmlspecialchars($row['email'])."</td><td><a href='?delete_user=".$row['id']."' onclick='return confirm(\"Are you sure?\")'>Delete</a></td></tr>";
    }
    ?>
</table>

<h3 class="subtitle">User Posts</h3>
<?php
$sql = "SELECT posts.id, posts.title, posts.content, users.name, users.email, posts.created_at
        FROM posts
        INNER JOIN users ON posts.user_id = users.id
        ORDER BY posts.created_at DESC";
$stmt = $pdo->query($sql);

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<h4>".htmlspecialchars($row['title'])."</h4>";
    echo "<p>".htmlspecialchars($row['content'])."</p>";
    echo "<small>Written by: ".htmlspecialchars($row['name'])." (".htmlspecialchars($row['email']).") on ".$row['created_at']."</small>";
    echo " <a href='?delete_post=".$row['id']."' onclick='return confirm(\"Are you sure?\")'>🗑️ Delete</a><hr>";
}
?>

🔧 PHP Example: Live Demo - Users & Posts

<?php
// Connect to database
$pdo = new PDO("mysql:host=localhost;dbname=traffice-test", "traffice_php", "!@y3Ge-Z!WQI");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Add user
if(isset($_POST['add_user'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];
    if($name && $email) {
        $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
        $stmt->execute([$name, $email]);
        echo "<p style='color:green;'>✅ User added!</p>";
    }
}

// Delete user
if(isset($_GET['delete_user'])) {
    $id = (int)$_GET['delete_user'];
    $stmt = $pdo->prepare("DELETE FROM users WHERE id=?");
    $stmt->execute([$id]);
    echo "<p style='color:red;'>🗑️ User deleted!</p>";
}

// Add post
if(isset($_POST['add_post'])) {
    $user_id = $_POST['user_id'];
    $title = $_POST['title'];
    $content = $_POST['content'];
    if($user_id && $title && $content) {
        $stmt = $pdo->prepare("INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)");
        $stmt->execute([$user_id, $title, $content]);
        echo "<p style='color:green;'>✅ Post added!</p>";
    }
}

// Delete post
if(isset($_GET['delete_post'])) {
    $id = (int)$_GET['delete_post'];
    $stmt = $pdo->prepare("DELETE FROM posts WHERE id=?");
    $stmt->execute([$id]);
    echo "<p style='color:red;'>🗑️ Post deleted!</p>";
}

// Fetch users for form
$users = $pdo->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_ASSOC);
?>

<h3 class="subtitle">Add New User</h3>
<form method="post">
    Name: <input type="text" name="name" required>
    Email: <input type="email" name="email" required>
    <button type="submit" name="add_user">Add User</button>
</form>

<h3 class="subtitle">Add New Post</h3>
<form method="post">
    User:
    <select name="user_id" required>
        <?php foreach($users as $u) { ?>
            <option value="<?= $u['id'] ?>"><?= htmlspecialchars($u['name']) ?></option>
        <?php } ?>
    </select>
    Title: <input type="text" name="title" required>
    Content: <textarea name="content" required></textarea>
    <button type="submit" name="add_post">Add Post</button>
</form>

<h3 class="subtitle">User List</h3>
<table>
    <tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>
    <?php
    $stmt = $pdo->query("SELECT id, name, email FROM users");
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr><td>".$row['id']."</td><td>".htmlspecialchars($row['name'])."</td><td>".htmlspecialchars($row['email'])."</td><td><a href='?delete_user=".$row['id']."' onclick='return confirm(\"Are you sure you want to delete?\")'>Delete</a></td></tr>";
    }
    ?>
</table>

<h3 class="subtitle">User Posts</h3>
<?php
$sql = "SELECT posts.id, posts.title, posts.content, users.name, users.email, posts.created_at
        FROM posts
        INNER JOIN users ON posts.user_id = users.id
        ORDER BY posts.created_at DESC";
$stmt = $pdo->query($sql);

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<h4>".htmlspecialchars($row['title'])."</h4>";
    echo "<p>".htmlspecialchars($row['content'])."</p>";
    echo "<small>Written by: ".htmlspecialchars($row['name'])." (".htmlspecialchars($row['email']).") on ".$row['created_at']."</small>";
    echo " <a href='?delete_post=".$row['id']."' onclick='return confirm(\"Are you sure you want to delete?\")'>🗑️ Delete</a><hr>";
}
?>

<style>
form { margin-bottom: 1rem; padding: 0.5rem; border: 1px solid #ccc; border-radius: 5px; }
input, select, textarea { margin: 0.25rem 0; padding: 0.25rem; width: 100%; }
button { margin-top: 0.25rem; padding: 0.5rem 1rem; cursor: pointer; }
table { border-collapse: collapse; margin-top: 1rem; width: 100%; }
th, td { border: 1px solid #ccc; padding: 0.5rem; text-align: left; }
h3.subtitle { margin-top: 1.5rem; }
</style>

🧠 Quiz - PHP & MySQL: Databases

Arrays in PHP ➔

What is MySQL and How to Access It from PHP

Essential Terms (Brief)

What is SQL (in simple terms)

Ultra-simple SQL examples

Why MySQL for PHP projects?

Installation & Startup (brief)

How to access MySQL from PHP (options)

Connecting to MySQL with PDO (recommended)

Separating credentials (separate config)

config.php

db.php (connection factory)

test-conn.php (quick check)

Common errors & how to fix them

Basic best practices (security)

Connecting to the database using PDO

Basic connection syntax

Explanations

Concrete Example: Creating the Database and Tables

1. Starting the MySQL server

2. Creating the database (simple method via phpMyAdmin)

3. Creating the database from PHP

Where to put this code?

4. Verifying the database

Creating the users table in the database

1. SQL command to create the table

2. Creating the table via a PHP script

3. How to run the script?

4. Verifying the table

Explanations

CRUD Operations in PHP & MySQL

1. Create

2. Read

3. Update

4. Delete

CRUD Operations in PHP & MySQL (the users table)

Practical CRUD Example (HTML + PHP)

Prepared Statements in PHP (Preventing SQL Injection)

Example of a vulnerable query (Do NOT do this!)

The solution: Prepared Statements

Correct example with PDO (anonymous parameters)

Correct example with PDO (named parameters)

Complete CRUD example with Prepared Statements (Table users)

PHP + HTML Code for CRUD

Table Relationships (Users - Posts)

Creating the posts Table

Inserting Test Data

Displaying Posts Along with Users

Live Demo: Users & Posts

🧠 Quiz - PHP & MySQL: Databases

Cookie Policy

Creating the `users` table in the database

CRUD Operations in PHP & MySQL (the `users` table)

Complete CRUD example with Prepared Statements (Table `users`)

Creating the `posts` Table