PHP Connections and Transactions: Connection Class

Why a class for opening a connection?

Having an explicit connection line in the source code and passing it as a parameter is not something positive, because it has specific information from the database, for example in a mysql connection we pass content such as host, user, password being information that should not be there in the application source code. Another problem is when changing the database we would have to go through many files to change the connection settings.

How will it be done?

We will create a class to open connections, so whenever we want to open a new connection we ask this class and we will leave the connection data outside the class in an ini configuration file, so the configuration data will not be in a source code file, improving if we want to change banks.

Example

Step 1 - Directory System:

📦Connection
 ┣ 📂api
 ┃ ┗ 📜Connection.php
 ┣ 📂config
 ┃ ┗ 📜config.ini
 ┣ 📂database
 ┃ ┗ 📜product.db
 ┗ 📜index.php

Step 2 - ini configuration file:

host = 
name = database/product.db
user = 
pass = 
type = sqlite

• host : database hosting location
• name : database name
• user : database access user
• pass : database password
• type : database used
• port : database port

Step 3 - Connection Class:

<?php

namespace api;

use Exception;
use PDO;

class Connection
{
    private function __construct()
    {
    }
}

• open method

    /**
     * the open function receives the name of the configuration 
     * file and returns a PDO connection
     * */
    public static function open(string $name): PDO
    {
    }

• check file

// Checks if the received file exists
if (file_exists("config/{$name}.ini")) {
    // Reads the file and returns a vector with the file data
    $db = parse_ini_file("config/{$name}.ini");
} else {
    // Throw an exception if the file is not found
    throw new Exception("Arquivo {$name} não encontrado");
}

• Read the file information

// Use isset() to check if information has been set
$host = isset($db['host']) ? $db['host'] : null;
$name = isset($db['name']) ? $db['name'] : null;
$user = isset($db['user']) ? $db['user'] : null;
$pass = isset($db['pass']) ? $db['pass'] : null;
$type = isset($db['type']) ? $db['type'] : null;

• Check database type

// Check database type
switch ($type) {
    // if the database is PostgreSQL
    case 'pgsql':
        break;

    // if the database is MySQL
    case 'mysql':
        break;

   // if the database is SQLite
   case 'sqlite':
       break;
}

• PostgreSQL connection

// set the port
$port = isset($db['port']) ? $db['port'] : '5432';
// open connection
$conn = new PDO("pgsql:dbname={$name}; user={$user};
password={$pass}; host={$host}; port={$port}");

• MySQL connection

// set the port
$port = isset($db['port']) ? $db['port'] : '3306';
// open connection
$conn = new PDO("mysql:host={$host}; dbname={$name}; 
port={$port}", "{$user}", "{$pass}");

• SQLite connection

// open connection
$conn = new PDO("sqlite:{$name}");

• return connection

// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// return PDO connection
return $conn;

Testing

<?php

require_once 'api/Connection.php';

use api\Connection;

try {
    $conn = Connection::open('config');
    var_dump($conn);
} catch (Exception $e) {
    print $e->getMessage();
}

object(PDO)[1]

result displayed by var_dump.