MySQL PHP MVC CRUD Without Framework

Introduction

 
This tutorial is for beginners or students. I created a functionality to add, edit and delete a record in PHP with MVC logic without Framework. Also,  explained how to create an MVC pattern in PHP. I hope it will be helpful for you to add a data table in your program.
 

Building Our MVC Framework Pattern in PHP

 
You might be wondering why we would even need to create our own framework when there are already so many good choices out there. The reason for this is so that we can gain an understanding of the underlying principles of MVC.

As we learn more about these principles, we will grow in our understanding of why the excellent MVC frameworks do things the way they do. We are not learning how to create an application in Zend Framework, or in CakePHP. We are learning how MVC works, and by extension, how these frameworks have built upon (or deviated from) the way in which we would expect an MVC framework to be built.
 

MVC Model & PHP MVC Project Structure

 
 
Section 1
 
Config.php is used to connect the mysql database to create a connection parameter for mysql host,user,password and database name.
  1. <?php  
  2. class config    
  3. {     
  4.     function __construct() {  
  5.         $this->host = "localhost";  
  6.         $this->user  = "root";  
  7.         $this->pass = "welcome";  
  8.         $this->db = "mydb13";  
  9.     }  
  10. }  
  11. ?>  
A small part of the code in index.php is used to setup a controller object and call mvcHandler() to view the default page list.php.
  1. <?php  
  2.     session_unset();  
  3.     require_once  'controller/sportsController.php';          
  4.     $controller = new sportsController();     
  5.     $controller->mvcHandler();  
  6. ?>  
In the model folder, create one class for table structure, its named sports and has field and message field to hold messages and data. 
  1. <?php  
  2. class sports  
  3. {  
  4.     // table fields  
  5.     public $id;  
  6.     public $category;  
  7.     public $name;  
  8.     // message string  
  9.     public $id_msg;  
  10.     public $category_msg;  
  11.     public $name_msg;  
  12.     // constructor set default value  
  13.     function __construct()  
  14.     {  
  15.         $id=0;$category=$name="";  
  16.         $id_msg=$category_msg=$name_msg="";  
  17.     }  
  18. }  
  19. ?>  
Section 2
 
The second section is a sportsModel class structure. We are going to explain and show insertRecord(), updateRecord(),selectRecord() and insertRecord(). The sportsModel class is used to access the function sportsController. The sportsModel class constructor receives a mysql connection parameter to work with the database.
  1. <?php  
  2.       
  3. class sportsModel  
  4. {  
  5.     // set database config for mysql  
  6.     function __construct($consetup)  
  7.     {  
  8.         $this->host = $consetup->host;  
  9.         $this->user = $consetup->user;  
  10.         $this->pass =  $consetup->pass;  
  11.         $this->db = $consetup->db;                                  
  12.     }  
  13.     // open mysql data base  
  14.     public function open_db()  
  15.     {  
  16.         $this->condb=new mysqli($this->host,$this->user,$this->pass,$this->db);  
  17.         if ($this->condb->connect_error)   
  18.         {  
  19.             die("Erron in connection: " . $this->condb->connect_error);  
  20.         }  
  21.     }  
  22.     // close database  
  23.     public function close_db()  
  24.     {  
  25.         $this->condb->close();  
  26.     }  
  27.     // insert record  
  28.     public function insertRecord($obj){ }  
  29.         //update record  
  30.     public function updateRecord($obj){ }  
  31.          // delete record  
  32.     public function deleteRecord($id){ }     
  33.          // select record       
  34.     public function selectRecord($id){ }  
  35. }  
  36.   
  37. ?>  
Section 3
 
Section 3 is the controller code part. The sportsController has mvcHandler() and the CRUD functions insert(), update(),delete() and list(). mvcHandler() receives request and execute. This request shows views according to call request by user.
  1. // insert record  
  2.         public function insertRecord($obj)  
  3.         {  
  4.             try  
  5.             {     
  6.                 $this->open_db();  
  7.                 $query=$this->condb->prepare("INSERT INTO sports (category,name) VALUES (?, ?)");  
  8.                 $query->bind_param("ss",$obj->category,$obj->name);  
  9.                 $query->execute();  
  10.                 $res$query->get_result();  
  11.                 $last_id=$this->condb->insert_id;  
  12.                 $query->close();  
  13.                 $this->close_db();  
  14.                 return $last_id;  
  15.             }  
  16.             catch (Exception $e)   
  17.             {  
  18.                 $this->close_db();     
  19.                 throw $e;  
  20.             }  
  21.         }  
  22.         //update record  
  23.         public function updateRecord($obj)  
  24.         {  
  25.             try  
  26.             {     
  27.                 $this->open_db();  
  28.                 $query=$this->condb->prepare("UPDATE sports SET category=?,name=? WHERE id=?");  
  29.                 $query->bind_param("ssi"$obj->category,$obj->name,$obj->id);  
  30.                 $query->execute();  
  31.                 $res=$query->get_result();                         
  32.                 $query->close();  
  33.                 $this->close_db();  
  34.                 return true;  
  35.             }  
  36.             catch (Exception $e)   
  37.             {  
  38.                 $this->close_db();  
  39.                 throw $e;  
  40.             }  
  41.         }  
  42.          // delete record  
  43.         public function deleteRecord($id)  
  44.         {     
  45.             try{  
  46.                 $this->open_db();  
  47.                 $query=$this->condb->prepare("DELETE FROM sports WHERE id=?");  
  48.                 $query->bind_param("i",$id);  
  49.                 $query->execute();  
  50.                 $res=$query->get_result();  
  51.                 $query->close();  
  52.                 $this->close_db();  
  53.                 return true;      
  54.             }  
  55.             catch (Exception $e)   
  56.             {  
  57.                 $this->closeDb();  
  58.                 throw $e;  
  59.             }         
  60.         }     
  61.         // select record       
  62.         public function selectRecord($id)  
  63.         {  
  64.             try  
  65.             {  
  66.                 $this->open_db();  
  67.                 if($id>0)  
  68.                 {     
  69.                     $query=$this->condb->prepare("SELECT * FROM sports WHERE id=?");  
  70.                     $query->bind_param("i",$id);  
  71.                 }  
  72.                 else  
  73.                 {$query=$this->condb->prepare("SELECT * FROM sports");    }         
  74.                   
  75.                 $query->execute();  
  76.                 $res=$query->get_result();     
  77.                 $query->close();               
  78.                 $this->close_db();                  
  79.                 return $res;  
  80.             }  
  81.             catch(Exception $e)  
  82.             {  
  83.                 $this->close_db();  
  84.                 throw $e;     
  85.             }  
  86.               
  87.         }  
Section Four
 
Section four is the view part, when mvcHandler() receives a request and executes the request, it shows views for user. We have created three views in the view folder, which is insert, update and list, which all have HTML design. These views work with controller, and the controller works with model to get or set records in a database table.
  1. <div class="wrapper">    
  2.     <div class="container-fluid">    
  3.         <div class="row">    
  4.             <div class="col-md-12">    
  5.                 <div class="page-header clearfix">    
  6.                     <a href="index.php" class="btn btn-success pull-left">Home</a>    
  7.                     <h2 class="pull-left">Sports Details</h2>    
  8.                     <a href="view/insert.php" class="btn btn-success pull-right">Add New Sports</a>    
  9.                 </div>    
  10.                 <?php    
  11.                     if($result->num_rows > 0){    
  12.                         echo "<table class='table table-bordered table-striped'>";    
  13.                             echo "<thead>";    
  14.                                 echo "<tr>";    
  15.                                     echo "<th>#</th>";                                            
  16.                                     echo "<th>Sports Category</th>";    
  17.                                     echo "<th>Sports Name</th>";    
  18.                                     echo "<th>Action</th>";    
  19.                                 echo "</tr>";    
  20.                             echo "</thead>";    
  21.                             echo "<tbody>";    
  22.                             while($row = mysqli_fetch_array($result)){    
  23.                                 echo "<tr>";    
  24.                                     echo "<td>" . $row['id'] . "</td>";                                            
  25.                                     echo "<td>" . $row['category'] . "</td>";    
  26.                                     echo "<td>" . $row['name'] . "</td>";    
  27.                                     echo "<td>";    
  28.                                     echo "<a href='index.php?act=update&id=". $row['id'] ."' title='Update Record' data-toggle='tooltip'><i class='fa fa-edit'></i></a>";    
  29.                                     echo "<a href='index.php?act=delete&id=". $row['id'] ."' title='Delete Record' data-toggle='tooltip'><i class='fa fa-trash'></i></a>";    
  30.                                     echo "</td>";    
  31.                                 echo "</tr>";    
  32.                             }    
  33.                             echo "</tbody>";                                
  34.                         echo "</table>";    
  35.                         // Free result set    
  36.                         mysqli_free_result($result);    
  37.                     } else{    
  38.                         echo "<p class='lead'><em>No records were found.</em></p>";    
  39.                     }    
  40.                 ?>    
  41.             </div>    
  42.         </div>            
  43.     </div>    
  44. </div>    

Conclusion

 
This article showed and explained to beginners how to make an MVC framework pattern in PHP. You might be wondering why we would even need to create our own framework when there are already so many good choices out there. The reason for this is so we can gain an understanding of the underlying principles of MVC.