Question about using a database connection class

[kenoli]

I created a database class to connect to a database.  The code is below.  I'm not sure how to call this connection in other classes.  Do I use:

$db->pdo = $conn->prepare($sql);

or what?  Note that the db object is instantiated at the end of the class file.  Here is the class:

 

class DB {
	
	public $pdo = '';
	//public $message = 'A message from db'; // Debug

function __construct() {
	
	// Database info located elsewhere
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "dbname";
	
	try {
		
	  $this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	  // set the PDO error mode to exception
	  $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	}  // End Try
	
	catch(PDOException $e) {
		  echo "Error: " . $e->getMessage();
		}
		
		//echo '<h3>Everything wnet OK.</h3>';  // Debug

	} // End __construct
	
} // End class definition DB.php

$db = new DB;

Thanks,

 

--Kenoli

[benanamen]

Take a look at my clean-pdo code repo.

https://github.com/benanamen/clean-pdo

[kenoli]

benanamen -- You didn't do anything much different than I; you just organized your classes differently.  What I am seeing is that you are passing the connection from Connection.php to DB.php as an attribute to the __construct method.  The makes sense, though I still don't understand how the connection is transferred to the pdo attribute.  Perhaps it is the following syntax which I am not familiar with in the connection.php file::

connect(array $config): PDO { ...rest of code}

I'm not familiar with the " : PDO" part of that syntax.  Can you explain that for me?  I appreciate the assistance.  It is clear that you are much more experienced with php than I.  Thanks.

--Kenoli

[kenoli]

benanamen -- 

I just looked this up and discovered a new feature of php Return Type Declarations.  It looks like this is what you are using and it is the perfect answer to my question. I think I can easily apply that to my code and implement some of the code in your approach to my advantage.  I will report back how that goes.

Thanks,

--Kenoli

[Barand]

19 minutes ago, kenoli said:

$this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

$this->pdo now contains your pdo connection. so

$db = new DB;
$stmt = $db->pdo->prepare(...)

You should extra code to your class to set the connection's charset (UTF-8) and to set PDO::ATTR_EMULATE_PREPARES to false.

It's also useful to set the PDO::ATTR_DEFAULT_FETCH_MODE attribute also.

[Strider64]

This is how I do it:

My Database Class:

<?php


namespace Miniature;

use PDO;
class Database {

    private $_connection;
    // Store the single instance.
    private static $_instance;

    // Get an instance of the Database.
    // @return Database:
    protected static function getInstance(): Database
    {
        if (!self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    public static function pdo(): PDO
    {
        $db = static::getInstance();
        return $db->getConnection();
    }

    // Constructor - Build the PDO Connection:
    public function __construct() {
        $db_options = array(
            /* important! use actual prepared statements (default: emulate prepared statements) */
            PDO::ATTR_EMULATE_PREPARES => false
            /* throw exceptions on errors (default: stay silent) */
        , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            /* fetch associative arrays (default: mixed arrays)    */
        , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        );
        $this->_connection = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
    }

    // Empty clone magic method to prevent duplication:
    private function __clone() {

    }

    // Get the PDO connection:
    public function getConnection(): PDO
    {
        return $this->_connection;
    }

}

Then I simple do this is my other classes (a small snippet) ->

    public function update(): void
    {
        /* Initialize an array */
        $attribute_pairs = [];

        /* Create the prepared statement string */
        foreach (static::$params as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = "{$key}=:{$key}"; // Assign it to an array:
        }

        /*
         * The query/sql implodes the prepared statement array in the proper format
         * and I also hard code the date_updated column as I practically use that for
         * all my database table. Though I think you could override that in the child
         * class if you needed too.
         */
        $sql  = 'UPDATE ' . static::$table . ' SET ';
        $sql .= implode(", ", $attribute_pairs) . ', date_updated=NOW() WHERE id =:id';

        /* Normally in two lines, but you can daisy chain pdo method calls */
        Database::pdo()->prepare($sql)->execute(static::$params);

    }

Though looking at the Database class the code possibly could be tighten up just a tad, but I have been refining it over the years.

 

[kenoli]

Thanks for the great responses.  They are hugely helpful.

 

--Kenoli

[kenoli]

Strider64 -- A couple of questions:

1.  I presume "use PDO" is the implementation of the recommendations use a 'use' statement for core classes.  Is there any other meaning involved?  This doesn't imply the explicit use of namespaces in the project?

2.  Regarding 'getConnection(): PDO', I presume this creates security that the output will be a PDO type.  I suppose there is no need to use 'declare(strict_types=1)' here because php can't change a return to a PDO type if it is not already a PDO type.

Is my understanding of these elements correct?

I like your returning the connection in the construct method.

This is explicitly a singleton class.  I'm going to have to spend some time to fully understand singletons.  I presume this assures that a number of independent connection are lying around in the case that a script don'ts close a connection it opened.

Thanks,

--Kenoli