Building a PHP MySQL CRUD Application with Image Upload

In this blog post, we’ll create a simple PHP application with MySQL to perform CRUD operations (Create, Read, Update, Delete) for managing products. Each product will have a name, description, image, price, color, and size. We’ll include image upload functionality as part of the process.

Prerequisites

Before starting, ensure you have the following:

• XAMPP or WAMP installed.
• Basic knowledge of PHP and MySQL.
• A database and table setup.

Step 1: Database Setup

1. Open phpMyAdmin and create a database named productdb.
2. Create a table products with the following structure:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    image VARCHAR(255),
    price DECIMAL(10, 2) NOT NULL,
    color VARCHAR(50),
    size VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Project Setup

Create a folder product_crud in your web server directory (htdocs for XAMPP). Inside this folder, create the following files:

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

Step 3: Database Connection

In db.php, write the code to connect to the database:

<?php
$host = 'localhost';
$username = 'root';
$password = ''; // Default password for XAMPP
$database = 'productdb';

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

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

Step 4: Create Product Form (create.php)

Add a form to upload product details and image:

<?php include 'db.php'; ?>

<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = $_POST['name'];
    $description = $_POST['description'];
    $price = $_POST['price'];
    $color = $_POST['color'];
    $size = $_POST['size'];

    $image = $_FILES['image']['name'];
    $target = 'uploads/' . basename($image);

    if (move_uploaded_file($_FILES['image']['tmp_name'], $target)) {
        $sql = "INSERT INTO products (name, description, image, price, color, size) VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('ssssss', $name, $description, $image, $price, $color, $size);

        if ($stmt->execute()) {
            echo "Product added successfully!";
        } else {
            echo "Error: " . $stmt->error;
        }
    } else {
        echo "Failed to upload image.";
    }
}
?>

<form action="" method="POST" enctype="multipart/form-data">
    <label for="name">Name:</label>
    <input type="text" name="name" required><br>

    <label for="description">Description:</label>
    <textarea name="description" required></textarea><br>

    <label for="price">Price:</label>
    <input type="number" name="price" step="0.01" required><br>

    <label for="color">Color:</label>
    <input type="text" name="color"><br>

    <label for="size">Size:</label>
    <input type="text" name="size"><br>

    <label for="image">Image:</label>
    <input type="file" name="image" required><br>

    <button type="submit">Add Product</button>
</form>

Step 5: Display Products (index.php)

Display all products in a table:

<?php include 'db.php'; ?>

<table border="1">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Description</th>
        <th>Image</th>
        <th>Price</th>
        <th>Color</th>
        <th>Size</th>
        <th>Actions</th>
    </tr>

    <?php
    $result = $conn->query("SELECT * FROM products");
    while ($row = $result->fetch_assoc()):
    ?>
    <tr>
        <td><?= $row['id']; ?></td>
        <td><?= $row['name']; ?></td>
        <td><?= $row['description']; ?></td>
        <td><img src="uploads/<?= $row['image']; ?>" width="50" alt=""></td>
        <td><?= $row['price']; ?></td>
        <td><?= $row['color']; ?></td>
        <td><?= $row['size']; ?></td>
        <td>
            <a href="update.php?id=<?= $row['id']; ?>">Edit</a>
            <a href="delete.php?id=<?= $row['id']; ?>">Delete</a>
        </td>
    </tr>
    <?php endwhile; ?>
</table>

Step 6: Update Product (update.php)

Fetch the product by ID and update its details:

<?php include 'db.php'; ?>

<?php
$id = $_GET['id'];
$result = $conn->query("SELECT * FROM products WHERE id = $id");
$product = $result->fetch_assoc();

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = $_POST['name'];
    $description = $_POST['description'];
    $price = $_POST['price'];
    $color = $_POST['color'];
    $size = $_POST['size'];

    $image = $product['image'];
    if (!empty($_FILES['image']['name'])) {
        $image = $_FILES['image']['name'];
        $target = 'uploads/' . basename($image);
        move_uploaded_file($_FILES['image']['tmp_name'], $target);
    }

    $sql = "UPDATE products SET name=?, description=?, image=?, price=?, color=?, size=? WHERE id=?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ssssssi', $name, $description, $image, $price, $color, $size, $id);

    if ($stmt->execute()) {
        echo "Product updated successfully!";
    } else {
        echo "Error: " . $stmt->error;
    }
}
?>

<form action="" method="POST" enctype="multipart/form-data">
    <label for="name">Name:</label>
    <input type="text" name="name" value="<?= $product['name']; ?>" required><br>

    <label for="description">Description:</label>
    <textarea name="description" required><?= $product['description']; ?></textarea><br>

    <label for="price">Price:</label>
    <input type="number" name="price" step="0.01" value="<?= $product['price']; ?>" required><br>

    <label for="color">Color:</label>
    <input type="text" name="color" value="<?= $product['color']; ?>"><br>

    <label for="size">Size:</label>
    <input type="text" name="size" value="<?= $product['size']; ?>"><br>

    <label for="image">Image:</label>
    <input type="file" name="image"><br>
    <img src="uploads/<?= $product['image']; ?>" width="50" alt=""><br>

    <button type="submit">Update Product</button>
</form>

Step 7: Delete Product (delete.php)

Delete a product by ID:

<?php include 'db.php'; ?>

<?php
$id = $_GET['id'];
$conn->query("DELETE FROM products WHERE id = $id");
header('Location: index.php');
?>

Conclusion

You now have a fully functional PHP CRUD application with MySQL and image upload functionality. Modify the application further to meet your requirements, such as adding validations or improving the UI.