Latest
PHP Tutorial RoadmapOOP Tutorial RoadmapDesign Patterns Tutorial RoadmapDesign Patterns in Real ProjectsDTO PatternService Layer PatternMVC PatternDependency InjectionCommand PatternObserver PatternPHP Tutorial RoadmapOOP Tutorial RoadmapDesign Patterns Tutorial RoadmapDesign Patterns in Real ProjectsDTO PatternService Layer PatternMVC PatternDependency InjectionCommand PatternObserver Pattern

MySQL Database with PHP

This article explains how to use MySQL with PHP, including database connection, creating databases and tables, inserting data, getting the last inserted ID, prepared statements, selecting, filtering, ordering, updating, deleting, and limiting data.

Jun 10, 2026
#php
MySQL Database with PHP

MySQL Database with PHP: Connect, Create, Insert, Select, Update and Delete

MySQL is one of the most common database systems used with PHP. Many dynamic websites and web applications need a database to store users, posts, products, orders, comments, settings, and other application data.

PHP can connect to MySQL and perform database operations such as creating databases, creating tables, inserting data, selecting records, filtering results, updating rows, deleting data, and limiting returned records.

This article explains the main MySQL topics shown in the learning path: MySQL database, MySQL connect, create database, create table, insert data, get last inserted ID, insert multiple records, prepared statements, select data, where, order by, delete data, update data, and limit data.

MySQL Database

A database is a structured place for storing data. In web development, databases are used to keep information that must remain available even after the page is closed or the server request ends.

For example, a blog website may store articles, categories, tags, users, and comments in a MySQL database. An e-commerce website may store products, customers, carts, orders, invoices, and payments.

MySQL stores data inside tables. A table contains rows and columns. Each row represents one record, while each column represents a specific field.

For example, a users table may contain columns such as:

  • id: the unique identifier of the user.

  • name: the user's name.

  • email: the user's email address.

  • password: the user's hashed password.

  • created_at: the creation date of the record.

PHP can communicate with MySQL using extensions such as MySQLi or PDO. PDO is often preferred in modern PHP because it supports multiple database systems and provides a clean interface for prepared statements.

MySQL Connect

Before PHP can work with a MySQL database, it must create a connection. A connection tells PHP which database server to use and which username, password, and database name should be used.

The following example uses PDO to connect PHP with MySQL.

<?php
$host = "localhost";
$dbname = "php_learning";
$username = "root";
$password = "";

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

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

    echo "Connected successfully.";
} catch (PDOException $exception) {
    echo "Connection failed: " . $exception->getMessage();
}
?>

The try and catch blocks are used to handle connection errors. If the connection fails, PHP catches the exception and displays an error message.

The charset=utf8mb4 part is important because it helps the database support many characters, including Arabic, Turkish, English, and emojis.

In real applications, database credentials should usually be stored in a configuration file or environment file, not directly inside every PHP page.

MySQL Create DB

Creating a database means creating the main container where tables and records will be stored. This is usually done once during project setup.

The following example creates a database using PDO.

<?php
$host = "localhost";
$username = "root";
$password = "";

try {
    $pdo = new PDO("mysql:host=$host;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "CREATE DATABASE php_learning CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

    $pdo->exec($sql);

    echo "Database created successfully.";
} catch (PDOException $exception) {
    echo "Error: " . $exception->getMessage();
}
?>

The database name in this example is php_learning. The character set and collation are set to support multilingual text correctly.

In professional projects, databases are often created manually through a database tool, hosting panel, command line, or migrations in a framework such as Laravel.

MySQL Create Table

After creating a database, the next step is creating tables. A table defines the structure of the data that will be stored.

The following example creates a simple users table.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
        "root",
        ""
    );

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

    $sql = "
        CREATE TABLE users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(150) NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ";

    $pdo->exec($sql);

    echo "Table created successfully.";
} catch (PDOException $exception) {
    echo "Error: " . $exception->getMessage();
}
?>

The id column is an auto-incrementing primary key. This means MySQL automatically creates a new unique ID for each inserted row.

The name and email columns store text. The NOT NULL rule means the field cannot be empty. The UNIQUE rule means the same email cannot be repeated.

The created_at column stores the date and time when the row is created.

MySQL Insert Data

Inserting data means adding new records to a table. For example, when a user registers, PHP inserts the user's name and email into the users table.

The following example inserts one user record.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
        "root",
        ""
    );

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

    $sql = "INSERT INTO users (name, email) VALUES ('Adnan', 'adnan@example.com')";

    $pdo->exec($sql);

    echo "New user inserted successfully.";
} catch (PDOException $exception) {
    echo "Error: " . $exception->getMessage();
}
?>

This example works, but it is not the safest way when values come from users. User input should not be placed directly inside SQL strings. Prepared statements should be used instead to help prevent SQL injection.

Direct insert statements are useful for understanding the basic idea, but prepared statements are the recommended approach for real applications.

MySQL Get Last ID

After inserting a new row into a table with an auto-incrementing ID, you may need to know the ID of the newly created record.

PHP PDO provides the lastInsertId() method for this purpose.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
        "root",
        ""
    );

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

    $sql = "INSERT INTO users (name, email) VALUES ('Noor', 'noor@example.com')";

    $pdo->exec($sql);

    $lastId = $pdo->lastInsertId();

    echo "New record created with ID: " . $lastId;
} catch (PDOException $exception) {
    echo "Error: " . $exception->getMessage();
}
?>

Getting the last inserted ID is useful when creating related records. For example, after creating an order, you may use the order ID to insert order items into another table.

MySQL Insert Multiple

Sometimes you need to insert more than one row. This can happen when importing data, adding multiple products, creating default settings, or saving many related records.

One way is to execute several insert statements inside a transaction.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
        "root",
        ""
    );

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

    $pdo->beginTransaction();

    $pdo->exec("INSERT INTO users (name, email) VALUES ('Ali', 'ali@example.com')");
    $pdo->exec("INSERT INTO users (name, email) VALUES ('Sara', 'sara@example.com')");
    $pdo->exec("INSERT INTO users (name, email) VALUES ('Omar', 'omar@example.com')");

    $pdo->commit();

    echo "Multiple users inserted successfully.";
} catch (PDOException $exception) {
    $pdo->rollBack();

    echo "Error: " . $exception->getMessage();
}
?>

A transaction helps keep the data consistent. If one insert fails, the transaction can roll back the changes instead of saving only part of the data.

For user-provided data, prepared statements should be used when inserting multiple records.

MySQL Prepared

Prepared statements are one of the most important concepts when using MySQL with PHP. They help protect applications from SQL injection by separating SQL structure from user input.

Instead of inserting values directly into the SQL string, placeholders are used. Then the values are passed separately.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
        "root",
        ""
    );

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

    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";

    $statement = $pdo->prepare($sql);

    $statement->execute([
        "name" => "Adnan",
        "email" => "adnan@example.com"
    ]);

    echo "User inserted with prepared statement.";
} catch (PDOException $exception) {
    echo "Error: " . $exception->getMessage();
}
?>

The placeholders :name and :email are replaced safely by PDO when the statement is executed.

Prepared statements should be used for insert, update, delete, and select queries whenever user input is involved.

They are especially important in login forms, search forms, profile updates, admin panels, APIs, and any page that receives request data.

MySQL Select Data

Selecting data means reading records from a database table. The SELECT statement is used to retrieve data.

The following example selects all users from the users table.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
        "root",
        ""
    );

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

    $statement = $pdo->query("SELECT id, name, email, created_at FROM users");

    $users = $statement->fetchAll(PDO::FETCH_ASSOC);

    foreach ($users as $user) {
        echo $user["name"] . " - " . $user["email"];
    }
} catch (PDOException $exception) {
    echo "Error: " . $exception->getMessage();
}
?>

The fetchAll(PDO::FETCH_ASSOC) method returns the result as an array of associative arrays. This makes it easy to access columns by name.

Select queries are used in almost every dynamic website, such as showing blog posts, product lists, user profiles, categories, comments, and search results.

MySQL Where

The WHERE clause is used to filter records. Instead of selecting all rows, you can select only rows that match a condition.

For example, the following query selects one user by email.

<?php
$email = "adnan@example.com";

$statement = $pdo->prepare("SELECT id, name, email FROM users WHERE email = :email");

$statement->execute([
    "email" => $email
]);

$user = $statement->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo $user["name"];
} else {
    echo "User not found.";
}
?>

The WHERE clause can use many operators, such as =, >, <, >=, <=, !=, LIKE, IN, and BETWEEN.

<?php
$statement = $pdo->prepare("SELECT * FROM users WHERE name LIKE :keyword");

$statement->execute([
    "keyword" => "%ad%"
]);

$results = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

WHERE is commonly used for search, filtering, login checks, profile pages, permissions, and finding specific records.

MySQL Order By

The ORDER BY clause is used to sort query results. You can sort records in ascending order using ASC or descending order using DESC.

The following example selects users and orders them by creation date from newest to oldest.

<?php
$statement = $pdo->query("SELECT id, name, email, created_at FROM users ORDER BY created_at DESC");

$users = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo $user["name"] . " - " . $user["created_at"];
}
?>

You can also order by name alphabetically.

<?php
$statement = $pdo->query("SELECT id, name, email FROM users ORDER BY name ASC");

$users = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

ORDER BY is commonly used in dashboards, blog lists, product pages, reports, tables, and admin panels.

MySQL Delete Data

Deleting data means removing records from a table. The DELETE statement is used for this operation.

It is very important to use a WHERE clause when deleting records. Without WHERE, all rows in the table may be deleted.

<?php
$id = 5;

$statement = $pdo->prepare("DELETE FROM users WHERE id = :id");

$statement->execute([
    "id" => $id
]);

echo "User deleted successfully.";
?>

In real applications, delete actions should usually be protected with permissions, confirmation messages, and sometimes soft delete logic.

Soft delete means the record is not physically removed from the database. Instead, it is marked as deleted using a column such as deleted_at. This allows the system to restore the record later if needed.

MySQL Update Data

Updating data means changing existing records in a table. The UPDATE statement is used for this operation.

Like delete queries, update queries should usually include a WHERE clause to avoid updating all rows by mistake.

<?php
$id = 1;
$name = "Adnan Mehrat";
$email = "adnan.mehrat@example.com";

$statement = $pdo->prepare("
    UPDATE users
    SET name = :name, email = :email
    WHERE id = :id
");

$statement->execute([
    "id" => $id,
    "name" => $name,
    "email" => $email
]);

echo "User updated successfully.";
?>

Update queries are used in profile edit forms, admin panels, settings pages, order status updates, password changes, and content management systems.

Prepared statements are strongly recommended for update queries because the new values often come from user input.

MySQL Limit Data

The LIMIT clause is used to restrict the number of records returned by a query. This is useful when you do not want to load all records at once.

For example, the following query returns only five users.

<?php
$statement = $pdo->query("SELECT id, name, email FROM users ORDER BY id DESC LIMIT 5");

$users = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

LIMIT is commonly used for recent posts, latest users, latest orders, dashboard widgets, and pagination.

For pagination, LIMIT is usually used with OFFSET.

<?php
$page = 2;
$perPage = 10;
$offset = ($page - 1) * $perPage;

$statement = $pdo->prepare("
    SELECT id, name, email
    FROM users
    ORDER BY id DESC
    LIMIT :limit OFFSET :offset
");

$statement->bindValue(":limit", $perPage, PDO::PARAM_INT);
$statement->bindValue(":offset", $offset, PDO::PARAM_INT);

$statement->execute();

$users = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

When using LIMIT and OFFSET with prepared statements, it is better to bind values as integers using PDO::PARAM_INT.

Complete MySQL CRUD Example

CRUD stands for Create, Read, Update, and Delete. These are the main database operations used in most web applications.

The following simplified example shows the general idea of CRUD using PDO prepared statements.

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=php_learning;charset=utf8mb4",
    "root",
    ""
);

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

// Create
$create = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$create->execute([
    "name" => "Adnan",
    "email" => "adnan@example.com"
]);

// Read
$read = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$read->execute([
    "email" => "adnan@example.com"
]);
$user = $read->fetch(PDO::FETCH_ASSOC);

// Update
$update = $pdo->prepare("UPDATE users SET name = :name WHERE email = :email");
$update->execute([
    "name" => "Adnan Mehrat",
    "email" => "adnan@example.com"
]);

// Delete
$delete = $pdo->prepare("DELETE FROM users WHERE email = :email");
$delete->execute([
    "email" => "adnan@example.com"
]);
?>

This example shows the basic database workflow used in many applications. In real projects, this logic is usually organized inside classes, repositories, models, services, or framework structures.

Security Notes for MySQL with PHP

Working with databases requires careful security practices. Database queries often use user input, so unsafe code can lead to SQL injection, data leaks, or damaged records.

Important security practices include:

  • Use prepared statements for all user input.

  • Do not place raw user input directly inside SQL queries.

  • Validate and sanitize form data before using it.

  • Escape output when displaying database content in HTML.

  • Use strong database passwords in production.

  • Do not expose database errors to public users.

  • Use proper permissions for database users.

  • Backup important databases regularly.

Frameworks such as Laravel provide tools such as migrations, Eloquent models, query builders, validation, and database configuration. However, understanding raw PHP and MySQL is important because it helps developers understand what happens behind the framework.

Conclusion

MySQL is a powerful database system commonly used with PHP to build dynamic and data-driven web applications. By learning how to connect to MySQL, create databases and tables, insert data, get the last inserted ID, insert multiple records, use prepared statements, select data, filter with WHERE, order results, update records, delete data, and limit results, you understand the foundation of database-driven PHP development.

These concepts are used in almost every real PHP project, including blogs, dashboards, admin panels, e-commerce systems, APIs, and user management systems.

After learning these MySQL basics, the next step is to practice by building small CRUD applications and then move toward cleaner architecture using PDO classes, repositories, MVC structure, or frameworks such as Laravel.

Most Viewed Articles

PHP Tutorial Roadmap
PHP Tutorial Roadmap

A complete ordered roadmap for learning PHP step by step, from PHP basics to forms, MySQL, security, OOP, MVC, Composer, APIs, file uploads, and PHP frameworks. This index article connects all PHP tutorials in one organized guide with direct links.

Read More Jun 10, 2026
OOP Tutorial Roadmap
OOP Tutorial Roadmap

A complete ordered roadmap for learning Object-Oriented Programming step by step, from basic OOP concepts to advanced topics and best practices. This index article connects all OOP tutorials in one organized guide with direct links.

Read More Jun 10, 2026