Configuring MySQL Database Connection using PHP OOP Approach Tutorial

Introduction

In this tutorial, we will tackle about Configuring MySQL Database using PHP Object-Oriented Programming (OOP) Approach. This topic can be helpful to those new to PHP Language especially the beginners to learn how to create a MySQL Database Connection using PHP OOP. This tutorial can be also helpful for learning to develop web applications using MVC (Model-View-Controller) whereas the Model and controllers are commonly structured or written using OOP.

What is OOP?

OOP stands for Object-Oriented Programming. It is a programming paradigm based on object concepts. It organizes software's design around data or objects.

PHP OOP is written using objects which are the instance of classes. Here's a sample PHP Script for writing an OOP code.

  1. <?php
  2. Class MyClass{
  3. private $variable;
  4.  
  5. function set_var_value($value){
  6. $this->variable = $value;
  7. }
  8.  
  9. function get_var_value(){
  10. return $this->variale;
  11. }
  12. }
  13. ?>

The objects can be accessed or called like the following snippets:

  1. <?php
  2. $my_class = new MyCLass();
  3.  
  4. // Set Variable Value
  5. $my_class->set_var_value("Sample Value");
  6.  
  7. // Getting value
  8. print($my_class->get_var_value());
  9.  
  10. ?>

Now, I will show you a basic technique for configuring your site MySQL Database Connection using OOP Approach. The class we'll be creating will be named as "Database Class".

Features of the program that we will be creating

  • Creating a Database If not existing yet.
  • Database Connection
  • Automatically creates a database table
  • Inserting a sample data
  • Fetching Data from the Database

Getting Started

To run the script on your local machine, kindly download and install virtual server software such as XAMPP and WAMP. Make sure to start the Apache and MySQL servers before proceeding to the coding part of this tutorial. To do that using XAMPP or WAMP, open the software's Control Panel and start the said servers.

Creating the Database Class

Here's the following script for creating the Database Class in PHP. The script contains all the code that is needed for connecting to the database.

  1. <?php
  2.  
  3. Class Database{
  4. private $dbname;
  5. private $tblname;
  6. private $host;
  7. private $username;
  8. private $password;
  9. private $conn;
  10.  
  11. function __construct(){
  12. // Database Name
  13. $this->dbname = 'dummy_db';
  14. // Table Name
  15. $this->tblname = 'members';
  16. // Host Name
  17. $this->host = 'localhost';
  18. // DB Username
  19. $this->username = 'root';
  20. // Database Name
  21. $this->password = '';
  22.  
  23. $this->conn = new mysqli($this->host, $this->username, $this->password);
  24.  
  25. // Creating the sample DB
  26.  
  27. $db_sql = " CREATE DATABASE IF NOT EXISTS {$this->dbname}";
  28. $this->conn->query($db_sql);
  29. if(!$this->conn->error){
  30. $this->conn->close();
  31. // Open Databse Connection
  32. $this->conn = new mysqli($this->host, $this->username, $this->password, $this->dbname);
  33. if($this->conn->affected_rows >0){
  34.  
  35.  
  36.  
  37. // Creating a sample table
  38.  
  39. $tbl_sql = "CREATE TABLE IF NOT EXISTS `{$this->tblname}`
  40. ( `id` int(30) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  41. `fullname` varchar(250) NOT NULL,
  42. `email` varchar(250) NOT NULL,
  43. `contact` varchar(250) NOT NULL,
  44. `address` text NOT NULL)";
  45. $this->conn->query($tbl_sql);
  46. if($this->conn->error){
  47. die("Creating DB Table Failed. Error: ". $this->conn->error);
  48. }else{
  49.  
  50. // Inserting a sample data
  51. $data_sql = "INSERT IGNORE INTO `{$this->tblname}` (`id`, `fullname`, `email`, `contact`, `address`) VALUES
  52. (1, 'Mark Cooper', '[email protected]', '09123456789', 'Sample Address 101'),
  53. (2, 'Jane Doe', '[email protected]', '09123789456', 'Sample Address 102'),
  54. (3, 'Rebert Miller', '[email protected]', '09123564456', 'Sample Address 103')
  55. ";
  56. $this->conn->query($data_sql);
  57. if($this->conn->error){
  58. die("Sample Data Failed to Insert. Error: ". $this->conn->error);
  59. }
  60. }
  61. }
  62. }else{
  63. die("Creating DB Failed. Error: ". $this->conn->error);
  64.  
  65. }
  66.  
  67. }
  68.  
  69. // Fetching Data From Database
  70. public function get_results($query= ""){
  71. if(empty($query)){
  72. // Returning Error if query String is Empty
  73. return json_encode(["error"=>"Query is empty"]);
  74. }else{
  75. $query = $this->conn->query($query);
  76.  
  77. if($this->conn->error){
  78. return json_encode($this->conn);
  79. }else{
  80. return $query;
  81. }
  82. }
  83. }
  84.  
  85. // closing db connection
  86. function __destruct(){
  87. $this->conn->close();
  88. }
  89. }
  90. ?>

The snippets above contain commented lines to explain the purposes of the following lines.

Creating the Interface

The following script is the Main Page or the index page of our sample application. It contains an HTML and PHP script for displaying the elements and data on the page. I am using Bootstrap Framework v5 CDN on the script which means that an internet connection is a must upon browsing the sample application in order to load the page design scripts.

  1. <?php
  2. require_once "Database.php";
  3. $db = new Database();
  4. ?>
  5. <!DOCTYPE html>
  6. <html lang="en">
  7. <meta charset="UTF-8">
  8. <meta http-equiv="X-UA-Compatible" content="IE=edge">
  9. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  10. <title>PHP OOP DB Config</title>
  11. <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-gH2yIJqKdNHPEq0n4Mqa/HGKIhSkIHeL5AyhkYV8i59U5AR6csBvApHHNl/vI1Bx" crossorigin="anonymous">
  12. </head>
  13. <body class="bg-primary bg-opacity-75">
  14. <div class="container-fluid my-5 py-4">
  15. <h2 class="text-center text-light">PHP (OOP) Object Oriented Programming Database Configration</h2>
  16. <hr class="border-light">
  17. <div class="card rounded-0 mx-auto col-lg-8 col-md-10 col-sm-12 mt-3">
  18. <div class="card-header">
  19. <div class="card-title">Member List</div>
  20. </div>
  21. <div class="card-body">
  22. <div class="container-fluid">
  23. <table class="table table-striped table-bordered">
  24. <tr class="bg-primary text-light">
  25. <th class="text-center">ID</th>
  26. <th class="text-center">Name</th>
  27. <th class="text-center">Email</th>
  28. <th class="text-center">Contact</th>
  29. <th class="text-center">Address</th>
  30. </tr>
  31. </thead>
  32. <?php
  33. $data = $db->get_results("SELECT * FROM `members` order by `id` asc");
  34. if($data->num_rows > 0):
  35. while($row = $data->fetch_assoc()):
  36. ?>
  37. <tr>
  38. <th class="text-center"><?= $row['id'] ?></th>
  39. <td><?= $row['fullname'] ?></td>
  40. <td><?= $row['email'] ?></td>
  41. <td><?= $row['contact'] ?></td>
  42. <td><?= $row['address'] ?></td>
  43. </tr>
  44. <?php endwhile; ?>
  45. <?php endif; ?>
  46. </tbody>
  47. </table>
  48. </div>
  49. </div>
  50. </div>
  51. </div>
  52. </body>
  53. </html>

DEMO VIDEO

That's it! You can test the sample application on your end and check if it achieves our goal for this tutorial. To check if it works as it was planned, browse the application index page on your preferred browser. The database and data should be automatically added and the sample data will be shown in the table element of the page.

Result Snapshot

PHP OOP Approach

I also provided the working source code zip file that I created for this tutorial. You can download it by clicking the download button below this article. I hope this Configuring MYSQL Database Connection using PHP OOP Approach Tutorial will help you with what you are looking for and for your future projects.

Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)

Comments

Submitted byShamsul Alam (not verified)on Fri, 12/30/2022 - 22:51

Very Useful, I am also beginner in PHP App. Thanks a lot.

Add new comment