cover image
Nitish Kumar Singh
Nitish Kumar Singh
Clock Image 10 minutes
Posted on
March 12, 2020

PHP CRUD Operation


Introduction #

We will be creating a form and submitting the data to the database. Our AIM is to learn how we can fetch the data from the form and submit in the form. We will not be discussing the form data validation.

You should not keep the expectation that you will learn about the form validation.

We will break the complete article into 6 parts. In every part, we will do something new and if you face some problem you can comment on the step number with the error. We will be happy to help you.

Step #Task
Step 1Creating Forms
Step 2Getting data of form
Step 3Creating database in MySQL
Step 4Connecting to MySQL
Step 5Inserting data to MySQL
Step 6Fetching Data from the MySQL
Step 7Updating Data from the MySQL
Step 8Delete Data from the MySQL

Step 1: Creating Forms #

Suppose we are creating a form for E-commerce Website which want to add a product to the database. Here is UI for adding products to the database of e-commerce of the website.

SCREENSHOT OF APPLICATION

You can see in the above image that it has three different fields.

Field is the above Image

  • Product Name
  • Product Price
  • Product Description

The above UI is made using the Bootstrap. Bootstrap is an open-source toolkit for developing with HTML, CSS, and JS. It helps us to quickly prototype our ideas or build your entire app.

You need to worry about the above UI code as I am going to provide a complete source code.

index.html
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>E-Commerce Company</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <div class="container">
            <a class="navbar-brand" href="#">E-Commerce Company</a>
            <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarText" aria-controls="navbarText" aria-expanded="false" aria-label="Toggle navigation">
                <span class="navbar-toggler-icon"></span>
            </button>
            <div class="collapse navbar-collapse" id="navbarText">
                <ul class="navbar-nav ml-auto">
                <li class="nav-item active">
                    <a class="nav-link" href="./">Home</a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="./show.php">All Products</a>
                </li>
                </ul>
            </div>
        </div>
    </nav>
    <div class="jumbotron">
        <h1 class="text-center">
        Add Product
        </h1>
    </div>
    <div class="container">
        <div class="row">
            <div class="col-md-6 offset-md-3 col-sm-12">
                <form action="./submit.php" method="POST">
                    <div class="form-group">
                        <label for="name">Name</label>
                        <input type="text" class="form-control" name="pname" id="name" value="<?= $olddata['name'] ?>">
                    </div>
                    <div class="form-group">
                        <label for="price">Price</label>
                        <input type="text" class="form-control" name="pprice" id="price" value="<?= $olddata['price'] ?>">
                    </div>
                    <div class="form-group">
                        <label for="description">Description</label>
                        <textarea class="form-control" name="pdescription" id="description" cols="30" rows="6"><?= $olddata['description'] ?></textarea>
                    </div>
                    <input type="submit" name="submitForm" value="submit" class="btn btn-primary btn-block">
                </form>
            </div>
        </div>
    </div>
</body>
</html>

Highlighted Line

  • #7 : This line is adding bootstrap css to the our webpage.
  • #36 : This line says that our form will be submitted to submit.php and method is POST.
  • #39, #43, #47 : You can see the name attribute in these three lines. Name attribute help us in getting the data at the backend.

Step 2: Getting data of form #

  • In the last step at Line #7, I mention to submit form at form.php.
  • In this step, we will create form.php and get the data which will be submitted by that form.
submit.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
<?php

    if(isset($_POST['submitForm'])){
        $name = $_POST['pname'];
        $price = $_POST['pprice'];
        $description = $_POST['pdescription'];
        echo "Name : $name <br> Price : $price <br> Description : $description";
    }else{
        // Someone open this page by entering the `submit.php` page url
        echo "no submit";
    }
?>
  • #2, I am using String isset. isset is used to check if variable is declared and is different than NULL. In our case it is checking for NULL.
  • #6, I am using String String Interpolation. String Interpolation is used to insert variable in the string.

Step 3: Creating database in MySQL #

Let’ Create the database and table in MySQL. So, that we can insert data into the Tables.

There is phpmyadmin which help us in doing all the database thing using GUI.

  • Open phpmyadmin, it is usually available at localhost/phpmyadmin
  • In the left top you will find new button. click on that to create new database.
  • Enter the database and click on Create ,my database name is crud.
  • Now create a table with four columns.
    • id : type: int with auto-increment
    • name : type: varchar length : 32
    • price : type int
    • description : type varchare length : 512

That’s it, you are ready with you table. We will be doing all four operation on this table only.

Step 4: Connecting to MySQL #

Now we will make a connection between PHP and MySQL. I am going to make a connection in a new file db.php. I am doing this in new file because to Create, Read, Update and Delete we need the connection. If I separate the connection file we can reuse this as many times as we want.

db.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
<?php
    $server = "localhost";
    $user = "root";
    $pass = "";
    $db = "crud"

    $con = new mysqli($server, $user, $pass, $db);

    if($con->connect_error){
        die("Connection Error");
    }
    else{
        echo "Connected Successfully";
    }

?>

Once you added this code to you db.php. It will try to establish the connection with MySQL. If everything goes well then it will show you a message on the browser that Connected Successfully.

Step 5: Inserting data to MySQL #

We made a connection between PHP and MySQL in then in db.php. It’s time to use the connection to use in inserting the data into the database.

submit.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
    require_once('db.php');

    if(isset($_POST['submitForm'])){
        $name = $_POST['pname'];
        $price = $_POST['pprice'];
        $description = $_POST['pdescription'];

        $sql = "INSERT INTO `product`(`name`, `price`, `description`) 
                VALUES ('$name', $price, '$description')";

        if($con->query($sql) === TRUE){
            echo "Data Inserted Successfully";
        }else{
            echo "something went wrong";
        }
    }else{
        // Someone open this page by entering the `submit.php` page url
        echo "no submit";
    }
?>

Line #2 : It executes the db.php first then it moves forward in submit.php. It means whatever we did in the db.php will be executed first then it will follow up the submit.php. If you remember we defined $con variable in the db.php which stores the connection object. we will use the con variable to insert data in the database.

Line #9 : It is just a SQL Query which is being build using php form data. Whatever we are sending from the form is being set as the value of MySQL fields.

Line #12 : It is just confirming if the query get executed successfully or not.

Step 6: Fetching Data from the MySQL #

In this step, we will be fetching the data from the database and showing at our website.

  • Complete code of fetching the data is little long because it includes markup also.
  • I highlighted the code which you must see and is very important to understand the fetching data.

SCREENSHOT OF SHOW Page

show.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
<?php
    require_once('db.php');

    $sql = "SELECT * FROM `product`";
    $result = $con->query($sql);

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Product Company</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <div class="container">
            <a class="navbar-brand" href="#">Product Company</a>
            <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarText" aria-controls="navbarText" aria-expanded="false" aria-label="Toggle navigation">
                <span class="navbar-toggler-icon"></span>
            </button>
            <div class="collapse navbar-collapse" id="navbarText">
                <ul class="navbar-nav ml-auto">
                <li class="nav-item active">
                    <a class="nav-link" href="http://localhost/crud/">Home</a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="http://localhost/crud/show.php">All Products</a>
                </li>
                </ul>
            </div>
        </div>
    </nav>
    <div class="jumbotron">
        <h1 class="text-center">
            Product Company
        </h1>
    </div>
    <div class="container">
        <table class="table table-striped table-borderrer">
            <tr>
                <th>ID#</th>
                <th>Name</th>
                <th>Price</th>
                <th>Description</th>
                <th>Actions</th>
            </tr>
            <?php
                if($result->num_rows > 0 ){
                    while($row = $result->fetch_assoc()){
                        echo "<tr>";
                        echo "<td>" . $row['id'] . "</td>";
                        echo "<td>" . $row['name'] . "</td>";
                        echo "<td> Rs. " . $row['price'] . "</td>";
                        echo "<td>" . $row['description'] . "</td>";
                        echo "<td>";
                        echo "<div class='btn-group'>";
                        echo "<a class='btn btn-secondary' href='./update.php?id=" .$row['id'] ."'> Update </a>";
                        echo "<a class='btn btn-danger' href='./delete.php?id=" .$row['id'] ."'> Delete</a>";
                        echo "</div>";
                        echo "</td>";
                        echo "</tr>";
                    }
                }
            ?>
        </table>
    </div>
</body>
</html>

Line #2 : I believe you understand,it’s responsible for making connection with MySQL(database).

Line #50-#68 : This is responsible for showing doing all magic. Showing data to us in html page.

  • #50 : First we are checking if we got some data from the database because there is possibilities that there will be no data in the table. You can show NO DATA AVAILABLE in else case, if you want.
  • #51 : Once we are confirm that we have data then we are running a loop through all our data.

Once you understand how fetching and loop is working then you got the magic. Now you are also magician.

Step 7: Updating Data from the MySQL #

This feature has lots of code but only some code is logical. Most of the code is related to markup and UI.

  • For convince I highlighted all the code which is logical or which you must see.
edit.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
<?php
    require_once('db.php');

    if(!isset($_GET['id'])){
        // redirect to show page
        die('id not provided');
    }

    $id =  $_GET['id'];
    $sql = "SELECT * FROM `product` where id = $id";
    $result = $con->query($sql);
    if($result->num_rows != 1){
        // redirect to show page
        die('id is not in db');
    }
    $data = $result->fetch_assoc();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Product Company</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <div class="container">
            <a class="navbar-brand" href="#">Product Company</a>
            <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarText" aria-controls="navbarText" aria-expanded="false" aria-label="Toggle navigation">
                <span class="navbar-toggler-icon"></span>
            </button>
            <div class="collapse navbar-collapse" id="navbarText">
                <ul class="navbar-nav ml-auto">
                <li class="nav-item active">
                    <a class="nav-link" href="http://localhost/crud/">Home</a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="http://localhost/crud/show.php">All Products</a>
                </li>
                </ul>
            </div>
        </div>
    </nav>
    <div class="jumbotron">
        <h1 class="text-center">
            Product Company
        </h1>
    </div>
    <div class="container">
        <div class="row">
            <div class="col-md-6 offset-md-3 col-sm-12">
                <form action="./modify.php?id=<?= $id ?>" method="POST">
                <h3>Edit Form</h3>
                    <div class="form-group">
                        <label for="name">Name</label>
                        <input type="text" class="form-control" name="pname" id="name" value="<?= $data['name']?>">
                    </div>
                    <div class="form-group">
                        <label for="price">Price</label>
                        <input type="text" class="form-control" name="pprice" id="price" value="<?= $data['price']?>">
                    </div>
                    <div class="form-group">
                        <label for="description">Description</label>
                        <textarea class="form-control" name="pdescription" id="description" cols="30" rows="10"><?= $data['description']?></textarea>
                    </div>
                    <input type="submit" name="editForm" value="submit" class="btn btn-primary btn-block">
                </form>
            </div>
        </div>
    </div>
</body>
</html>

Now it’s time to handle the data which you requested for the modification. If you know the SQL query that how to update the data this is not any magic for you.

modify.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
    require_once('db.php');

    if(isset($_GET['id']) && isset($_POST['editForm'])){
        $id = $_GET['id'];
        $name = $_POST['pname'];
        $price = $_POST['pprice'];
        $description = $_POST['pdescription'];

        $sql = "UPDATE `product` SET 
                `name`= '$name',
                `price`= '$price',
                `description`= '$description' 
                WHERE id = $id";

        if($con->query($sql) === TRUE){
            header('Location: http://localhost/crud/show.php');
        }else{
            echo "something went wrong";
        }
        
    }else{
        echo "invalid request";
    }
?>

Step 8: Delete Data from the MySQL #

Now it’s time to delete the data. Deleting the data is very simple. You just have to tell the id of the data. Based on the ID of the data it will build a SQL query and execute it to delete .

delete.php
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
<?php
    require_once('db.php');

    if(isset($_GET['id'])){
        $id = $_GET['id'];
        $sql = "DELETE FROM `product` WHERE id = $id";

        if($con->query($sql) === TRUE){
            header('Location: http://localhost/crud/show.php');
        }else{
            echo "something went wrong";
        }
        
    }else{
        // redirect to show with error
        die('id not provided');
    }

?>

Remember: Delete query is never invalid. If you write a query for deleting the data and data doesn’t exist, then also it will return TRUE.

Share this article

Comment Box

Subscribe to our Newsletter


Tweet this article