Introduction

All contemporary web applications, from simple contact forms to complex admin panels, rely heavily on CRUD operations. CRUD, or Create, Read, Update, and Delete, is the four basic operations required to work with long-lived data in a database. You're building a blog, a customer system, or an app for a product catalog: CRUD is in its middle of activity.

In this guide, you’ll learn how to perform CRUD operations using PHP and MySQL, with the help of phpMyAdmin and a local server environment like XAMPP. This combination offers a powerful yet beginner-friendly setup for developers to practice database-driven web development on their local machines.

We will walk you through the installation of your setup, setting up a database, connecting PHP to MySQL, and implementing each of the CRUD operations step by step. You will be able to create dynamic applications capable of creating, displaying, updating, and deleting data by the end.

What is CRUD? (Create, Read, Update, Delete)

CRUD is a popular software development acronym that defines the four fundamental operations any database-driven application needs to support:

Create
This operation enables the user or system to create new records in a database. For instance, in a user management system, the "Create" operation adds a new user's name, email, and phone number to the database.

Read
The Read operation fetches already stored data from the database. It's commonly employed to present content on a webpage—such as showing all users, blog entries, or products.

Update
Update facilitates changing existing records. When a user updates their email address, the update process ensures the correct data is stored in the database.

Delete
As the name implies, the Delete action erases records from the database for good—such as deleting a comment, post, or user account.

CRUD is not a theoretical term—it's the backbone of the way most dynamic websites function under the hood. Whether you're working with PHP and MySQL, controlling your database through phpMyAdmin, or running the whole shebang on your own computer using XAMPP, having control of CRUD operations is a vital stepping stone to becoming an effective web developer.

👉Learn more about CRUD on Wikipedia to understand how these operations relate to modern software architecture.

Visual guide to CRUD operations with a computer showing database interface, bold 3D CRUD heading, and Create, Read, Update, Delete explanations
CRUD operations explained visually – Create, Read, Update, Delete with PHP and MySQL on XAMPP & phpMyAdmin

Prerequisites Before You Start

Before you start with CRUD operations in PHP and MySQL, there are some things that you would need to install and have ready. These prerequisites will get your environment ready for dynamic web application development:

Familiarity with basic PHP and MySQL
To proceed with this tutorial, you should be well acquainted with PHP and MySQL. You will be using PHP to communicate with the MySQL database, and you must have a general idea of the functions and syntax.

Install XAMPP on Your Local Machine:
XAMPP is an installed software bundle of Apache, MySQL, and phpMyAdmin that allows you to easily install a local development environment quickly. The following are what you will need:
💠Download and install XAMPP at https://www.apachefriends.org.
💠XAMPP will enable you to have the web server (Apache), database server (MySQL), and database manager (phpMyAdmin, the graphical user interface for managing MySQL databases) on your local machine.

Open phpMyAdmin:
Once installed and running, XAMPP makes phpMyAdmin available in a web browser at http://localhost/phpmyadmin. It's the simple control panel for your MySQL databases. You'll be using it to add databases, tables, and users, and it's extremely important for this tutorial to have installed and fiddled with your database.

A Code Editor
You'll place your PHP code into a text editor. Popular PHP development text editors are:
💠Visual Studio Code (VS Code)
💠Sublime Text
💠PHPStorm

Make sure you have a decent editor installed before that.

👍With those tools installed and set up, you can now begin to get your local environment in order and go ahead and begin building your first CRUD application.

👉If you haven’t yet installed your local server, follow this guide to set up XAMPP and phpMyAdmin on Windows before starting CRUD operations in PHP.

Create Your Own Local Setting

You need to set up your local environment before you can begin constructing the CRUD routines. This entails starting XAMPP, setting up the necessary files, then using phpMyAdmin to create a MySQL database. Pay close attention to these measures.

1. Lauch XAMPP:
💠First, make sure your local workstation has XAMPP installed correctly. To begin, follow these steps:
💠Launch the Apache and MySQL servers by using the XAMPP management panel. These are the servers for the database and the website, respectively.
💠In the XAMPP control panel, you should see green lights next to Apache and MySQL when the services are starting. At this point, you may connect to your database.

2. Go to phpMyAdmin
💠Then go to phpMyAdmin:
💠Navigate to http://localhost/phpmyadmin in your browser.
💠You may manage all of your MySQL databases on the phpMyAdmin interface after doing this.

3. Establish a fresh MySQL database:
To establish a new database in phpMyAdmin, take the following actions:
💠At the top, select the Databases tab.
💠Give your new database a name (such as crud_app) under Create database.
💠Selecting utf8_general_ci for the collation guarantees multilingual compatibility.
💠To set up the database, click Create.

4. Make a Table for CRUD Testing:
After creating your database, you must configure a table in order to use the CRUD functions. Take these actions:
💠In the left-hand sidebar, click on the database you just established, such as crud_app.
💠To create a users table, select the SQL tab and type the following query:

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
phone VARCHAR(15)
);

Press Go to run the query. The user information for your CRUD operations will be kept in this table.

5. Confirm the Creation of the Table
The users table ought to appear in the Structure tab when it has been created. You may now add, read, update, and remove items as you start working with data!

👍You may begin connecting PHP to MySQL and implementing CRUD activities now that the environment and MySQL database have been configured.

PHP-MySQL Database Connection

It's time to connect PHP to MySQL now that you have a MySQL database available and your XAMPP environment configured. This will enable you to carry out CRUD operations and communicate with the database.

1. Make the connection file for the database:
To begin managing the database connection, create a new PHP file. Name it db.php for organization's sake (or whatever you like). The PHP and MySQL connection will be managed by this file. The following is the basic code to use MySQLi (MySQL Improved Extension) to connect PHP to MySQL:

<?php
// Database credentials
$servername = "localhost";
$username = "root"; // default XAMPP username
$password = ""; // default XAMPP password (empty)
$dbname = "crud_app"; // your database name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

2. Understanding the Code:
 💠$servername: When operating on a local computer, this is usually localhost.
💠The default credentials for XAMPP are $username and $password. Root is the default username, while "empty" is the default password.
💠$dbname: This is the name of the MySQL database (such as crud_app) that you made in phpMyAdmin.

This script tries to establish a connection to the MySQL database and verifies that it was successful. An error notice will appear if the connection is lost.

3. Verify the Connection:
💠In the XAMPP htdocs folder (C:\xampp\htdocs\crud_app\db.php, for example), save your PHP file as db.php.
💠Go to http://localhost/crud_app/db.php in your browser.
💠If everything is configured properly, "Connected successfully" should appear.

4. Terminating Interaction:
Terminating the database connection when you're done with it is great practice. Include this line at the end of your script: $conn->close();

This ensures that the connection is properly terminated when the script has finished executing.

👍After this connection is made, you may begin interacting with your MySQL database using PHP. You will be able to add, read, update, and remove entries in the following phases.

👉In case your MySQL database is hosted remotely, here’s how you can open MySQL for external access securely.

Using PHP to Implement CRUD Operations

Now that PHP and MySQL are linked, it's time to put the fundamental CRUD functions into practice. This section will walk over the code for utilizing PHP and MySQL to create, read, update, and delete data.

A. Create (Add Data)

We'll first set up an HTML form for users to enter new data before creating it in the database. After that, we'll use PHP to manage the form submission and add the information to the MySQL database.

1. Data Input HTML Form
This HTML form may be used to gather user information, including name, email, and phone number:

<form action="create.php" method="post">
    <label for="name">Name:</label>
    <input type="text" name="name" required><br>

    <label for="email">Email:</label>
    <input type="email" name="email"><br>

    <label for="phone">Phone:</label>
    <input type="text" name="phone"><br>

    <input type="submit" value="Create">
</form>

2. PHP Script to Insert Data:
Now, let’s handle the form submission and insert the data into the database in create.php:

<?php
include 'db.php'; // Include the database connection

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $phone = $_POST['phone'];

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

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

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

This script:
💠Retrieves the form data via $_POST.
💠Inserts the data into the users table using an INSERT INTO query.
💠Checks if the operation was successful and closes the connection.

B. Read (Display Data)

The Read operation retrieves and displays data stored in the database. We’ll fetch records from the users table and display them in an HTML table.

1. PHP Script to Fetch Data
Here’s how to fetch and display the records:

<?php
include 'db.php'; // Include the database connection

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>Name</th><th>Email</th><th>Phone</th><th>Actions</th></tr>";

    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["id"]. "</td><td>" . $row["name"]. "</td><td>" . $row["email"]. "</td><td>" . $row["phone"]. "</td><td>
        <a href='edit.php?id=" . $row["id"] . "'>Edit</a> | <a href='delete.php?id=" . $row["id"] . "'>Delete</a></td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results found";
}

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

This script:
💠Retrieves all the records from the users table.
💠Displays each record in an HTML table, with options to edit or delete.

C. Update (Edit Data)

To update data, we need to display the existing data in a form, allow the user to edit it, and then update the database.

1. Edit Form for Updating Data
In edit.php, we fetch the current record based on the ID and pre-fill the form fields:

<?php
include 'db.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $sql = "SELECT * FROM users WHERE id=$id";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
}
?>

<form action="update.php" method="post">
    <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
    <label for="name">Name:</label>
    <input type="text" name="name" value="<?php echo $row['name']; ?>" required><br>

    <label for="email">Email:</label>
    <input type="email" name="email" value="<?php echo $row['email']; ?>"><br>

    <label for="phone">Phone:</label>
    <input type="text" name="phone" value="<?php echo $row['phone']; ?>"><br>

    <input type="submit" value="Update">
</form>

2. PHP Script to Update Data:
In update.php, we handle the form submission and update the record in the database:

<?php
include 'db.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = $_POST['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    $phone = $_POST['phone'];

    $sql = "UPDATE users SET name='$name', email='$email', phone='$phone' WHERE id=$id";

    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();
}
?>

This script:
💠Retrieves the edited data via $_POST.
💠Updates the corresponding record in the users table with the new values.

D. Delete (Remove Data)

The Delete operation removes a record from the database. Here’s how to do it:

1. PHP Script to Delete Data:
In delete.php, we will execute a DELETE query to remove a record:

<?php
include 'db.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];

    $sql = "DELETE FROM users WHERE id=$id";

    if ($conn->query($sql) === TRUE) {
        echo "Record deleted successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();
}
?>

This script:
💠Retrieves the id from the URL parameter.
💠Executes the DELETE query to remove the record from the users table.

👍With these CRUD operations, you now have a fully functional application for creating, reading, updating, and deleting data in MySQL using PHP.

Enhancing Your CRUD Program

Although the basic CRUD application works, there are a few ways to improve its efficiency, security, and usability. Let's explore these recommendations.

A. Including Fundamental Validation

Making ensuring the data being entered into the database is accurate and sanitized is one of the first stages in optimizing your CRUD application. This guarantees that only legitimate data is kept and lowers the possibility of SQL injection.

1. Clean Up Input Data
By sanitizing user input, criminal individuals can avoid inserting undesirable data or damaging SQL queries. This is an illustration of how to clean up input data before adding it to a database:

$name = mysqli_real_escape_string($conn, $_POST['name']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$phone = mysqli_real_escape_string($conn, $_POST['phone']);

To make the input data safe for use in SQL queries, this code utilizes mysqli_real_escape_string() to escape special characters.

2. Verify User Input
To make sure that all necessary fields are filled in and that the data is formatted correctly, you should additionally check user input. You can verify the phone and email fields, for instance:

if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    echo "Invalid email format";
}

if (!preg_match("/^[0-9]{10}$/", $phone)) {
    echo "Invalid phone number. Must be 10 digits.";
}

These validations verify that the phone number has precisely ten digits and that the email format is proper.

B. Using Pagination to Show Big Data Sets

Displaying every record on a single page may become too much to handle when your program expands and begins to handle large amounts of data. By using pagination, you may improve efficiency and usability by displaying a subset of items on each page.

1. Adjust the SQL query to include pagination.
You must modify your SQL query to get a set number of rows per page in order to apply pagination. Here's an illustration:

$limit = 5; // Number of records per page
$page = isset($_GET['page']) ? $_GET['page'] : 1; // Get the current page number
$start_from = ($page - 1) * $limit;

$sql = "SELECT * FROM users LIMIT $start_from, $limit";
$result = $conn->query($sql);

This code calculates the total number of pages and generates links for each page.

2. Display Pagination Links
To let users navigate between pages, add pagination links below the table:

$sql = "SELECT COUNT(id) FROM users";
$result = $conn->query($sql);
$row = $result->fetch_row();
$total_records = $row[0];
$total_pages = ceil($total_records / $limit);

for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='index.php?page=$i'>$i</a> ";
}

This code calculates the total number of pages and generates links for each page.

C. Securing Your CRUD Application

Security is crucial when dealing with user data. Below are some steps to secure your application:

1. Use Prepared Statements
Instead of directly embedding user input into SQL queries (as shown earlier), use prepared statements to prevent SQL injection. Here’s an example of using prepared statements with MySQLi:

$stmt = $conn->prepare("INSERT INTO users (name, email, phone) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $phone); // "sss" means all parameters are strings
$stmt->execute();

Prepared statements render user input secure to work with by encapsulating it as data rather than executable code.

2. Use HTTPS
If the app is going to be used for production, use HTTPS to encrypt data that is being passed between the user's browser and the server. This keeps confidential data, like passwords, secure.

👉Want to showcase your CRUD operations to clients without deploying them live? Use this guide to securely share your localhost using Ngrok.

D. UI (User Interface) Improvement

The success of your application depends on its user interface (UI). A simple, intuitive layout will make your application simpler to use and more accessible.

1. Including Bootstrap in the Design
A popular CSS framework called Bootstrap provides pre-existing UI components that you can utilize to instantly enhance the appearance of your application. You can include Bootstrap in your project by adding the following link to your HTML file’s <head> section:

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">

Then, you can use Bootstrap classes to style the form and table. For example:

<form class="form-group" action="create.php" method="post">
    <input type="text" class="form-control" name="name" required>
    <input type="email" class="form-control" name="email">
    <input type="text" class="form-control" name="phone">
    <button type="submit" class="btn btn-primary">Create</button>
</form>

2. Adding Confirmation Modals for Deleting
When users attempt to delete a record, show a confirmation modal to avoid accidental deletions. This modal can be created using Bootstrap’s modal component.

<!-- Delete Confirmation Modal -->
<div class="modal" id="deleteModal" tabindex="-1" role="dialog">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">Confirm Deletion</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <p>Are you sure you want to delete this record?</p>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
        <a href="delete.php?id=<?php echo $row['id']; ?>" class="btn btn-danger">Delete</a>
      </div>
    </div>
  </div>
</div>

E. Adding Search Functionality

As your database grows, users may need a way to quickly search for specific records. Adding a search feature is straightforward.

1. Search Form: Here’s a simple search form:

<form action="index.php" method="get">
    <input type="text" name="search" placeholder="Search users">
    <button type="submit">Search</button>
</form>

2. Search Logic: Modify the SQL query to filter records based on the search term:

$search = isset($_GET['search']) ? $_GET['search'] : '';

$sql = "SELECT * FROM users WHERE name LIKE '%$search%' OR email LIKE '%$search%' OR phone LIKE '%$search%'";
$result = $conn->query($sql);

This will search the users table for any records that match the search term.

Conclusion

We learned here how to deploy a CRUD application using PHP in a manner that allows us to communicate with the MySQL database. We began here from scratch as far as getting PHP and MySQL to talk to one another, and proceeded then to use the fundamental CRUD operations of Create, Read, Update, and Delete. These were all illustrated with some actual code examples, which was a good starting point in creating database-driven applications.

Key Takeaways

  • PHP to MySQL: We showed how to work with a database using MySQLi and have basic things such as inserting and retrieving sorted out.
  • CRUD Operations: We had all four CRUD operations, i.e., the bread and butter of most web applications.
  • Security Precautions: We implemented input validation and prepared statements to protect your app from security breaches such as SQL injection.
  • UI Improvements: We discovered how to render the user interface more beautiful with such as Bootstrap in order to make it personalized and pagination to showcase a large block of data.
  • Usability and Efficiency: Pagination, searching, and delete confirm functionality made the application fast and easy to use.

What's Next?

💠You now possess CRUD basics in your pocket, so you can deploy your application in one of many ways:
💠User Authentication: Add login and registration to personalized the experience for every user.
💠Advanced Data Handling: Add more sophisticated queries, data relationships, and reporting.
💠Frontend Enhancements: You can even add JavaScript or AJAX to add interactivity and dynamism to the interface.
💠Releasing the Application: After you've constructed your application, you can host it on a live server so that anybody in the world can view it.

No matter whether you're creating a straightforward contact manager or a sophisticated inventory system, the fundamental principles of CRUD operations never change. Learning these building blocks will have you well prepared to create data-driven applications that suit users' requirements.

Final Verdict:

CRUD operations are performed when you are developing the database when you are developing the web application, and after you get familiar with that, you will be helpful to work on a lot of projects. This tutorial was helpful not only to develop your own CRUD application but to optimize it as per best security practice, best performance practice, and best user experience practice.

With that, you’ve successfully learned how to implement, secure, and enhance CRUD operations in PHP. Keep experimenting with new features and techniques to improve your web development skills!