php

How to Connect to Database (MongoDB / MySQL) with OOP PHP For Beginners

By Rasyue | On June 20, 2020

Welcome folks! In this tutorial we are going to write PHP code to connect to the database OOP style! In case if you are not familiar with OOP or Object-Oriented Programming, you can check out my previous post here.

We will write code to connect to two databases which are MongoDB and MySQL. I will be using XAMPP in this tutorial or if you want to use a standalone Apache or Nginx, feel free to do so.

Introduction

A database connection is a big part in any web development. Often time, many new developers will find themselves putting their database credentials and making database connection in every part of their project. Not to mention the mistake of writing similar or the exact same queries over and over again.
This tutorial aims at beginners on how we can use OOP in PHP to setup a database connection and writing methods for queries so you Do Not Repeat yourself.

MySQL Database Connection

First of all, start up your XAMPP or WAMPP or Apache or Nginx so we can start to write PHP code. Create a folder and name it whatever you like but I am going to name it Rasyue. This will be our project folder. Now, we are going to install a PHP library to allow us to use credentials from our .env file. Run the below command in your command line. Make sure you already have composer install.

composer require vlucas/phpdotenv
composer require mongodb/mongodb

Then. create a .env file in our root.
Next, create a folder and name it database. Create two files and name it db_mysql.php and db_mongodb.php. Just to be sure, refer the image below so you know you have everything set up correctly.

db-directory

Open the db_mysql.php and let’s write some code.

<?php

require __DIR__. '\\..\\'."/vendor/autoload.php";

$dotenv = \Dotenv\Dotenv::createImmutable(__DIR__.'\\..\\'.'/');
$dotenv->load();


class MySqlDb{

    protected $host;
    protected $username;
    protected $password;
    protected $db;
    protected $conn;

    function __construct(){

       $this->host = $_ENV['mysql_host'];
       $this->username = $_ENV['mysql_username'] ;
       $this->password = $_ENV['mysql_password'] ;
       $this->db = $_ENV['mysql_database'] ;      
       $this->connect();
      
    }

    private function connect(){
        $this->conn = new mysqli($this->host, $this->username, $this->password, $this->db);
        if ($this->conn->connect_error) {
            die("Connection failed: " . $this->conn->connect_error);
        }
    }

    function getData($table, $where){
        //$this->connect();
        $sql = "SELECT * FROM ".$table." WHERE ".$where;          
        $sql = $this->conn->query($sql);
        $sql = $sql->fetch_assoc();
        return $sql;

    }

    function updateData($table, $update_value, $where){
        $this->connect();
        $sql = "UPDATE ".$table." SET ".$update_value." WHERE ".$where;        
        $sql = $this->conn->query($sql);
        if($sql == true){
            return true;
        }else{
            return false;
        }
    }

    function createData($table, $columns, $values){
        $this->connect();
        $sql = "INSERT INTO ".$table." ".$columns." VALUES ".$values;        
        $sql = $this->conn->query($sql);
        if($sql == true){
            return $sql;
        }else{
            return false;
        }
    }

    function deleteData($table, $filter){
       
        $this->connect();
        $sql =  "DELETE FROM ".$table." ".$filter;  
        $sql = $this->conn->query($sql);
        if($sql == true){
            return true;
        }else{
            return false;
        }
    }
   
}



?>

The above Class is a Class that will handle our database connection and queries with MySQL. Once you have copied this, open up your .env file and make sure you replace the mysql credentials with yours.

env-credentials

Now, create an index.php file on your root folder and you test out the Class that we have wrote.

MySQL Database

If the above returns error, make sure you have your database service running and create a table, any table will do but make sure you pass in the correct values. For simplicity you can use my table, but you are free to create your own table.

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT

Ok folks! Let’s move to MongoDB.

MongoDB

Ok! Before we can start building our Class, there is something that we must do to be able to use MongoDB with PHP. First, download the driver here.
Make sure you download the correct DLL file for your PHP version. You can check your PHP version by echo-ing phpinfo() on your php file.
Download the file, extract it and copy the php_mongodb.dll into php\ext folder. Open you php.ini and add the code extension=php_mongodb.dll.

Boot up your server again and you should see mongodb in your phpinfo()
Now we are ready!
But hold up, you will need to install the MongoDB locally first.
Here’s how! Once you have installed the Community Edition, you can start your MongoDB from the command line with mongod. If it does not works, you can go to C:/Program Files/MongoDB/Server/4.2/bin and run mongod

Once you have start the database, you can view it from MongoDB Compass.

Open up your php_mongodb.php and paste the below code.

<?php 

require __DIR__. '\\..\\'."/vendor/autoload.php";

class MongoDB{



   function insertData($database, $collection, $insert_data){
        $db = (new MongoDB\Client)->$database->$collection;
        
        $insertOneResult = $db->insertOne($insert_data);

        return $insertOneResult->getInsertedId();

   }

    function updateData($database, $collection, $update_column, $update_value){
        $db = (new MongoDB\Client)->$database->$collection;
        $db->drop();
        $updateResult = $collection->updateOne(
            $update_column,
            $update_value
        );
        return $updateResult->getModifiedCount();
       
    }

    function fetchData($database, $collection, $filter_data){
        $db = (new MongoDB\Client)->$database->$collection;

        $cursor = $db->find($filter_data);
        
        foreach ($cursor as $document) {
            echo $document['_id'], "\n";
        }
    }


    function deleteData($database, $collection, $delete_data){
        $db = (new MongoDB\Client)->$database->$collection;
        $db->drop();
        $deleteResult = $db->deleteOne($delete_data);

        
        return $deleteResult->getDeletedCount();
    }
}


?>

Now that the Class for MongoDb is all set up. Go back to your index.php and let’s try to insert data. Remember, you must always pass in a data array. You may specify any name for the database and collection.

<?php 


include "database/php_mysql.php";
include "database/php_mongodb.php";

$mongo = new MongoDB();

// you may specify as many key-value in the array as you want
// you dont have to specify the ID since mongodb will take care of that
//
$insert_data = array('name'=>'rasyue', 'email'=>'rasyue@rasyue.com');
$insert = $mongo->insertData('demo', 'user', $insert_data);



?>

Once you have run that, you can check in your MongoDB whether the data is properly save or not.

There you have it folks! Simple PHP Classes to handle your database connection and queries. Keep in mind this is not the best way to create the Class for your database but treat it as a platform for your learning. In time, you will figure out how to write a better Class to handle your queries. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*
*