Recent Posts

GraphQL in Laravel Using Lighthouse

3 months ago

Sanitize and Create URLs via Laravel command

4 months ago

Short Summary : The Obstacle is the way

2 years ago

CRUD Operations In Laravel 8

3 years ago

Installing PHP on Windows

3 years ago
  • Calculating...
  • 5 years ago
  • 15.2K Views
  • Archived This is an Archived post.
    The content within this may not be used or replaced with newer versions.

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.

 

Database

  1. Create the DB(Database) file

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

 

Creating the DB

SQL query to create a table named users

 

 

Creating the Table

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();
}
 
  1. 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 a 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 in the comment section.

Tags:
php programming CRUD
Share:

Related Post

PHP

Installing PHP on Windows

PHP is known to be one of the most commonly used server-side programming language on the web. It provides an easy to master with a simple learning curve. It has close ties with the MySQL database.

  • 3 years ago
PHP

Convert PHP Array To JSON : Examples

This tutorial focuses on converting PHP Array to JSON. It is used to read data from the server and display it to the Web. JSON is a text format , and We can convert any JS Object into a JSON format.

  • 5 years ago
PHP

Simple PHP Page Router

This is a simple yet basic PHP routing application created to direct all request to index.php and route the files to it’s relevant paths.

  • 5 years ago
PHP

PHP Program to remove empty or specific array elements

This tutorial is created to remove specific or empty (NULL) array elements from the array.

  • 5 years ago
PHP

PHP Array Functions and their usage with examples

This tutorial is created to give an abstract idea about commonly used PHP array function with examples. Through this tutorial you’ll be able to know, how to use array functions to make code more short

  • 4 years ago