Building a Simple CRUD Application with PHP and MySQL

PHP and MySQL have been my go-to for developing dynamic web applications. If you're just getting started or want to build a simple app that manages data, mastering CRUD operations (Create, Read, Update, Delete) is the first step. Let me walk you through how to build a basic CRUD application using PHP and MySQL. I'll break it down in a way that worked for me when I started, and I hope it helps you too.

Step 1: Setting Up the Environment

Before we jump into coding, you'll need a development environment. Personally, I use XAMPP because it simplifies the setup process. If you haven’t already, download and install XAMPP or WAMP. These local servers bundle Apache, PHP, and MySQL, so you don’t have to install them individually. Once you've got that running, we can start building.

Step 2: Creating the Database

First, we need to set up the database in MySQL. I use phpMyAdmin for this but feel free to use any tool you're comfortable with. Here’s a simple SQL script to create our database and table:

CREATE DATABASE crud_app;
USE crud_app;

CREATE TABLE users (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This will create a crud_app database and a users table with id, name, email, and created_at fields, where we’ll store the information.

Step 3: Connecting PHP to MySQL

Okay, now let’s connect PHP to the database. It’s an important step for any app that uses a database. Here’s how you can do it with mysqli:

<?php
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'crud_app';

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

This snippet creates a connection between PHP and MySQL, which will allow us to interact with our database.

Step 4: Building the CRUD Operations

1. Create (Inserting Data)

To start adding data to the database, create a form that captures user input and sends it via a POST request to a PHP script that handles the insertion.

if (isset($_POST['submit'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];

    $query = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
    $result = $conn->query($query);

    if ($result) {
        echo "Record added successfully!";
    } else {
        echo "Error: " . $conn->error;
    }
}
?>
<form method="post" action="">
  Name: <input type="text" name="name" required>
  Email: <input type="email" name="email" required>
  <button type="submit" name="submit">Submit</button>
</form>

2. Read (Displaying Data)

To display the data stored in the database, run a simple SELECT query and loop through the results like this:

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

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . ' - ' . $row['email'] . '<br>';
}

3. Update (Editing Data)

For updating data, first, fetch the record and pre-fill it into a form for editing. After submission, the updated info is saved back to the database:

if (isset($_POST['update'])) {
    $id = $_POST['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];

    $query = "UPDATE users SET name='$name', email='$email' WHERE id=$id";
    $result = $conn->query($query);

    if ($result) {
        echo "Record updated successfully!";
    } else {
        echo "Error: " . $conn->error;
    }
}
?>
<form method="post" action="">
  <input type="hidden" name="id" value="<?php echo $user['id']; ?>">
  Name: <input type="text" name="name" value="<?php echo $user['name']; ?>" required>
  Email: <input type="email" name="email" value="<?php echo $user['email']; ?>" required>
  <button type="submit" name="update">Update</button>
</form>

4. Delete (Removing Data)

Finally, to delete data, create a button that sends a DELETE query.

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

    $query = "DELETE FROM users WHERE id=$id";
    $result = $conn->query($query);

    if ($result) {
        echo "Record deleted successfully!";
    } else {
        echo "Error: " . $conn->error;
    }
}
?>
<form method="post" action="">
  <input type="hidden" name="id" value="<?php echo $user['id']; ?>">
  <button type="submit" name="delete">Delete</button>
</form>

With these steps, you now have a basic CRUD application using PHP and MySQL. This example can easily be expanded to include more complex features like additional fields, improved validation, or even functionalities like pagination and file uploads. Whether you're looking to manage dynamic data for a personal project or scale up for a larger application, this CRUD framework sets the foundation.

If you're looking for more help or want to build something bigger, feel free to reach out to me. I love working on projects that push boundaries and take simple ideas to the next level. Check out my GitHub or my portfolio website for more of my work. You can also drop me a message at [e-mail]. Let's build something awesome together! 🫶🏽

Comments

[Lithe]

Great article on building a CRUD application with PHP and MySQL! You can simplify the process even further by using Lithe. With Lithe, you can leverage its lightweight architecture and built-in migration support to manage your database easily. This makes creating and managing your database tables straightforward, allowing you to focus on building features rather than handling boilerplate code. Check out the Lithe documentation for more details!

[Joao Alves]

The code presented here is full of sql-injection bad practices.
Two options:

• Sanitize all input from user/browser
• USe prepared statements

Please rewrite the code. It should not be used has an example by others.
When using SQL statements avoid string interpolation/concatenation at all costs.