OOP PHP CRUD Operation Using MySQLi - Part 1

Submitted by razormist on Wednesday, January 18, 2017 - 18:18.

In this tutorial, we will tackle about Object Oriented Programming PHP CRUD using MySQLi as a database server. Object Oriented Programming is an organize structured that consist of different functions inside a class. It is mostly used by an advanced programmer because for them this is some what a new challenge for their programming carreer. Let's see how OOP is done in creating a connection. OOP way

class db{
  public $conn
 
  public function __construct(){
    $this->conn->connect();
  }
 
  public function connect(){
    $this->conn = new mysqli('localhost', 'root', '', 'yourdb');
  } 
}

Normal way

  mysqli_connect('localhost', 'root', '', 'yourdb');

You'll see that OOP is a little bit more complicated than the way we normally use. By the way, this tutorial is consist of two parts, the one that we will do for now is on how to create and read the data in the database. So let's start coding. Creating the database Open your database web server then create a database name in it 'crud', after that click SQL and copy/paste the code below.

CREATE TABLE `member` (
  `mem_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  PRIMARY KEY (`mem_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Creating the database connection Open your any kind of text editor(notepadd++, etc..). Then just copy/paste the code below then name it 'config.php'

<?php
        define('db_host', 'localhost');
        define('db_user', 'root');
        define('db_pass', '');
        define('db_name', 'crud');
        
        class db_connect{       
                public $host = db_host;
                public $user = db_user;
                public $pass = db_pass;
                public $dbname = db_name;
                public $conn;
                public $error;
                
                public function connect(){
                        $this->conn = new mysqli($this->host, $this->user, $this->pass, $this->dbname);
                        if(!$this->conn){
                                $this->error = "Fatal Error: Can't connect to database" . $this->connect->connect_error();
                                return false;
                        }
                }
        }       
?>

The code above will create the database connection script. The CRUD(Create and Read) functions After creating the database connect, we will now create the CRUD function inside the class, to do that just copy/paste the code below then name it 'class.php'

<?php
        require 'config.php';
        
        class db_class extends db_connect{      
                
                public function __construct(){
                        $this->connect();
                }
                
                public function create($firstname, $lastname){
                        $stmt = $this->conn->prepare("INSERT INTO `member` (`firstname`, `lastname`) VALUES (?, ?)") or die($this->conn->error);
                        $stmt->bind_param("ss", $firstname, $lastname);
                        if($stmt->execute()){
                                $stmt->close();
                                $this->conn->close();
                                return true;
                        }
                }
                
                public function read(){
                        $stmt = $this->conn->prepare("SELECT * FROM `member` ORDER BY `lastname` ASC") or die($this->conn->error);
                        if($stmt->execute()){
                                $result = $stmt->get_result();
                                return $result;
                        }
                }
        }       
?>

The code above will generate the request after the function is called, The create() function will store the value into the database, while the read() function will retrieve the data within the database and display it in the webpage The Mark-up Form This is the form we will use in the CRUD functions, copy/paste the code below and name it 'index.php'

<!DOCTYPE html>
<html lang = "en">
        <head>
                <meta charset = "UTF-8" name = "viewport" content = "width-device=width, initial-scale=1" />
                <link rel = "stylesheet" type = "text/css" href = "css/bootstrap.css" />
                <title>OOP PHP CRUD Operation</title>
        </head>
<body>
        <nav class = "navbar navbar-default">
                <div class = "container-fluid">
                        <a class = "navbar-brand" href = "https://www.sourcecodester.com">Sourcecodester</a>
                </div>
        </nav>
        <div class = "row">     
                <div class = "col-md-3">
                </div>
                <div class = "col-md-6 well">
                        <h3 class = "text-primary">OOP PHP CRUD Operation Using MySQLi - Part 1</h3>
                        <hr style = "border-top:1px dotted #000;"/>
                        <form method  = "POST" class = "form-inline" action = "create.php">
                                <div class = "form-group">
                                        <label>Firstname:</label>
                                        <input type  = "text" name = "firstname" class = "form-control" required = "required"/>
                                </div>
                                <div class = "form-group">
                                        <label>Lastname:</label>
                                        <input type  = "text" name = "lastname" class = "form-control" required = "required"/>
                                </div>
                                <div class = "form-group">
                                        <button name = "save" class = "btn btn-primary"><span class = "glyphicon glyphicon-plus"></span> Add</button>
                                </div>
                        </form>
                        <br />
                        <table class = "table table-bordered alert-warning table-hover">
                                <thead>
                                        <th>Firstname</th>
                                        <th>Lastname</th>
                                        <th>Action</th>
                                </thead>
                                <tbody>
                                <?php
                                        require 'class.php';
                                        $conn = new db_class();
                                        $read = $conn->read();
                                        while($fetch = $read->fetch_array()){ 
                                ?>
                                        <tr>
                                                <td><?php echo $fetch['firstname']?></td>
                                                <td><?php echo $fetch['lastname']?></td>
                                                <td><center><a class = "btn btn-warning"><span class = "glyphicon glyphicon-edit"></span> Update</a> | <a class = "btn btn-danger"><span class = "glyphicon glyphicon-trash"></span> Delete</a></center></td>
                                        </tr>
                                <?php
                                        }
                                ?>      
                                </tbody>
                        </table>
                </div>
        </div>
</body>
</html>

In this form you'll notice that we used read() inside the table, the function read() here call back the requested function within the class.php to display it in an array of data The Save query After creating the form we will now create the submit query, copy/paste the code below then name it 'create.php'

<?php
        require_once 'class.php';
        
        if(ISSET($_POST['save'])){      
                $firstname = $_POST['firstname'];
                $lastname  = $_POST['lastname'];
                $conn = new db_class();
                $conn->create($firstname, $lastname);
                header('location: index.php');
        }       
        
?>

The code above stored the all the value within the form and call the function create() to put all the stored data into database. There you have it, we have created a form on how to create and read the data in the database in OOP way. I hope that this tutorial help you. On my next tutorial, we will tackle on how to update and delete a data based on the code I have given. Just stay tuned in this site. Enjoy Coding!!