Simple PHP MySQL CRUD Application

PHP is widely used by many developers today and it's one of the easiest language to learn. This tutorial will help you to learn the basic CRUD (Create, Read, Update, Delete) operations in PHP using MYSQL.

PHP CRUD Application
  1. Create the DB(Database) file

    Create the DB as 'php_crud' and run the following SQL query to create a table users inside the MySQL 'php_crud' database.

    A generic square placeholder image with rounded corners in a figure.
    Creating 'php_crud' Database.
    A generic square placeholder image with rounded corners in a figure.
    Running SQL query to create users table

    SQL query to create a table named users

    CREATE TABLE users (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        address VARCHAR(255) NOT NULL,
        age INT(10) NOT NULL
    );

    Now, It's time to create the DB config.php file as shown below

    config.php

    <?php
    define('DB_SERVER', 'localhost');
    define('DB_USERNAME', '');
    define('DB_PASSWORD', '');
    define('DB_NAME', 'php_crud');
    
    $link = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
    
    if ($link->connect_errno) {
        printf("Connect failed: %s\n", $link->connect_error);
        exit();
    }
    
  2. Creating the Pages

    Create the landing page for the CRUD application that contains a table to show the records from the users table.

    index.php

    <?php
    require_once "config.php";
    $sql = "SELECT * FROM users";
    $result = $link->query($sql);
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>PHP CRUD : bishrulhaq.com</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
              integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
        <style>
            .btn{
                margin-left: 10px;
            }
        </style>
    </head>
    <body>
    <div class="wrapper">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <div class="card" style="margin-top: 20px;margin-bottom: 20px;">
                        <div class="card-body">
                            <h2 class="pull-left">User Details <a href="create.php" class="btn btn-success pull-right">Add New User</a></h2>
                            <h6>Find more interesting tutorials at <a href="https://bishrulhaq.com/">bishrulhaq.com</a></h6>
                        </div>
                    </div>
                    <?php
                    if ($result->num_rows > 0) {
                            echo "<table class='table table-bordered table-striped'>";
                            echo "<thead>";
                            echo "<tr>";
                            echo "<th>#</th>";
                            echo "<th>Name</th>";
                            echo "<th>Address</th>";
                            echo "<th>Age</th>";
                            echo "<th>Action</th>";
                            echo "</tr>";
                            echo "</thead>";
                            echo "<tbody>";
                            while ($row = $result->fetch_assoc()) {
                                echo "<tr>";
                                echo "<td>" . $row['id'] . "</td>";
                                echo "<td>" . $row['name'] . "</td>";
                                echo "<td>" . $row['address'] . "</td>";
                                echo "<td>" . $row['age'] . "</td>";
                                echo "<td>";
                                echo "<a href='read.php?id=" . $row['id'] . "' class='btn btn-primary'>Read</a>";
                                echo "<a href='update.php?id=" . $row['id'] . "' class='btn btn-info'>Update</a>";
                                echo "<a href='delete.php?id=" . $row['id'] . "' class='btn btn-danger'>Delete</a>";
                                echo "</td>";
                                echo "</tr>";
                            }
                            echo "</tbody>";
                            echo "</table>";
                            // Free result set
                            $result->free();
                        } else {
                            echo "<p class='lead'><em>No records were found.</em></p>";
                        }
                    $link->close();
                    ?>
                </div>
            </div>
        </div>
    </div>
    </body>
    </html>
    

    In this step, Let's create the "create.php" and add the following code inside it.

    create.php

    <?php
    require_once "config.php";
    
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
    
        if (isset($_POST['name']) && isset($_POST['address']) && isset($_POST['age'])) {
    
            $sql = "INSERT INTO users (name, address, age) VALUES (?,?,?)";
            if ($stmt = $link->prepare($sql)) {
                $stmt->bind_param("ssi", $_POST['name'], $_POST['address'], $_POST['age']);
                if ($stmt->execute()) {
                    header("location: index.php");
                    exit();
                } else {
                    echo "Error! Please try again later.";
                }
                $stmt->close();
            }
        }
    
        $link->close();
    }
    ?>
    
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Create User : bishrulhaq.com</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
              integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    <body>
    <div class="wrapper">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header">
                        <h2>Create Users</h2>
                    </div>
                    <p>Fill this form to add users to the database.</p>
                    <form action="<?php echo $_SERVER["PHP_SELF"] ?>" method="post">
                        <div class="form-group">
                            <label>Name</label>
                            <input type="text" name="name" class="form-control" required>
                        </div>
                        <div class="form-group">
                            <label>Address</label>
                            <textarea name="address" class="form-control" required></textarea>
                        </div>
                        <div class="form-group">
                            <label>Age</label>
                            <input type="number" name="age" class="form-control" required>
                        </div>
                        <input type="submit" class="btn btn-primary" value="Submit">
                        <a href="index.php" class="btn btn-default">Cancel</a>
                    </form>
                </div>
            </div>
        </div>
    </div>
    </body>
    </html>
    

    Create "delete.php" and add the following code inside it. Which will help to delete particular user by passing the ID.

    delete.php

    <?php
    if (isset($_GET["id"]) && !empty($_GET["id"])) {
    
        require_once "config.php";
        $sql = "DELETE FROM users WHERE id = ?";
    
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("i", $_GET["id"]);
            if ($stmt->execute()) {
                header("location: index.php");
                exit();
            } else {
                echo "Error! Please try again later.";
            }
        }
        $stmt->close();
        $link->close();
    } else {
        echo "Error! Please try again later.";
    }
    ?>
    

    Now, Let's Create the "read.php" and add the following code inside it.

    read.php

    <?php
    require_once "config.php";
    
    if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
        $sql = "SELECT * FROM users WHERE id = ?";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("i", $_GET["id"]);
            if ($stmt->execute()) {
                $result = $stmt->get_result();
                if ($result->num_rows == 1) {
                    $row = $result->fetch_array(MYSQLI_ASSOC);
    
                    $name = $row["name"];
                    $address = $row["address"];
                    $age = $row["age"];
    
                } else {
                    echo "Error! Please try again later.";
                    exit();
                }
    
            } else {
                echo "Error! Please try again later.";
                exit();
            }
        }
        $stmt->close();
        $link->close();
    } else {
        echo "Error! Please try again later.";
        exit();
    }
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>View User : bishrulhaq.com</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
              integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
        <style>
            label{
                font-weight: bold;
            }
        </style>
    </head>
    <body>
    <div class="wrapper">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <div class="card" style="margin-top: 20px;">
                        <div class="card-body">
                            <div class="page-header">
                                <h1>View User</h1>
                            </div>
                            <div class="form-group">
                                <label >Name</label>
                                <p class="form-control-static"><?php echo $name; ?></p>
                            </div>
                            <div class="form-group">
                                <label>Address</label>
                                <p class="form-control-static"><?php echo $address; ?></p>
                            </div>
                            <div class="form-group">
                                <label>Age</label>
                                <p class="form-control-static"><?php echo $age; ?></p>
                            </div>
                            <p><a href="index.php" class="btn btn-primary">Back</a></p>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    </body>
    </html>
    

    Create "update.php" and add the following code inside it. Which will help to update the particular user data

    update.php

    <?php
    require_once "config.php";
    
    if (isset($_GET['id'])) {
        $sql = "SELECT * FROM users WHERE id = ?";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("i", $_GET["id"]);
            if ($stmt->execute()) {
                $result = $stmt->get_result();
                if ($result->num_rows == 1) {
                    $row = $result->fetch_array(MYSQLI_ASSOC);
    
                    $param_name = $row["name"];
                    $param_address = $row["address"];
                    $param_age = $row["age"];
                } else {
                    echo "Error! Data Not Found";
                    exit();
                }
            } else {
                echo "Error! Please try again later.";
                exit();
            }
            $stmt->close();
        }
    } else {
        header("location: index.php");
        exit();
    }
    
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        if (!empty($_POST["name"]) && !empty($_POST["address"]) && !empty($_POST["age"])) {
    
            $sql = "UPDATE users SET name = ?, address = ?, age = ? WHERE id = ?";
            if ($stmt = $link->prepare($sql)) {
    
                $stmt->bind_param("ssii", $_POST["name"], $_POST["address"], $_POST["age"], $_GET["id"]);
                $stmt->execute();
                if ($stmt->error) {
                    echo "Error!" . $stmt->error;
                    exit();
                } else {
                    header("location: index.php");
                    exit();
                }
                $stmt->close();
            }
        }
        $link->close();
    }
    
    ?>
    
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Update User : bishrulhaq.com</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
              integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
        <style>
            label{
                font-weight: bold;
            }
        </style>
    </head>
    <body>
    <div class="wrapper">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                   <div class="card" style="margin-top:20px;">
                       <div class="card-body">
                           <div class="page-header">
                               <h2>Update User</h2>
                           </div>
                           <p>Edit the input to update the User.</p>
                           <form action="<?php echo $_SERVER['REQUEST_URI']; ?>" method="post">
                               <div class="form-group">
                                   <label>Name</label>
                                   <input type="text" name="name" class="form-control" required value="<?php echo $param_name; ?>">
                               </div>
                               <div class="form-group">
                                   <label>Address</label>
                                   <textarea name="address" class="form-control" required ><?php echo $param_address; ?></textarea>
                               </div>
                               <div class="form-group">
                                   <label>Age</label>
                                   <input type="text" name="age" class="form-control" value="<?php echo $param_age; ?>" required>
                               </div>
                               <input type="submit" class="btn btn-primary" value="Submit">
                               <a href="index.php" class="btn btn-default">Cancel</a>
                           </form>
                       </div>
                   </div>
                </div>
            </div>
        </div>
    </div>
    </body>
    </html>
    

Hope this tutorial helped you! Feel free to drop your opinion at the comment section.