What is the best way to use the database connection file in PHP?

Question

I have a connection.php file into my root folder and at the bottom, I added BASE_URL

<?php
$hostname = "localhost";
$username='root';
$password='';
$db='demo';


$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$hostname;dbname=$db;charset=$charset";
try {
        $pdo = new PDO($dsn, $username, $password, $options);
    }
    catch (PDOException $e) {
        throw new PDOException($e->getMessage(), (int)$e->getCode());
  }
  
define('BASE_URL', 'http://example.com/');
?>

Now, On each file, I am adding the connection.php file

<?PHP 
require($_SERVER["DOCUMENT_ROOT"].'/connection.php');
?>// HTML code start

Is this the correct way to use the database connection file? What should I use include or require?

Answer 1

Good question. Although the connection code itself is good, some improvements can be made.

A separate config file

When your code goes live, most likely database credentials will be different from those at home. Therefore, having them hardcoded in the script will make it extremely inconvenient. Instead, create a separate file for your configuration as follows:

• add the config.php line in .gitignore (in case you are not using git yet, you definitely should)

• create a file called config.sample.php with all variables set to empty values like this:

    return [
        'db' => [
            'host' => '127.0.0.1',
            'username' => '',
            'password' => '',
            'dbname' => '',
            'port' => 3306,
            'charset' => 'utf8mb4',
        ],
        'base_url' => 'http://example.com/',
    ];
  

• add it to the version control

• then in your application bootstrap file have a code like this:

    <?php
    if (!file_exists('config.php'))
    {
        throw new \Exception('Create config.php based on config.sample.php');
    }
    $config = require __DIR__.'/config.php';
  
    define('BASE_URL', $config['base_url']);
  
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    $dbconf = $config['db'];
    $dsn = "mysql:host=$dbconf[host];dbname=$dbconf[dbname];charset=$dbconf[charset]";
    try {
            $pdo = new PDO($dsn, $username, $password, $options);
    } catch (PDOException $e) {
        throw new PDOException($e->getMessage(), (int)$e->getCode());
    }
    unset($dbconf, $config['db']);
  

• then, as suggested, create config.php on the every server that your application runs, each with its own set of values.

Actually using the connection

Just use this $pdo variable. When PDO is needed in a function - then pass it as a function parameter. If some function do not need PDO but it calls some other function that needs PDO - then pass $pdo into both. Yes it seems a bit a nuisance but this way you will always know what's going on and where. Avoid using dirty tricks like declaring variable global, singletons and such.

Some minor issues

• closing PHP tag is not necessary and forbidden by the standard
• // HTML code start reveals completely wrong approach to structure the code. Never an HTML code should start from the very beginning. Your PHP code should perform all the data manipulations first, and only then start the output
• require should be always used for the files that are vital for the application. As this file surely is, then it must be require'd. Note that you should avoid using require_once and the likes - nowadays it's not more than a dirty hack to cover an inconsistent code