Set Up My Database Connection with PHP PDO

Setting up the PDO database connection with PHP is essential in my opinion if you want a dynamic database driven website . It looks like a lot but its really not, I just broke it down into small steps.

Step 1

The first thing I’m going to be using is My Basic File Structure which if you want you can  download  to get you started quickly.

Step 2

Set Up Database User And Database In Cpanel

Step 3

You will need a defines file where you can define your database connection info . You will need your host: which is usually localhost , database name: the name that you gave your database, database user name : the username of the user who controls the database  and  the database password : the password you set for your database user. Create this file in your includes folder and name it defines.php

<?php 
//FILE NAME defines.php 
//DATABASE 
define("HOST", "localhost"); 
define("DATABASE_NAME", "put_your_database_name_here"); 
define("DATABASE_USER", "put_your_database_user_name_here"); 
define("DATABASE_PASSWORD", "put_your_database_password_here"); 
?>

Step 4

Initialization – Inside your includes folder create a file called init.php. This file will be required on each page you create. It will load your defines.php file that will be needed by your database class file which we will be making in step 5.  So inside your init.php file

<?php 
//THIS WILL SET YOUR TIMEZONE 
date_default_timezone_set("America/Chicago"); 

//THIS REQUIRE ONCE YOUR DEFINES FILE 
require_once "includes/defines.php"; 

//THIS WILL REQUIRE ONCE ALL CLASSES IN CLASSES FOLDER 
spl_autoload_register(function($class){ require_once "classes/{$class}.php"; });

 //THIS WILL CREATE AN INSTANCE OF YOUR DATABASE OBJECT THAT CAN 
//BE USED IN YOUR CLASSES IF YOU NEED A DATABASE CONNECTION 
//IN THAT CLASS 
$instance = Database::getInstance();
$db = $instance->con;

//THIS CREATES AND OBJECT OF EACH CLASS AND EACH OBJECT IS GIVEN 
//THE NAME OF THE CLASS AND IS LOADED WITH THE DATABASE INSTANCE 
//SO YOU CAN MAKE QUERIES
foreach (glob("classes/*") as $filename) {
    if($filename != "classes/Database.php"){
         $class_name = basename($filename,".php");
         $obj_name = strtolower($class_name);
         ${$obj_name} = new $class_name($db);
    }

//If you don't want to send your database 
//connection to every class file just disregard the code right above
//and use this for the ones classes you want the database connection and leave
//out the $db when creating a new object.
$stuff = new Stuff($db);  

Step 5

Database Class File – Create a file called Database.php inside your classes folder.  This file will take your defines database info and create a PDO connection to connect to the database.

<?php 
//THE DEFINES FILE WITH YOUR DATABASE CONNECTION INFO 
// IS LOADED IN THE INIT.PHP FILE 

class Database{ 
private static $_instance = null; 
private $host = HOST; 
private $database_name = DATABASE_NAME; 
private $database_user = DATABASE_USER; 
private $database_password = DATABASE_PASSWORD; 
public $con; 

public function __construct() {
 try{ 
$this->con =  new PDO('mysql:host='.$this->host.';dbname='.$this->database_name.';', $this->database_user, $this->database_password);
            
      }catch (PDOException $e) {

//THIS STATIC METHOD WILL TAKE ANY EXCEPTIONS AND ADD THEM TO AN ERROR FILE
//IN CASE THERE IS AN ERROR CONNECTING TO THE DATABASE
           Errors::catch_errors($e);
         die("SORRY CAN NOT CONNECT TO THE DATABASE");
   
}
    }
    
     public static function getInstance(){
        if(!isset(self::$_instance)){
            self::$_instance = new Database();
        }
        return self::$_instance;
    }
    
    


    
}

    


?>

Step 6

Error Class File –  We are going to create a Error.php class file inside your classes folder. We are making this file to log any exceptions that occur when you are trying to connect to the database.  It will create a folder named logs if there is not one and put a file called mainLog.txt into it. It will show the date , error message , the file the error occurred in  and line number of the error.

<?php 
class Errors{ 
public static function catch_errors($e){ 
$error = "\n".date("m-d-Y H:i:s")."\n"; 
$error .= "Error Message = ".$e->getMessage()."\n";
$error .= "Error in File = ".$e->getFile()."\n";
$error .= "in line numer".$e->getLine()."\n";
        
        $file = "logs/mainLog.txt";
        if(is_dir("logs")){
            file_put_contents($file, $error,FILE_APPEND);
        }else{
            mkdir("logs");
             file_put_contents($file, $error,FILE_APPEND);
        }
       
    }
    
    
    
    
    
    
}

Step 7

Stuff Class File – This class file is just an example of how to add the database connection to your other class files. Create a class file called Stuff.php and put it in your classes folder.

<?php

class Stuff{

private $db;

public function __construct($db){

$this->db = $db;

}

public function get_stuff(){


$sql = "SELECT * FROM your_table_name";
$stmt= $this->db->query($sql);

$stuff = $stmt->fetchAll(PDO::FETCH_OBJ);

if(count($stuff)){

return $stuff;
}else{

return false;
}



}

}

<?php

//

require_once "includes/init.php";

$my_stuff = $stuff->get_stuff();
foreach($my_stuff as $each){
echo $each->id;
}

?>