PHP MYSQL CRUD example( Using PDO connection ,bootstrap plugin and oops concept )

Hello, friends, this is my first Post on this Blogs :)

Hope you will all like this tutorial. Please comment your feedback. This post is regarding CRUD ( Create, Read , Update , Delete ). I have used bootstrap as the frontend for designing the UI.

And I have used the oops concept to do the crud operation . Friends you will find the I am using a single page for adding and updating the product details. I have used the product details because In future I am going to publish some e-commerce site related tutorials like checking out adding the element into the cart generating the invoice and much more

I have used the product details because In future I am going to publish some e-commerce site related tutorials like checking out , adding the element into the cart generating the invoice and much more
article .

I have divided the post into many parts.

1 ) Database table creation : –

I have created a database “blog ” in this I have created a table product_details ( In this some details of product will be inserted )

CREATE TABLE `product_details` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(200) NOT NULL,
`product_price` int(10) NOT NULL,
`product_weight` int(10) NOT NULL,
`product_sku` varchar(100) NOT NULL,
`product_color` char(20) NOT NULL,
`product_category` varchar(200) NOT NULL,
`cdate` datetime DEFAULT NULL,
`udate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`product_id`),
KEY `product_name` (`product_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I have added 2 columns cdate (created date and udate ) by using this 2 columns we can easily track the changes made on the table .

2 ) Display of output image : –

PHP CRUD OOPS Boostrap

PHP CRUD Bootstrap

a) We have added the pagination also please the picture .

3 )

CONFIG :- DataBase connection file

Set the credential of the database and make the connection to the database. We have used the PDO
connection to establish.And OOPS method to define the database and connection.

<!--?php /** * @author Abhay Pandey (abhayhk29@gmail.com) * Date : 14 May 2016 * Config class containing the database connectivity */ class DBConnection{ //Please specify your own database credentials private $host = "localhost"; private $db_name = "blog"; private $user_name = "root"; private $password = ""; public $connection = null; public function getConnectData(){ $this-&gt;connection = null;&lt;br ?--> 

&lt;?php

/**
* @author Abhay Pandey (xyz@gmail.com)
* Date : 14 June 2016
* Config class containing the database connectivity
*/

class DBConnection{

//Please specify your own database credentials

private $host = "localhost";
private $db_name = "blog";
private $user_name = "root";
private $password = "";
public $connection = null;

public function getConnectData(){
$this-&gt;connection = null;
try {
$this-&gt;connection = new PDO("mysql:host=". $this-&gt;host . ";dbname=".$this-&gt;db_name, $this-&gt;user_name, $this-&gt;password);
} catch (Exception $exc) {
echo "connection error please check your credentials ". $exc-&gt;getTraceAsString();
}

return $this-&gt;connection;
}

public static function disconnection(){
self::$connection = null;
}
}

4) Layout page where we have put the layout of the page . In that page user will see the entered products details.
In this page user has a option to edit and delete the product details . A button is given to create the new product details. Please see the code given below.

<?php
   
//  $pdo = DBConnection::getConnectData();
  $page = isset($_GET['page']) ? $_GET['page'] : 1;
  
  $records_per_page = 5;
  
  $from_record_page = ($records_per_page * $page) - $records_per_page;  
  include 'includes/config.php';
  include 'includes/dataProduct.php';
  $databaseConnection = new DBConnection();
  $db = $databaseConnection->getConnectData();  
  $product = new ProductData($db);
  
  $stmt = $product->readAllProductDetails($page, $from_record_page, $records_per_page);
  $num = $stmt->rowCount();

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <script src="bootstrap/js/jquery.min.js"></script>
    <link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" media="screen">
    <script src="bootstrap/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container">
            <div class="row">
                <h3>Product CRUD Example</h3>
            </div>
            <p>
                <a class="btn btn-default" href="add.php" role="button">Add Product Detail</a>
            </p>
            <div class="row">
            <?php 
               if($num > 0){
            ?>
                <table class="table table-striped table-bordered">
                  <thead>
                    <tr>
                      <th>ID</th>
                      <th>Product Name</th>
                      <th>Product SKU</th>
                      <th>Product Price</th>
                      <th>Product Weight</th>
                      <th>Product Category</th>
                      <th>Action</th>
                    </tr>
                  </thead>
                  <tbody>
                  <?php
                            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                                    extract($row);
                                    ?>
                                    <tr>
                                    <?php echo "<td>{$product_id}</td>" ?>
                                    <?php echo "<td>{$product_name}</td>" ?>
                                    <?php echo "<td>{$product_sku}</td>" ?>
                                    <?php echo "<td>{$product_price}</td>" ?>
                                    <?php echo "<td>{$product_weight}</td>" ?>
                                    <?php echo "<td>{$product_category}</td>" ?>
                                    <?php echo "<td width='100px'>
                                    <a class='btn btn-warning btn-sm' href='add.php?id={$product_id}' role='button'><span class='glyphicon glyphicon-pencil' aria-hidden='true'></span></a>
                                    <a class='btn btn-danger btn-sm' href='delete.php?productID={$product_id}' role='button'><span class='glyphicon glyphicon-trash' aria-hidden='true'></span></a>
                                    </td>" ?>
                                    </tr>
                            <?php } ?>
                  </tbody>
            </table>
                <?php
            $page_dom = "index.php";
                include_once 'includes/pagination.php';
            } else {
            ?>
            <div class="alert alert-warning alert-dismissible" role="alert">
            <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            <strong>Warning!</strong> Data is still empty
            </div>
            <?php
            }
            ?>
        </div>
    </div> <!-- /container -->
  </body>
</html>

In that, you can see that I have displayed the data inside a tabular format.

5)add.php
In this page we have included code for both add and edit the product details. For further please see the code below . We have seperated the code for edit and add product details in the same page .

<?php
include 'includes/config.php';
include 'includes/dataProduct.php';

$databaseConnection = new DBConnection();
$db = $databaseConnection->getConnectData();
$product = new ProductData($db);
?>

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>PHP CRUD Example with OOPS concept</title>

        <!-- Bootstrap -->
        <link href="bootstrap/css/bootstrap.min.css" rel="stylesheet">
        <link href="bootstrap/css/bootstrapStylesheet.css" rel="stylesheet">
        <link href="bootstrap/css/bootstrap-datepicker3.min.css" rel="stylesheet">
    </head>
    <style>
        .date-form { margin: 10px; }
        label.control-label span { cursor: pointer; }
        </style>
    <body>
        <p><br/></p>
        <div class="container">
            <p>
                <a class="btn btn-primary" href="index.php" role="button">Back</a>
            </p><br/>
            <?php
            if (isset($_GET['id'])) {
                $id = $_GET['id'];
                $product->readOneProduct($id);
            }


            if ($_POST) {

                $product->productName = $_POST['product_name'];
                $product->productSKU = $_POST['product_sku'];
                $product->productPrice = $_POST['product_price'];
                $product->productWeight = $_POST['product_weight'];
                $product->productCategory = $_POST['product_category'];
                $product->productColor = $_POST['product_color'];
                if ($product->productID == '') {
                        if ($product->insertData()) {
                            ?>
                                <div class="alert alert-success alert-dismissible" role="alert">
                                    <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span
                                            aria-hidden="true">&times;</span></button>
                                    <strong>Success!</strong> <a href="index.php">View Data</a>.
                                </div>
                                    <?php
                                } else {
                                    ?>
                                <div class="alert alert-danger alert-dismissible" role="alert">
                                    <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span
                                            aria-hidden="true">&times;</span></button>
                                    <strong>Fail!</strong>
                                </div>
                                    <?php
                                }
                     } else {
                         if($product->updateProductDetails()){ ?>
                             <div class="alert alert-success alert-dismissible" role="alert">
                                    <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span
                                            aria-hidden="true">&times;</span></button>
                                    <strong>Record Updated Successfully!</strong> <a href="index.php">View Data</a>.
                                </div>
                         <?php } else { ?>
                                <div class="alert alert-danger alert-dismissible" role="alert">
                                    <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span
                                            aria-hidden="true">&times;</span></button>
                                    <strong>Record Fail to update</strong>
                                </div>
                     <?php } 
                }
                
            }
            ?>
            <form method="post" class="form-horizontal" action="">
                
            <input type="hidden" id="productID" name="productID" value="<?php echo isset($product->productID) ? $product->productID : '' ;?>"
        <div class="col-sm-6 b-r romeve_border form-horizontal">
            <div class="form-group"><label class="col-lg-3 control-label">Product Name</label>
                <div class="col-lg-9">
                    <input type="text" placeholder="Name" class="form-control" id="product_name" name="product_name" required="true" value="<?php echo isset($product->productName) ? $product->productName : ''; ?>">
                </div>
            </div>
            <div class="form-group"><label class="col-lg-3 control-label">Product SKU</label>
                <div class="col-lg-9">
                    <input type="text" placeholder="Product SKU" class="form-control" id="product_sku" name="product_sku" required="true" value="<?php echo isset($product->productSKU) ? $product->productSKU : ''; ?>">
                </div>
            </div>
            <div class="form-group"><label class="col-lg-3 control-label">Product Price</label>
                <div class="col-lg-9">
                    <input type="text" placeholder="Product Price" class="form-control" id="product_price" name="product_price" required="true" value="<?php echo isset($product->productPrice) ? $product->productPrice : ''; ?>">
                </div>
            </div>
            <div class="form-group"><label class="col-lg-3 control-label">Product Weight</label>
                <div class="col-lg-9">
                    <input type="text" placeholder="Product Weight" class="form-control required" id="product_weight" name="product_weight" required="true" value="<?php echo isset($product->productWeight) ? $product->productWeight : ''; ?>">
                </div>
            </div>
            <div class="form-group"><label class="col-lg-3 control-label">Product Color</label>
                <div class="col-lg-9">
                    <input type="text" placeholder="Product color" class="form-control required" id="product_color" name="product_color" required="true" value="<?php echo isset($product->productColor) ? $product->productColor : ''; ?>">
                </div>
            </div>
            <div class="form-group"><label class="col-lg-3 control-label">Product Category</label>
                <div class="col-lg-9">
                    <input type="text" placeholder="Product Category" class="form-control required" id="product_category" name="product_category" required="true" value="<?php echo isset($product->productCategory) ? $product->productCategory : ''; ?>">
                </div>
            </div>
        </div>
    </div>
        <div class="col-md-3">
        </div>
        <div class="col-md-3">
        <button type="submit" class="btn btn-success">Submit</button>
        </div>
    </form>
</div>

<script src="bootstrap/js/jquery.min.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>
<script src="bootstrap/js/boootstrap-datepicker.min.js.js"></script>
<script type="text/javascript">
</script>
</body>
</html


6) dataProduct.php : –
In that, page we have implemented the CRUD example with OOPS basics (create , read , update and delete )
Please see the code for further information.

<?php

/**
 * @author Abhay Pandey <abhayhk29@gmail.com>
 * Date : 14 MAy 2016
 */

class ProductData{
    
    // DataBase connection Name and table NAme
    private $conn;
    private $tableName = 'product_details';
    
    //table data objects 
    
    public $productID;
    public $productName;
    public $productPrice;
    public $productWeight;
    public $productSKU; //Stock keeping unit
    public $productColor;
    public $productCategory;
    
    public function __construct($db) {
        $this->conn = $db;
    }
    
    /**
     * Create Product Details
     */
    
    public function insertData(){
        try {
            $date  = date(time());
            $stmt1 = $this->conn->prepare("Insert INTO " . $this->tableName . " (product_name,product_price, product_sku, product_weight, product_color, product_category, cdate, udate) VALUES (?,?,?,?,?,?,?,?)");
            $stmt1->bindParam(1, $this->productName);
            $stmt1->bindParam(2, $this->productPrice);
            $stmt1->bindParam(3, $this->productSKU);
            $stmt1->bindParam(4, $this->productWeight);
            $stmt1->bindParam(5, $this->productColor);
            $stmt1->bindParam(6, $this->productCategory);
            $stmt1->bindParam(7, $date);
            $stmt1->bindParam(8, $date);
            $stmt1->execute();
            return true;
        } catch (PDOException $e) {
            echo $e->getMessage();
            return false;
        }
    }

    public function readAllProductDetails($page , $form_record_num, $records_per_page){
        
        $query = "select * from " . $this->tableName . " ORDER BY product_id LIMIT  {$form_record_num} , {$records_per_page } ";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
                
    }
    
    //used for counting the element 
    
    public function countAllProduct(){
        $query = "select product_id from ".$this->tableName;
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        
        $num = $stmt->rowCount();
        
        return $num;
    }
    
    public function readOneProduct($id){
        $query = "Select * from ".$this->tableName." where product_id = :id limit 0,1";
        $stmt = $this->conn->prepare($query);
        $stmt->execute(array(":id"=>$id));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $this->productName = $row['product_name'];
        $this->productPrice = $row['product_price'];
        $this->productSKU = $row['product_sku'];
        $this->productWeight = $row['product_weight'];
        $this->productCategory = $row['product_category'];
        $this->productColor = $row['product_color'];
        $this->productID = $row['product_id'];
    }
    
    public function updateProductDetails() {
        $query = "update " . $this->tableName . " SET 
                  product_name = :productName,
                  product_price = :productPrice,
                  product_sku = :productSKU,
                  product_weight = :productWeight,
                  product_category = :productCategory,
                  product_color = :productColor
                  where product_id = :productID";
        $stmt = $this->conn->prepare($query);
        
        $stmt->bindParam(':productName', $this->productName);
        $stmt->bindParam(':productPrice', $this->productPrice);
        $stmt->bindParam(':productSKU', $this->productSKU);
        $stmt->bindParam(':productWeight', $this->productWeight);
        $stmt->bindParam(':productCategory', $this->productCategory);
        $stmt->bindParam(':productColor', $this->productColor);
        $stmt->bindParam(':productID', $this->productID);

        if ($stmt->execute()) {
            return true;
        } else {
            return false; 
        }
    }
    
    public function deleteProduct(){
        $query = "DELETE FROM ".$this->tableName." where product_id = ? ";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1,  $this->productID);
        if($stmt->execute()){
            return true;
        } else {
            return false;
        }
        
    }

}

7)delete.php :-
In this code we are deleting the product details.If success we returning to the main page. We can add here modal popup also to confirming from the user that user want to delete or not.

<?php
    include 'includes/config.php';
    include 'includes/dataProduct.php';
    
    $databaseConnection = new DBConnection();
    $db = $databaseConnection->getConnectData();  
    
    $product = new ProductData($db);
    $product->productID = isset($_GET['productID']) ? $_GET['productID'] : die('Need Product ID');
    
    if($product->deleteProduct()){
        echo "<script>location.href='index.php'</script>";
    } else {
        echo "<script>alert('Failed to Deleted Data')</script>";
    }
?>

Here’s we have developed the Simple CRUD example with basics of code. Here may be very flaws in the article but we
have tried to implement basics od CRUD example.Please give your suggestion to improve the article. I will update the download and demo page shortely thanks for reading the article . Please give your valuable comments.

18

2 Responses

  1. Heramb
    June 25, 2016
  2. Saurabh
    June 26, 2016

Write a response