The Easiest Way to Build a CRUD App with PHP and MySQL

9 min readJun 24, 2024

In this tutorial, we will build a simple To-Do List application where users can create, read, update, and delete tasks. We’ll be using core PHP and MySQL without any frameworks. This project will help you understand the basics of CRUD operations and how to interact with a MySQL database using PHP.

Prerequisites

Basic knowledge of PHP and HTML.
A web server environment like XAMPP, WAMP, or MAMP.
A code editor (e.g., VS Code, Sublime Text).

Step 1: Setting Up the Project

First, set up your project directory and necessary files. Create a folder named todo_list and within it, create the following files:

index.php
create.php
update.php
delete.php
db.php

Step 2: Setting Up the Database

Create a MySQL database and a table to store the to-do items.

Open your MySQL database management tool (e.g., phpMyAdmin).
Create a new database named todo_list_db.
Either you can create manually or else
Create a table named tasks with the following structure:

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

in MySQL databases, the created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP command is used to automatically set the value of a timestamp column to the current date and time whenever a new row is inserted into the table

When you insert a new task into the tasks table without specifying a value for created_at, MySQL automatically sets created_at to the current timestamp.

by default, MySQL typically uses the system timezone of the server it is running on unless explicitly configured otherwise.

Step 3: Connecting to the Database

Create a db.php file to handle the database connection. db.php is just a file name , you can take connection.php as well or anything.

The connect_error / mysqli_connect_error() function returns the error description from the last connection error, if any.

Step 4: Adding a New Task (Create Operation)

In create.php, create a form to add new tasks.

When deciding between embedding PHP directly within HTML or using separate files, consider your project’s complexity. For small projects, combining PHP and HTML simplifies the workflow and is great for beginners.

Here i am writing php and html in same file only .

In our previous articles on creating secure login and signup forms, we emphasized the importance of using prepared statements ($conn->prepare) in PHP to interact safely with databases. Prepared statements provide robust protection against SQL injection by automatically handling input parameterization and escaping.

Today, I’ll introduce you to another method of database interaction in PHP: $conn->query. Unlike prepared statements, which use placeholders to separate SQL logic from data, $conn->query executes SQL queries directly with concatenated variables

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $title = $_POST['title'];
    $description = $_POST['description'];

    $sql = "INSERT INTO tasks (title, description) VALUES ('$title', '$description')";

    if ($conn->query($sql) === TRUE) {
        echo "task added";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Add New Task</title>
</head>
<body>
    <h1>Add New Task</h1>
    <form method="post">
        <label for="title">Title:</label><br>
        <input type="text" id="title" name="title"><br><br>
        <label for="description">Description:</label><br>
        <textarea id="description" name="description"></textarea><br><br>
        <input type="submit" value="Add Task">
    </form>
</body>
</html>

Choose $conn->prepare for enhanced security, efficiency with repeated queries, and clearer error handling, especially in larger applications. $conn->query($sql) is suitable for simpler queries but requires careful input sanitization to prevent SQL injection vulnerabilities. Prioritize security and performance based on the specific requirements and complexity of your database interactions.

to use prepared statements for inserting data into the tasks table securely, you can change the code

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $title = $_POST['title'];
    $description = $_POST['description'];

    // Prepare the SQL statement with placeholders
    $sql = "INSERT INTO tasks (title, description) VALUES (?, ?)";
    
    // Prepare the statement
    $stmt = $conn->prepare($sql);
    
    // Bind parameters to the prepared statement
    $stmt->bind_param("ss", $title, $description);
    
    // Execute the statement
    if ($stmt->execute()) {
        echo "Task added successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $stmt->error;
    }
    
    // Close the statement
    $stmt->close();
}
?>

ok lets go to our pages

http://localhost/todo_list/create.php

Once we add our task to this , we will find these things

Step 5: Getting the To-Do List (Read Operation)

In index.php, fetch and display the tasks from the database.

<?php
include 'db.php';

// Prepare the SQL statement
$sql = "SELECT * FROM tasks";
$stmt = $conn->prepare($sql);

// Execute the statement
$stmt->execute();

// Get result set
$result = $stmt->get_result();
?>

<!DOCTYPE html>
<html>
<head>
    <title>To-Do List</title>
</head>
<body>
    <h1>To-Do List</h1>
    <a href="create.php">Add New Task</a>
    <ul>
        <?php while($row = $result->fetch_assoc()): ?>
            <li>
                <h2><?php echo htmlspecialchars($row['title']); ?></h2>
                <p><?php echo htmlspecialchars($row['description']); ?></p>
                <a href="update.php?id=<?php echo $row['id']; ?>">Edit</a>
                <a href="delete.php?id=<?php echo $row['id']; ?>">Delete</a>
            </li>
        <?php endwhile; ?>
    </ul>
</body>
</html>

<?php
// Close statement
$stmt->close();
$conn->close();
?>

Lets go the page

http://localhost/todo_list/index.php

$stmt->get_result() returns a mysqli_result object which allows you to fetch rows from a result set. Each row fetched from this result set can be accessed as an associative array using methods like fetch_assoc().

An associative array in PHP is a collection of key-value pairs where keys are unique identifiers mapped to their corresponding values, allowing for direct access and manipulation of data elements.

an associative array in PHP is conceptually similar to an object in JavaScript. Both structures allow you to store data in key-value pairs

Fetching Data with fetch_assoc():

while ($row = $result->fetch_assoc()) { ... } initiates a loop that iterates through each row in the result set.
Inside the loop, $row is an associative array where keys are column names ('id', 'title', 'description') and values are the corresponding column values for each row fetched.

Why `<?php endwhile; ?>`?

<?php endwhile; ?> is a PHP syntax construct that explicitly marks the end of a while loop.
It helps maintain clarity and structure in PHP code, especially when dealing with nested loops or complex logic.
It’s part of PHP’s syntax to indicate where the control structure (in this case, the while loop) ends, ensuring the interpreter knows where to stop looping.

Both approaches are valid in PHP, and the choice between them often depends on personal preference and the specific requirements of the project or coding standards in use

 <ul>
        <?php while($row = $result->fetch_assoc()): ?>
            <li>
                <h2><?php echo htmlspecialchars($row['title']); ?></h2>
                <p><?php echo htmlspecialchars($row['description']); ?></p>
                <a href="update.php?id=<?php echo $row['id']; ?>">Edit</a>
                <a href="delete.php?id=<?php echo $row['id']; ?>">Delete</a>
            </li>
        <?php endwhile; ?>
    </ul>

Using id in URLs for CRUD operations in web applications provides clarity, reliability, and security. It ensures that users can interact with specific records in the database in a controlled and predictable manner, supporting efficient data management and user experience. Thus, id is chosen primarily because of its uniqueness and suitability as a primary key identifier in database-driven applications.

When you execute a query using the mysqli extension, you typically get a mysqli_result object. The fetch_assoc() method retrieves the next row from the result set as an associative array where the keys are the column names of the table.

Usage in a Loop

Often, fetch_assoc() is used in a while loop to fetch all rows from the result set when you expect multiple rows , when you expect a single row no need to run the loop .

Step 6: Updating a Task (Update Operation)

In update.php, create a form to update existing tasks.

<?php
include 'db.php';

$id = $_GET['id'];

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $title = $_POST['title'];
    $description = $_POST['description'];

    // Prepare UPDATE statement
    $sql = "UPDATE tasks SET title=?, description=? WHERE id=?";

    // Bind parameters and execute the statement
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $title, $description, $id); // "ssi" indicates types of parameters: string, string, integer
    if ($stmt->execute()) {
        header('Location: index.php');
    } else {
        echo "Error: " . $stmt->error;
    }
} else {
    // Prepare SELECT statement
    $sql = "SELECT * FROM tasks WHERE id=?";

    // Bind parameter and execute the statement
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $id); // "i" indicates type of parameter: integer
    $stmt->execute();
    $result = $stmt->get_result();

    // Fetch task details
    $task = $result->fetch_assoc();
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Edit Task</title>
</head>
<body>
    <h1>Edit Task</h1>
    <form method="post">
        <label for="title">Title:</label><br>
        <input type="text" id="title" name="title" value="<?php echo htmlspecialchars($task['title']); ?>"><br><br>
        <label for="description">Description:</label><br>
        <textarea id="description" name="description"><?php echo htmlspecialchars($task['description']); ?></textarea><br><br>
        <input type="submit" value="Update Task">
    </form>
</body>
</html>

Single Row Query: Use fetch_assoc() to fetch a single row when you expect only one result.

When you execute a SQL query that you expect to return only one row, you can fetch that row directly without looping through the result set

You can then access the columns of the fetched row using the associative array keys ("id", "title", "description") and perform any operations you need.

http://localhost/todo_list/update.php?id=1

Retrieving Task ID ($id = $_GET['id'];):

The script retrieves the id of the task to be edited from the URL parameter using $_GET['id']. This assumes that the URL will be something like update.php?id=1, where 1 is the task ID.

Preparing and Executing SQL Statements:

UPDATE Statement: Constructs a SQL query to update the tasks table with new title and description values for the specified id. It uses prepared statements ($stmt->prepare(), $stmt->bind_param()) to safely insert values into the query, preventing SQL injection attacks.
SELECT Statement: Constructs a SQL query to retrieve the existing task details (title and description) based on the provided id. Similarly, it uses prepared statements to bind parameters and execute the query safely.

Binding Parameters ($stmt->bind_param()):

Parameters are bound to prepared statements to ensure correct data types and prevent SQL injection. "ssi" indicates the types of parameters (string, string, integer).

Executing SQL Queries ($stmt->execute()):

Executes the prepared SQL statements (UPDATE or SELECT) to perform the database operations.

Displaying Task Details in Form:

Upon loading the page (GET request), the script fetches the current task details from the database and populates the form fields (<input> and <textarea>) with the existing title and description values.

Redirecting After Successful Update (header('Location: index.php');):

After successfully updating the task, the script redirects the user to index.php, which presumably lists all tasks.

Error Handling:

If an error occurs during the database operation ($stmt->error), an error message is displayed (echo "Error: " . $stmt->error;)

Step 7: Deleting a Task (Delete Operation)

In delete.php, delete the selected task.

<?php
include 'db.php';

$id = $_GET['id'];

// Prepare DELETE statement with a placeholder for the ID
$sql = "DELETE FROM tasks WHERE id=?";

// Prepare the statement
$stmt = $conn->prepare($sql);

// Bind the ID parameter
$stmt->bind_param("i", $id); // "i" indicates the type of parameter: integer

// Execute the statement
if ($stmt->execute()) {
    header('Location: index.php');
} else {
    echo "Error: " . $stmt->error;
}

// Close the statement
$stmt->close();

// Close the database connection
$conn->close();
?>

the commands we mentioned (INSERT, SELECT, DELETE) are fundamental MySQL commands used for manipulating data within a MySQL database.

INSERT: Adds new records into a database table.
SELECT: Retrieves data from one or more tables based on specified conditions.
DELETE: Removes existing records from a database table based on specified conditions.

Congratulations! You’ve created a basic CRUD To-Do List application using core PHP and MySQL. This project covered essential concepts such as:

Database connections
SQL queries
Form handling
Redirecting and passing data via URLs

With this foundation, you can now expand the functionality of your application, such as adding user authentication, implementing more advanced validation, or styling it with CSS.

Keep pushing yourself to learn and improve, and remember that consistency is key. Happy coding!

👏 Clap Clap! 👏 if you find this helpful