October 10, 2026

PHP and MySQL Database Connectivity – Complete Beginner’s Guide

Meta Title: Core PHP Module 9: PHP and MySQL Database Connectivity

Meta Description: Learn PHP and MySQL database connectivity step by step. Create databases and tables, connect using PDO, insert, retrieve, update, and delete records with practical examples.

Focus Keyword: PHP and MySQL database connectivity

Secondary Keywords: PHP MySQL connection, PHP PDO tutorial, connect PHP to MySQL, PHP CRUD operations, Core PHP tutorial for beginners

URL Slug: php-mysql-database-connectivity

---

Introduction

Welcome to Module 9 of our Core PHP tutorial series!

In the previous module, we learned how to handle HTML forms in PHP, collect user input, and validate submitted data. However, there is one important question: Where can we store the information submitted through a form?

For example, when a student registers on a website, their name, email address, and course selection should be stored somewhere so that the information remains available after the page is refreshed or the browser is closed.

This is where a database becomes useful.

In this tutorial, you will learn how to connect PHP with MySQL, create a database and table, insert records, retrieve and display data, update records, and delete records.

By the end of this module, you will have the foundation needed to build database-driven PHP applications.

1. What Is a Database?

A database is an organized collection of information that can be stored, accessed, updated, and managed.

Consider a student registration system. It may need to store the following information:

ID	Name	Email	Course
1	Rahul Sharma	rahul@example.com	PHP
2	Priya Verma	priya@example.com	MySQL
3	Amit Kumar	amit@example.com	Web Development

Without a database, you might have to store information in files or hard-code it into your PHP program. That approach quickly becomes difficult to manage.

A database allows you to save and retrieve information whenever it is needed.

What is MySQL?

MySQL is a relational database management system. It organizes information into tables consisting of rows and columns.

What is PHP?

PHP is a server-side programming language that can process requests, validate data, communicate with a database, and generate HTML responses.

How do PHP and MySQL work together?

The basic process is:

1. A user submits a form.

2. PHP receives the submitted information.

3. PHP validates the information.

4. PHP sends a database query to MySQL.

5. MySQL performs the requested operation.

6. PHP displays the result to the user.

For example, when a student submits a registration form, PHP can save the student's details in a MySQL table.

2. Requirements for PHP and MySQL Connectivity

Before starting, make sure you have the following:

• XAMPP installed on your computer.

• Apache and MySQL services running.

• A web browser.

• A text editor or code editor, such as VS Code.

• Basic knowledge of PHP variables, forms, arrays, and functions.

XAMPP usually includes PHP, Apache, MySQL-compatible MariaDB, and phpMyAdmin. Depending on your XAMPP version, the database service may be MariaDB rather than Oracle MySQL. The examples in this tutorial work with both for the features used here.

Start XAMPP

1. Open the XAMPP Control Panel.

2. Start Apache.

3. Start MySQL.

4. Open your browser.

5. Visit http://localhost/.

To open phpMyAdmin, visit:

http://localhost/phpmyadmin/

We will use phpMyAdmin to create the database and table.

3. Create a MySQL Database

A database is where we will store the student information.

Follow these steps:

1. Open phpMyAdmin.

2. Select the SQL tab.

3. Enter the following SQL statement.

4. Click Go.

CREATE DATABASE student_db;

This creates a database named student_db.

You can also select the New option in phpMyAdmin and create the database through its interface.

Select the database

When running SQL directly, you can select the database using:

USE student_db;

The database is now ready for us to create a table.

4. Create a Students Table

A table stores related information in rows and columns.

Run the following SQL query in phpMyAdmin:

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    course VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's understand the important parts.

• id: A unique identifier for each student.

• INT UNSIGNED: Stores a non-negative integer.

• AUTO_INCREMENT: Automatically generates a new ID.

• PRIMARY KEY: Uniquely identifies each record.

• name: Stores the student's name.

• email: Stores the student's email address.

• course: Stores the selected course.

• NOT NULL: Requires a value for the specified column.

• created_at: Stores the time when the record was created.

For this beginner project, we will use the table to perform CRUD operations.

CRUD stands for:

• Create: Insert a new record.

• Read: Retrieve records.

• Update: Modify an existing record.

• Delete: Remove a record.

5. Understand PDO in PHP

PHP provides different ways to communicate with MySQL, including MySQLi and PDO.

In this tutorial, we will use PDO (PHP Data Objects) because it provides a consistent interface for working with several database systems and supports prepared statements.

Create a project folder named php_mysql_demo inside your XAMPP htdocs directory.

For example, on a typical Windows installation:

C:\xampp\htdocs\php_mysql_demo

Inside this folder, create a file named db.php.

6. Connect PHP to MySQL Using PDO

Add the following code to db.php:

<?php

$host = "localhost";
$dbname = "student_db";
$username = "root";
$password = "";

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

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

    $pdo->setAttribute(
        PDO::ATTR_DEFAULT_FETCH_MODE,
        PDO::FETCH_ASSOC
    );

} catch (PDOException $e) {
    error_log($e->getMessage());

    http_response_code(500);
    exit("Database connection failed. Please try again later.");
}

Explanation of the code

1. Database host

$host = "localhost";

This tells PHP that the database server is running on the same computer.

2. Database name

$dbname = "student_db";

This identifies the database we created earlier.

3. Database username and password

$username = "root";
$password = "";

These are common default settings for a local XAMPP installation, but your configuration may be different.

Do not use an empty password or the root account for a production website.

4. Create the PDO connection

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

This creates the database connection. The utf8mb4 character set supports a broad range of Unicode characters.

5. Enable exception-based error handling

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

This allows database errors to be reported as exceptions that we can handle.

6. Set the default fetch mode

$pdo->setAttribute(
    PDO::ATTR_DEFAULT_FETCH_MODE,
    PDO::FETCH_ASSOC
);

This makes retrieved records available as associative arrays using column names as keys.

Test the connection

Create a file named test_connection.php:

<?php

require_once "db.php";

echo "Database connected successfully!";

Open this URL in your browser:

http://localhost/php_mysql_demo/test_connection.php

If your settings are correct and the database service is running, you should see:

Database connected successfully!

Once the connection works, you can use the same db.php file throughout your project.

7. Insert Data Into MySQL Using PHP

Now let's save a student's details in the database.

Create a file named insert.php:

<?php

require_once "db.php";

$name = "Rahul Sharma";
$email = "rahul@example.com";
$course = "Core PHP";

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

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

$stmt->execute([
    "name" => $name,
    "email" => $email,
    "course" => $course
]);

echo "Student record inserted successfully!";

Open:

http://localhost/php_mysql_demo/insert.php

The code inserts a new record into the students table.

Why do we use prepare() and execute()?

The prepare() method prepares a query with placeholders. The execute() method supplies the values.

For example:

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

Here, :name, :email, and :course are named placeholders.

Prepared statements help prevent SQL injection when values are passed separately as parameters.

Important: If you refresh insert.php, it will insert the same student again. Later, we will use a form and a redirect to make this workflow more practical.

8. Retrieve Data From MySQL Using PHP

Inserting records is useful, but websites also need to display saved information.

Create a file named view.php:

<?php

require_once "db.php";

$sql = "SELECT id, name, email, course, created_at
        FROM students
        ORDER BY id DESC";

$stmt = $pdo->query($sql);

$students = $stmt->fetchAll();

function escapeHtml($value)
{
    return htmlspecialchars(
        (string) $value,
        ENT_QUOTES,
        "UTF-8"
    );
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student Records</title>
</head>
<body>

<h2>Student Records</h2>

<table border="1" cellpadding="10">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Course</th>
            <th>Created At</th>
        </tr>
    </thead>

    <tbody>
        <?php if (count($students) > 0): ?>

            <?php foreach ($students as $student): ?>
                <tr>
                    <td><?= escapeHtml($student["id"]) ?></td>
                    <td><?= escapeHtml($student["name"]) ?></td>
                    <td><?= escapeHtml($student["email"]) ?></td>
                    <td><?= escapeHtml($student["course"]) ?></td>
                    <td><?= escapeHtml($student["created_at"]) ?></td>
                </tr>
            <?php endforeach; ?>

        <?php else: ?>
            <tr>
                <td colspan="5">No student records found.</td>
            </tr>
        <?php endif; ?>
    </tbody>
</table>

</body>
</html>

Open:

http://localhost/php_mysql_demo/view.php

You should see the student records in an HTML table.

Understand the important functions

query()

$stmt = $pdo->query($sql);

This is suitable for a query without user-supplied values, such as the fixed SELECT statement above.

fetchAll()

$students = $stmt->fetchAll();

This retrieves all matching rows into an array.

foreach

foreach ($students as $student)

This loops through the records so that each student can be displayed.

htmlspecialchars()

htmlspecialchars($value, ENT_QUOTES, "UTF-8");

This escapes special HTML characters before displaying database values in an HTML page. It helps prevent stored HTML or JavaScript from being interpreted as active markup.

Remember: prepared statements protect database queries; HTML escaping protects output. These are two different security measures.

9. Insert Data Through an HTML Form

Hard-coding student information is fine for a first example, but real websites usually accept data from users.

Create a file named add_student.php:

<?php

require_once "db.php";

$errors = [];
$name = "";
$email = "";
$course = "";

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $name = trim($_POST["name"] ?? "");
    $email = trim($_POST["email"] ?? "");
    $course = trim($_POST["course"] ?? "");

    if ($name === "") {
        $errors[] = "Name is required.";
    }

    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors[] = "Enter a valid email address.";
    }

    if ($course === "") {
        $errors[] = "Course is required.";
    }

    if (!$errors) {
        $sql = "INSERT INTO students (name, email, course)
                VALUES (:name, :email, :course)";

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

        $stmt->execute([
            "name" => $name,
            "email" => $email,
            "course" => $course
        ]);

        header("Location: view.php");
        exit;
    }
}

function escapeHtml($value)
{
    return htmlspecialchars(
        (string) $value,
        ENT_QUOTES,
        "UTF-8"
    );
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Add Student</title>
</head>
<body>

<h2>Register a Student</h2>

<?php if ($errors): ?>
    <ul>
        <?php foreach ($errors as $error): ?>
            <li><?= escapeHtml($error) ?></li>
        <?php endforeach; ?>
    </ul>
<?php endif; ?>

<form method="POST" action="">
    <label for="name">Name:</label>
    <input
        type="text"
        id="name"
        name="name"
        maxlength="100"
        value="<?= escapeHtml($name) ?>"
        required
    >

    <br><br>

    <label for="email">Email:</label>
    <input
        type="email"
        id="email"
        name="email"
        maxlength="150"
        value="<?= escapeHtml($email) ?>"
        required
    >

    <br><br>

    <label for="course">Course:</label>
    <input
        type="text"
        id="course"
        name="course"
        maxlength="100"
        value="<?= escapeHtml($course) ?>"
        required
    >

    <br><br>

    <button type="submit">Register Student</button>
</form>

</body>
</html>

Open:

http://localhost/php_mysql_demo/add_student.php

Enter a student's details and submit the form. If validation succeeds, the data will be saved, and the browser will redirect to view.php.

What does this example teach us?

• $_SERVER["REQUEST_METHOD"] checks whether the form was submitted using POST.

• $_POST contains submitted form values.

• trim() removes leading and trailing whitespace.

• filter_var() validates the email format.

• Prepared statements pass values safely to the SQL query.

• header("Location: view.php") redirects the browser after insertion.

• exit stops further execution after the redirect.

The redirect also helps avoid accidental duplicate submissions when the user refreshes the results page.

For a public-facing application, also consider CSRF protection, stronger validation, database constraints, rate limiting where appropriate, and proper error logging.

10. Update Records in MySQL Using PHP

Suppose a student changes their course. You can update the existing record rather than inserting another student.

Create a file named update_student.php:

<?php

require_once "db.php";

$id = 1;
$newCourse = "Advanced PHP";

$sql = "UPDATE students
        SET course = :course
        WHERE id = :id";

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

$stmt->execute([
    "course" => $newCourse,
    "id" => $id
]);

echo "Student record updated successfully.";

Open:

http://localhost/php_mysql_demo/update_student.php

This updates the course of the student whose ID is 1.

Why is the WHERE clause important?

The following query updates only the record with ID 1:

UPDATE students
SET course = 'Advanced PHP'
WHERE id = 1;

Without the WHERE clause, the update could change the course for every student in the table.

In a real application, obtain the ID from a validated request and verify that the current user is authorized to edit that record. Do not blindly trust an ID supplied by a browser.

11. Delete Records From MySQL Using PHP

To delete a record, use the SQL DELETE statement.

Create a file named delete_student.php:

<?php

require_once "db.php";

$id = 1;

$sql = "DELETE FROM students WHERE id = :id";

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

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

echo "Student record deleted successfully.";

Open:

http://localhost/php_mysql_demo/delete_student.php

This removes the record with ID 1.

Important: This example is for local learning. Avoid using a public URL that deletes records automatically. In a real application, deletion should be performed through an appropriately protected POST request, with authorization checks and a confirmation step where suitable.

12. Common Errors in PHP and MySQL Connectivity

Beginners often encounter database connection problems. Here are some common causes and solutions.

Error 1: Access denied for user

Possible cause: The database username or password is incorrect, or the account does not have the required permissions.

Solution:

• Check the credentials in db.php.

• Verify the account permissions in your local database setup.

• Do not assume every installation uses the default XAMPP credentials.

Error 2: Unknown database

Possible cause: The database name is misspelled or the database has not been created.

Solution:

• Open phpMyAdmin.

• Confirm that student_db exists.

• Make sure the database name in db.php matches exactly.

Error 3: Table does not exist

Possible cause: The students table has not been created in the selected database.

Solution: Run the table-creation SQL query and verify that you selected the correct database.

Error 4: Could not find driver

Possible cause: The PDO MySQL driver is not enabled or installed.

Solution:

• Check the PHP configuration used by your web server.

• Ensure the PDO MySQL extension is enabled.

• Restart Apache after changing the relevant PHP configuration.

Error 5: Headers already sent

Possible cause: PHP has already sent output before calling header().

Solution:

• Call header() before producing HTML or other output.

• Check for accidental spaces or text before the opening <?php tag.

• Keep the redirect followed by exit.

Error 6: Duplicate records appear

Possible cause: The insertion script has been executed multiple times.

Solution:

• Use a form submission workflow followed by a redirect.

• Add appropriate unique database constraints if the application requires unique values, such as email addresses.

• Check for duplicate data before inserting it.

13. Best Practices for PHP and MySQL Connectivity

When developing database-driven applications, follow these guidelines.

1. Use prepared statements. Avoid combining user input directly into SQL strings.

2. Validate user input. Check required fields, lengths, formats, and business rules on the server.

3. Escape output. Use context-appropriate escaping when displaying database values in HTML.

4. Protect database credentials. Do not publish passwords or commit secrets to a public repository.

5. Use a least-privilege database account. Your application should have only the permissions it needs.

6. Handle errors safely. Log technical details privately and show users a generic error message.

7. Use HTTPS in production. Protect data sent between the browser and your server.

8. Add authorization checks. A valid record ID does not prove that a user is allowed to change or delete that record.

9. Use CSRF protection for state-changing forms. Prepared statements do not prevent cross-site request forgery.

10. Keep backups. Important database records should be backed up and recovery should be tested.

11. Avoid using the root database account in production. Create a dedicated account with limited permissions.

12. Set database constraints. Use appropriate NOT NULL, unique, primary-key, and foreign-key constraints to protect data integrity.

14. Practice Exercises

Try the following exercises to strengthen your understanding.

Beginner level

1. Create a database named college_db.

2. Create a table named courses with id, course_name, and duration columns.

3. Connect PHP to college_db using PDO.

4. Insert three courses into the table.

5. Display all courses in an HTML table.

Intermediate level

1. Create an HTML form to add a new course.

2. Validate the course name and duration before inserting.

3. Add a page to update a course by ID.

4. Add a safe delete workflow using POST.

5. Display a message when no records are found.

Mini project: Student Management System

Build a small student management application with the following features:

• Add a student.

• View all students.

• Update a student's course.

• Delete a student.

• Validate all form fields.

• Use PDO prepared statements for database operations.

• Escape all database values displayed in HTML.

Try completing the project yourself before looking back at the examples above.

15. Frequently Asked Questions (FAQs)

Q1. Can PHP work with MySQL?

Yes. PHP can communicate with MySQL-compatible databases using extensions such as PDO MySQL and MySQLi.

Q2. What is PDO in PHP?

PDO stands for PHP Data Objects. It provides a consistent interface for accessing supported databases and supports prepared statements.

Q3. What is the difference between MySQL and PHP?

PHP is a server-side programming language. MySQL is a relational database management system. PHP can send queries to MySQL to store and retrieve information.

Q4. What is a prepared statement?

A prepared statement separates an SQL query from the values supplied to it. This helps protect against SQL injection when parameters are used correctly.

Q5. What is the difference between query() and prepare()?

query() executes an SQL statement directly and is useful for fixed queries without user-supplied values. prepare() prepares a statement with placeholders so that values can be supplied separately through execute().

Q6. Why do we use fetchAll()?

fetchAll() retrieves all remaining rows from a query result and returns them as an array. For large datasets, fetching and processing records in batches can be more efficient.

Q7. Is it safe to display database data directly in HTML?

Not necessarily. Database values may contain HTML characters or untrusted content. Escape values for the correct output context, such as with htmlspecialchars() for ordinary HTML text and quoted HTML attributes.

Q8. Should I use PDO or MySQLi?

Both are useful. PDO supports multiple database systems through a consistent interface, while MySQLi is designed specifically for MySQL-compatible databases. This tutorial uses PDO for its examples.

Q9. Why is my database connection failing?

Common reasons include incorrect credentials, a stopped database service, a misspelled database name, missing PDO MySQL support, or insufficient database permissions.

Q10. Can I use these examples on a live website?

The examples teach the basic concepts, but a production application also needs secure credential management, access control, CSRF protection, robust validation, appropriate error handling, HTTPS, and a suitable deployment configuration.

Conclusion

In this module, we learned how PHP and MySQL work together to build database-driven websites.

You learned how to:

• Create a database and table.

• Connect PHP to MySQL using PDO.

• Insert records with prepared statements.

• Retrieve and display database records.

• Collect student information through an HTML form.

• Update and delete records.

• Troubleshoot common connection problems.

• Follow important database security practices.

Database connectivity is a fundamental skill for Core PHP development. Once you understand these concepts, you can begin building applications such as student management systems, contact directories, product catalogs, and simple content management systems.

Practice the examples locally, understand each SQL query, and build the mini project before moving forward.