Introduction


CRUD is the ancronym of Create, Read, Update, Delete. A CRUD Application handles the four basic operations manipulating the database.

In this tutorial, we will learn how perform the four operation of CRUD without any framework. We will try to use a simple PHP code so as it can be understood by everyone.

We will begin by creating the connection file to database, create our database and insert sample data. Then we will create the default page (index page) and the other pages required for performing the CRUD operations.

Content


  •      Screen Shots
  • 1- Database Connection.
  • 2- Database content.
  • 3- Index Page.
  • 4- Display products list.
  • 5- CREATE operation.
  • 6- READ operation
  • 7- UPDATE operation
  • 8- DELETE operation
  • 9- Error Page

  • Screenshots


    Build a PHP Crud Application


    1- Database Connection


    To handle the connection to our database. Create a file and name it db_connect.php. Then add the following code :

    <?php

    //Database Connection file. Update with your Database information .

        define ("DB_HOST", "localhost"); //Databse Host.

        define ("DB_USER", "root"); //Databse User.

        define ("DB_PASS", ""); //database password.

        define ("DB_NAME", "tutos"); //database Name.

    $db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    //

    //$db->set_charset('utf8');

    mysqli_set_charset($db,"utf8");

    if($db->connect_errno > 0){

        die('Unable to connect to database ['.$db->connect_error.']');

    }

    Update the file with your database information


2- Database Content


Create a table named products in your database and add the following SQL code :
--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `productId` int(11) NOT NULL,
  `productBarcode` varchar(20) NOT NULL,
  `productName` varchar(100) NOT NULL,
  `UnitPrice` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`productId`, `productBarcode`, `productName`, `UnitPrice`) VALUES
(1, '1254785496325', 'Fruit Juice Tropical CAN 250 ml', 0.75),
(2, '1254236552148', 'Fruit Juice Mojito CAN 250 ml', 0.75),
(3, '1254785665923', 'Carbonated Drink Cola 300 ml', 1.25),
(4, '1254785696523', 'Carbonated Juice 250 ml', 1),
(5, '1236521545879', 'Fried Chicken Frozen 500 g', 2.35),
(6, '1254785696325', 'Fresh Fish 1 Kg', 4.45);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`productId`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `productId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;COMMIT;
This code will create the table and insert test data.

3- Index Page


In ths default page, we will call all the CRUD operations according to what we want to do. For that, we will use a Switch ... case conditional statement by passing as parameter the action that will be handeled.

Create a page named index.php in the root of your server and add the following code :

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html lang="en">

<head>

  <title>MESDEV.COM || Build a PHP Crud Application</title>

  <meta charset="utf-8">

  <meta name="viewport" content="width=device-width, initial-scale=1">

  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">

  <link rel="stylesheet" type="text/css" href="css/style.css" media="all" />

  <link rel="stylesheet" type="text/css" href="css/select2.css" media="all" />

  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

  

</head>

<body>

    <div class="container">    

        <div class="row content">

            <div class="well">

                <div class="wrapper">

                    <div class="container-fluid">

                        <div class="row">

                            <div class="col-md-12">

                                <?php 

                                    // extract the action parameter from the url

                                    

                                    if (isset($_GET['action']) && $_GET['action'] !='') {

                                        $action = $_GET['action'];

                                    } else {

                                        $action ='';

                                    }

                                    switch  ($action) {

                                        case  '':

                                            //default page that shows all the products

                                            include("pages/default.php");

                                            break;

                                        case 'create':

                                            //page that handles the CREATE operation

                                            include("pages/create.php");

                                            break;

                                        case 'edit':

                                            //page that handles the UPDATE operation

                                            include("pages/update.php");

                                            break;

                                        case 'display':

                                            //page that handles the READ operation

                                            include("pages/read.php");

                                            break;

                                        case 'remove':

                                            //page that handles the DELETE operation

                                            include("pages/delete.php");

                                            break;

                                        default:

                                            //page that handles any error occured

                                            include("pages/error.php");

                                    }

                                ?>

                                </div>

                            </div>        

                        </div>

                    </div>

                </div>

            </div>

        </div>

    </body>

</html>

As you can see, the index page will call, according to the action handled, the page required for performing this action.

Before begining the CRUD operations, let's create the default page that will display the products details and from this page we can perform the CRUD operations.


4- Display products list


This page is the head of the application as it contains the list of product and links to perform CRUD operations to each product

Create a folder named pages where we will put the pages required for the CRUD operations. Create in this folder a page named default.php which will display the products list. Then, add to this page the following code :

<div class="page-header clearfix">

    <h2 class="pull-left">Products Details</h2>

    <a href="index.php?action=create" class="btn btn-success pull-right">Add New Product</a>

</div>

<?php

                                    

// Attempt select query execution

$sql = "SELECT * FROM products";

if($result = mysqli_query($db, $sql)){

    if(mysqli_num_rows($result) > 0){

        echo "<table class='table table-bordered table-striped'>";

            echo "<thead>";

                echo "<tr>";

                    echo "<th>#</th>";

                    echo "<th>Barcode</th>";

                    echo "<th>Description</th>";

                    echo "<th>Unit Price</th>";

                    echo "<th>Action</th>";

                echo "</tr>";

            echo "</thead>";

            echo "<tbody>";

            $count = 0;

            while($row = mysqli_fetch_array($result)){

                echo "<tr>";

                    echo "<td>" . ++$count . "</td>";

                    echo "<td>" . $row['productBarcode'] . "</td>";

                    echo "<td>" . $row['productName'] . "</td>";

                    echo "<td align='right'>$ " . number_format($row['UnitPrice'],2) . "</td>";

                    echo "<td>";

                        echo "<a href='index.php?action=display&id=". $row['productId'] ."' title='View Product' data-toggle='tooltip'><span class='glyphicon glyphicon-eye-open'></span></a>";

                        echo "<a href='index.php?action=edit&id=". $row['productId'] ."' title='Update Product' data-toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>";

                        echo "<a href='index.php?action=remove&id=". $row['productId'] ."' title='Delete Product' data-toggle='tooltip'><span class='glyphicon glyphicon-trash'></span></a>";

                    echo "</td>";

                echo "</tr>";

            }

            echo "</tbody>";                            

        echo "</table>";

        // Free result set

        mysqli_free_result($result);

    } else{

        echo "<p class='small alert alert-danger text-center'><em><i class='fa fa-exclamation-triangle'></i> No products found.</em></p>";

    }

} else{

    echo "ERROR: Could not able to execute $sql. " . mysqli_error($db);

}

// Close connection

mysqli_close($db);

?>

As written in the code, this page will display the list of products with their details as well as three links to maintain the call to the Read, Edit and Delete pages and also a button to call the Create page.

Let's begin by the Create action handled by the Create page


5- CREATE operation


The Create page is responsible for handling the CREATE action.
Add a page named create.php in the pages folder. Then add the following code :

<?php

// Define variables and initialize with empty values

$barcode = $name = $price = "";

$barcode_err = $name_err = $price_err = "";

 

// Processing form data when form is submitted

if($_SERVER["REQUEST_METHOD"] == "POST"){

    // Validate barcode

    $input_barcode = trim($_POST["barcode"]);

    if(empty($input_barcode)){

        $barcode_err = "Please enter a barcode.";

    } else{

        $barcode = $input_barcode;

    }

    

    // Validate name

    $input_name = trim($_POST["name"]);

    if(empty($input_name)){

        $name_err = "Please enter a description for the product.";     

    } else{

        $name = $input_name;

    }

    

    // Validate price

    $input_price = trim($_POST["price"]);

    if(empty($input_price)){

        $price_err = "Please enter the price amount.";     

    }  else{

        $price = $input_price;

    }

    

    // Check input errors before inserting in database

    if(empty($barcode_err) && empty($name_err) && empty($price_err)){

        // Prepare an insert statement

        $sql = "INSERT INTO products (productBarcode, productName, UnitPrice) VALUES (?, ?, ?)";

         

        if($stmt = mysqli_prepare($db, $sql)){

            // Bind variables to the prepared statement as parameters

            mysqli_stmt_bind_param($stmt, "sss", $param_barcode, $param_name, $param_price);

            

            // Set parameters

            $param_barcode = $barcode;

            $param_name = $name;

            $param_price = $price;

            

            // Attempt to execute the prepared statement

            if(mysqli_stmt_execute($stmt)){

                // Records created successfully. Redirect to landing page

                header("location: index.php");

                exit();

            } else{

                echo "Something went wrong. Please try again later.";

            }

        }

         

        // Close statement

        mysqli_stmt_close($stmt);

    }

    

    // Close connection

    mysqli_close($db);

}

?>


<div class="page-header">

    <h2>Create Product</h2>

</div>

<p>Please fill this form and submit to add product details to the database.</p>

<form action="index.php?action=create" method="post">

    <div class="form-group <?php echo (!empty($barcode_err)) ? 'has-error' : ''; ?>">

        <label>Barcode</label>

        <input type="text" name="barcode" class="form-control" value="<?php echo $barcode; ?>">

        <span class="help-block"><?php echo $barcode_err;?></span>

    </div>

    <div class="form-group <?php echo (!empty($name_err)) ? 'has-error' : ''; ?>">

        <label>Description</label>

        <textarea name="name" class="form-control"><?php echo $name; ?></textarea>

        <span class="help-block"><?php echo $name_err;?></span>

    </div>

    <div class="form-group <?php echo (!empty($price_err)) ? 'has-error' : ''; ?>">

        <label>Unit Price</label>

        <input type="number" min="0" step="0.01" name="price" class="form-control" value="<?php echo $price; ?>">

        <span class="help-block"><?php echo $price_err;?></span>

    </div>

    <input type="submit" class="btn btn-primary" value="Submit">

    <a href="index.php" class="btn btn-default">Cancel</a>

</form>

                                

The create page will check the product data and add them to the database


6- READ operation


The read page will display the details of the product passed.
Create a new page named read.php in the pages folder and add the following code to it :

<?php

    // Check existence of id parameter before processing further

if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){

   

    // Prepare a select statement

    $sql = "SELECT * FROM products WHERE productId = ?";

    

    if($stmt = mysqli_prepare($db, $sql)){

        // Bind variables to the prepared statement as parameters

        mysqli_stmt_bind_param($stmt, "i", $param_id);

        

        // Set parameters

        $param_id = trim($_GET["id"]);

        

        // Attempt to execute the prepared statement

        if(mysqli_stmt_execute($stmt)){

            $result = mysqli_stmt_get_result($stmt);

    

            if(mysqli_num_rows($result) == 1){

                /* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */

                $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

                

                // Retrieve individual field value

                $barcode = $row["productBarcode"];

                $name = $row["productName"];

                $price = $row["UnitPrice"];

            } else{

                // URL doesn't contain valid id parameter. Redirect to error page

                header("location: error.php");

                exit();

            }

            

        } else{

            echo "Oops! Something went wrong. Please try again later.";

        }

    }

     

    // Close statement

    mysqli_stmt_close($stmt);

    

    // Close connection

    mysqli_close($db);

} else{

    // URL doesn't contain id parameter. Redirect to error page

    header("location: error.php");

    exit();

}

?>


<div class="page-header">

    <h1>View Product</h1>

</div>

<div class="form-group">

    <label>Barcode</label>

    <p class="form-control-static"><?php echo $row["productBarcode"]; ?></p>

</div>

<div class="form-group">

    <label>Description</label>

    <p class="form-control-static"><?php echo $row["productName"]; ?></p>

</div>

<div class="form-group">

    <label>Unit Price</label>

    <p class="form-control-static"><?php echo $row["UnitPrice"]; ?></p>

</div>

<p><a href="index.php" class="btn btn-primary">Back</a></p>

                                


7- UPDATE operation


The edit page will save the changes added to the details of the product passed.
Create a new page named edit.php in the pages folder and add the following code to it :

<?php

// Define variables and initialize with empty values

$barcode = $name = $price = "";

$barcode_err = $name_err = $price_err = "";

 

// Processing form data when form is submitted

if(isset($_POST["id"]) && !empty($_POST["id"])){

    // Get hidden input value

    $id = $_POST["id"];

    

    // Validate barcode

    $input_barcode = trim($_POST["barcode"]);

    if(empty($input_barcode)){

        $barcode_err = "Please enter a barcode.";

    } else{

        $barcode = $input_barcode;

    }

    

    // Validate name name

    $input_name = trim($_POST["name"]);

    if(empty($input_name)){

        $name_err = "Please enter a product description.";     

    } else{

        $name = $input_name;

    }

    

    // Validate price

    $input_price = trim($_POST["price"]);

    if(empty($input_price)){

        $price_err = "Please enter the price.";     

    } else{

        $price = $input_price;

    }

    

    // Check input errors before inserting in database

    if(empty($barcode_err) && empty($name_err) && empty($price_err)){

        // Prepare an update statement

        $sql = "UPDATE products SET productBarcode=?, productName=?, UnitPrice=? WHERE productId=?";

         

        if($stmt = mysqli_prepare($db, $sql)){

            // Bind variables to the prepared statement as parameters

            mysqli_stmt_bind_param($stmt, "sssi", $param_barcode, $param_name, $param_price, $param_id);

            

            // Set parameters

            $param_barcode = $barcode;

            $param_name = $name;

            $param_price = $price;

            $param_id = $id;

            

            // Attempt to execute the prepared statement

            if(mysqli_stmt_execute($stmt)){

                // Records updated successfully. Redirect to landing page

                header("location: index.php");

                exit();

            } else{

                echo "Something went wrong. Please try again later.";

            }

        }

         

        // Close statement

        mysqli_stmt_close($stmt);

    }

    

    // Close connection

    mysqli_close($db);

} else{

    // Check existence of id parameter before processing further

    if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){

        // Get URL parameter

        $id =  trim($_GET["id"]);

        

        // Prepare a select statement

        $sql = "SELECT * FROM products WHERE productId = ?";

        if($stmt = mysqli_prepare($db, $sql)){

            // Bind variables to the prepared statement as parameters

            mysqli_stmt_bind_param($stmt, "i", $param_id);

            

            // Set parameters

            $param_id = $id;

            

            // Attempt to execute the prepared statement

            if(mysqli_stmt_execute($stmt)){

                $result = mysqli_stmt_get_result($stmt);

    

                if(mysqli_num_rows($result) == 1){

                    /* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */

                    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

                    

                    // Retrieve individual field value

                    $barcode = $row["productBarcode"];

                    $name = $row["productName"];

                    $price = $row["UnitPrice"];

                } else{

                    // URL doesn't contain valid id. Redirect to error page

                    header("location: error.php");

                    exit();

                }

                

            } else{

                echo "Oops! Something went wrong. Please try again later.";

            }

        }

        

        // Close statement

        mysqli_stmt_close($stmt);

        

        // Close connection

        mysqli_close($db);

    }  else{

        // URL doesn't contain id parameter. Redirect to error page

        header("location: error.php");

        exit();

    }

}

?>


<div class="page-header">

    <h2>Update Product</h2>

</div>

<p>Please edit the product details and submit save the updates.</p>

<form action="index.php?action=edit" method="post">

    <div class="form-group <?php echo (!empty($barcode_err)) ? 'has-error' : ''; ?>">

        <label>Barcode</label>

        <input type="text" name="barcode" class="form-control" value="<?php echo $barcode; ?>">

        <span class="help-block"><?php echo $barcode_err;?></span>

    </div>

    <div class="form-group <?php echo (!empty($name_err)) ? 'has-error' : ''; ?>">

        <label>Description</label>

        <textarea name="name" class="form-control"><?php echo $name; ?></textarea>

        <span class="help-block"><?php echo $name_err;?></span>

    </div>

    <div class="form-group <?php echo (!empty($price_err)) ? 'has-error' : ''; ?>">

        <label>Unit Price</label>

        <input type="number" min="0" step="0.01" name="price" class="form-control" value="<?php echo $price; ?>">

        <span class="help-block"><?php echo $price_err;?></span>

    </div>

    <input type="hidden" name="id" value="<?php echo $id; ?>"/>

    <input type="submit" class="btn btn-primary" value="Submit">

    <a href="index.php" class="btn btn-default">Cancel</a>

</form>

                                


8- DELETE operation


The delete page will remove the passed product from database.
Create a new page named delete.php in the pages folder and add the following code :

<?php

    // Process delete operation after confirmation

if(isset($_POST["id"]) && !empty($_POST["id"])){

    

    // Prepare a delete statement

    $sql = "DELETE FROM products WHERE productId = ?";

    

    if($stmt = mysqli_prepare($db, $sql)){

        // Bind variables to the prepared statement as parameters

        mysqli_stmt_bind_param($stmt, "i", $param_id);

        

        // Set parameters

        $param_id = trim($_POST["id"]);

        

        // Attempt to execute the prepared statement

        if(mysqli_stmt_execute($stmt)){

            // Records deleted successfully. Redirect to landing page

            header("location: index.php");

            exit();

        } else{

            echo "Oops! Something went wrong. Please try again later.";

        }

    }

     

    // Close statement

    mysqli_stmt_close($stmt);

    

    // Close connection

    mysqli_close($db);

} else{

    // Check existence of id parameter

    if(empty(trim($_GET["id"]))){

        // URL doesn't contain id parameter. Redirect to error page

        header("location: error.php");

        exit();

    }

}

?>


<div class="page-header">

    <h1>Delete Product</h1>

</div>

<form action="index.php?action=remove" method="post">

    <div class="alert alert-danger fade in">

        <input type="hidden" name="id" value="<?php echo trim($_GET["id"]); ?>"/>

        <p>Are you sure you want to delete this product?</p><br>

        <p>

            <input type="submit" value="Yes" class="btn btn-danger">

            <a href="index.php" class="btn btn-default">No</a>

        </p>

    </div>

</form>

                                



9- Error Page


Finally , it is better to create an error page that can handle any miss-passed parameters or an error that occur during the execution of the crud operations.
Create a new page named error.php in the pages folder and add the following code to it :

<div class="page-header">

    <h1>Invalid Request</h1>

</div>

<div class="alert alert-danger fade in">

    <p>Sorry, you've made an invalid request. Please <a href="index.php" class="alert-link">go back</a> and try again.</p>

</div>