Database Connection With PDO

PDO (PHP Data Objects) is an extension that enables connection to any SQL database.

Let’s see how we can use this extension.

At the root our project, we’ll create a new file and call it install.php. Inside the file, we’ll create a new PDO() and store it under the $connection variable.

$connection = new PDO(params);

The parameters required for this process are:

• DSN (data source name) that defines database type, host name, database name (optional)
• The DSN (data source name), which defines the database type, host name, and database name (optional)
• Username
• Password
• Additional options, such as encoding and error

Let’s see this in practice:

<?php
// install.php
$connection = new PDO("mysql:host=localhost", "user", "user",
  array(
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  ););

We can organize our code by putting all the database information into variables:

<?php
// install.php
$connection = new PDO("mysql:host=$host", $username, $password, $options);

Let’s put these variables into a new file. We will call this file config.php:

<?php

$host       = "localhost";
$username   = "user";
$password   = "user";
$dbname     = "tracker";
$dsn        = "mysql:host=$host;dbname=$dbname"; 
$options    = array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
              );

Be aware:
We must ensure that the information we use in the config.php file is the same as that defined in database.sql.
In case there are connection issues, we can change the localhost to 127.0.0.1.

Now, our install.php file will look like this:

<?php
// install.php
require "config.php";

$connection = new PDO("mysql:host=$host", $username, $password, $options);

Let’s use the content of our SQL code. We’ll place the content of each .sql file into a variable, using the file_get_contents() function, and execute each file with the exec() function:

// order of files matters

    $sql_db = file_get_contents("data/database.sql");
    $sql_structure = file_get_contents("data/structure.sql");
    $sql_content = file_get_contents("data/content.sql");

    $connection->exec($sql_db);
    $connection->exec($sql_structure);
    $connection->exec($sql_content);

We’ll also use the try/catch block to handle errors and exceptions:

try {
// code to execute
} catch() {
// exception
}

After putting everything together, our install.php file should look like this:

<?php
require "config.php";

try {
    $connection = new PDO("mysql:host=$host", $username, $password, $options);

    $sql_db = file_get_contents("data/database.sql");
    $sql_structure = file_get_contents("data/structure.sql");
    $sql_content = file_get_contents("data/content.sql");

    $connection->exec($sql_db);
    $connection->exec($sql_structure);
    $connection->exec($sql_content);

    echo "<p>Database created and populated successfully. <br><a href='./'>Home</a></p>";
} catch (PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
}

Run the installer

Congratulations! We’ve successfully created an installer. Now, it’s time for us to test it.

Click the “Run” button below and use the provided link to call the installation script.

Note: If everything works fine, we’ll see a message that will tell us that the database was created and populated successfully.