Demonstration of CRUD Operations in a Database using PHP and PDO

Introduction

Creating a web application often involves interacting with a database to perform CRUD (Create, Read, Update, Delete) operations. PHP, a popular server-side scripting language, along with PDO (PHP Data Objects), provides a powerful and secure way to interact with databases. In this blog post, we will demonstrate how to perform CRUD operations using PHP and PDO with prepared statements. For our example, we'll use a simple "Inventory Information" dataset to illustrate these operations.

Prerequisites

Before we dive into the code, make sure you have the following prerequisites in place:

1.     A web server (e.g., Apache) with PHP installed.

2.     A database server (e.g., MySQL) installed and running.

3.     Basic knowledge of PHP and SQL.

Step 1: Creating the Database

CREATE DATABASE inventory;
USE inventory;
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    description VARCHAR(100),
    price INT(10),
    quantity VARCHAR(100),
    category VARCHAR(100),
);

Now, we have a database named "inventory" with a table "items" to store inventory records.

Step 2: Connecting to the Database

Create a file named db_connection.php to handle the database connection.

<?php
$host = 'localhost';
$dbname = 'inventory';
$username = 'root';
$password = '';
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "connection successfully..";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Make sure to replace "your_username" and "your_password" with your database credentials.

Step 3: Insert Data

Start by creating an HTML form that collects the data you want to insert into the database.

<!DOCTYPE html>
<html>
<head>
    <title>Inventory Management</title>
</head>
<body>
    <h1>Inventory Management</h1>
    <form action="insert.php" method="post">
        <label for="name">Name:</label>
        <input type="text" name="name" required><br><br>
 
        <label for="description">Description:</label>
        <textarea name="description"></textarea><br><br>
 
        <label for="price">Price:</label>
        <input type="number" step="0.01" name="price" required><br><br>
 
        <label for="quantity">Quantity:</label>
        <input type="number" name="quantity" required><br><br>
 
        <label for="category">Category:</label>
        <select id="category" name="category">
            <option value="Electronics">Electronics</option>
            <option value="Clothing">Clothing</option>
            <option value="Furniture">Furniture</option>
        </select><br><br>
        <input type="submit" value="Add Item">
    </form>
</body>
</html>

Create a PHP script that will process the form data and insert it into the database. Save this script as “Insert.php”.

<?php
include "connection.php";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = $_POST["name"];
    $description = $_POST["description"];
    $price = $_POST["price"];
    $quantity = $_POST["quantity"];
    $category = $_POST["category"];
    $sql = "INSERT INTO items (name, description, price, quantity,category) VALUES (:name, :description, :price, :quantity,:category)";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':description', $description);
    $stmt->bindParam(':price', $price);
    $stmt->bindParam(':quantity', $quantity);
    $stmt->bindParam(':category', $category);
    if ($stmt->execute()) {
        header("Location: view.html");
    } else {
        echo "Error adding item.";
    }
}
?>

Step 4: Read Data

Create a PHP script to retrieve data from the database and display it on the HTML page. Save this script as “List.php”.

<?php
include "connection.php";
try {
    $sql = "SELECT * FROM items";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($result as $row) {
        echo "<tr>";
        echo "<td> ID " . $row['id'] . "</td>";
        echo "<td> Name" . $row['name'] . "</td>";
        echo "<td> Description" . $row['description'] . "</td>";
        echo "<td> Price" . $row['price'] . "</td>";
        echo "<td> Quantity" . $row['quantity'] . "</td>";
        echo "</tr>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>                                                                  

Step 5: Update Data

Start by creating an HTML form that collects the data you want to insert into the database.

<form action="update.php" method="post">
    <label for="item_id">Item ID (to update):</label>
    <input type="number" name="item_id" required><br><br>
    <label for="name">Name:</label>
    <input type="text" name="name"><br><br>
    <label for="description">Description:</label>
    <textarea name="description"></textarea><br><br>
    <label for="price">Price:</label>
    <input type="number" step="0.01" name="price"><br><br>
    <label for="quantity">Quantity:</label>
    <input type="number" name="quantity"><br><br>
    <label for="category">Category:</label>
    <select id="category" name="category">
        <option value="Electronics">Electronics</option>
        <option value="Clothing">Clothing</option>
        <option value="Furniture">Furniture</option>
        <!-- Add more categories as needed -->
    </select><br><br>
    <input type="submit" value="Update Item">
</form>

Create a PHP script (e.g., "update.php") to handle the form submission and update the data in the database.

<?php
include "connection.php";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $item_id = $_POST["item_id"];
    $new_name = $_POST["name"];
    $description = $_POST["description"];
    $new_price = $_POST["price"];
    $quantity = $_POST["quantity"];
    $category = $_POST["category"];
    $sql = "UPDATE items SET name = :name, price = :price,description =:description,quantity = :quantity,category =:category WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id', $item_id);
    $stmt->bindParam(':name', $new_name);
    $stmt->bindParam(':price', $new_price);
    $stmt->bindParam(':description', $description);
    $stmt->bindParam(':quantity', $quantity);
    $stmt->bindParam(':category', $category);
    if ($stmt->execute()) {
        header("Location: update.html");
    } else {
        echo "Error updating item.";
    }
}
?>

Step 6: Delete Data

Create an HTML form that allows users to select the data they want to delete. Here's a simple example:

<form action="delete.php" method="post">
    <label for="item_id">Item ID (to delete):</label>
    <input type="number" name="item_id" required><br>
    <input type="submit" value="Delete Item">
</form>

Create a PHP script (e.g., "delete.php") to handle the form submission and delete the data from the database.

<?php
include "connection.php";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $item_id = $_POST["item_id"];
    $sql = "DELETE FROM items WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id', $item_id);
    if ($stmt->execute()) {
        header("Location: index.html");
    } else {
        echo "Error deleting item.";
    }
}
?>

Conclusion

In this blog post, we demonstrated how to perform CRUD operations in a database using PHP and PDO with prepared statements. This approach ensures the security of your application by preventing SQL injection. You can adapt these examples for your own datasets and applications. Properly securing and validating user input is crucial when working with databases to protect your application from potential security threats.

Additional Tips

Make sure you add the file name properly. Also add your username, password, databaseName and tableName at required place.

Closing Remarks

In summary, mastering CRUD operations in a database using PHP and PDO is crucial for web developers. This blog post emphasized the importance of security, user-friendliness, modular code, and thorough testing. By following these best practices, you can build efficient and secure web applications that interact with databases seamlessly. Remember to prioritize security, provide user-friendly feedback, and consider features like search and pagination for enhanced user experience. Well-organized and documented code is essential for maintenance and collaboration. With these principles in mind, you'll be well-equipped to create successful web applications. Happy coding!

References

·        https://phpgurukul.com/php-crud-operation-using-pdo-extension/

·        https://phppot.com/php/php-pdo-crud/

Top of Form

 

Comments

Post a Comment

Popular posts from this blog

PHP Installation with Apache Server in Ubuntu

Image
PHP Installation with Apache Server in Ubuntu Introduction In this guide, we'll walk you through the process of setting up a powerful web development environment by installing PHP with the Apache web server on your Ubuntu machine. PHP is a versatile scripting language for building dynamic websites and web applications, while Apache serves as the webserver to handle requests. By the end of this tutorial, you'll be ready to create and test your PHP scripts, providing you with the tools you need for web development. Let's get started! Prerequisites Ubuntu OS: You need an Ubuntu operating system installed on your machine. Sudo Access: Ensure you have sudo privileges to run administrative commands. Internet Connection: A stable internet connection is required for package downloads. Terminal Proficiency: Basic familiarity with the Ubuntu Terminal is helpful. Command-Line Skills: Understanding fundamental command-line operations is beneficial. MySQL Root Password: Have a secu...
Read more
  Building a REST API for Inventory Management in PHP Introduction REST stands for Representational State Transfer, and it is an architectural style for designing networked applications. A REST API (Application Programming Interface) allows different software systems to communicate with each other over the internet, typically using HTTP requests. REST APIs are stateless, meaning each request from a client to a server must contain all the information needed to understand and fulfill that request. REST APIs are widely used because they are simple, scalable, and easy to understand. They are often employed in web and mobile applications to retrieve, update, create, or delete data from a server. Prerequisites Before we dive into the code, make sure you have the following prerequisites in place: ·        Web Server: Use a web server (e.g., Apache) with PHP support. ·        Database: Set up a MySQL database with ...
Read more