PHP CRUD Application without any framework

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);
} 
?>

11 items available

Scripts & Templates