PHP Database connection

Accessing a database in PHP

There’re two ways to access a database in PHP:

• Using a database-specific extension.
• Using PHP Data Objects (PDO) extension.

If you use a database-specific extension (e.g., my MySQLi), your code is tied to the database you’re using. For example, if you change the database from MySQL to PostgreSQL, it will require rewriting significant parts of the code.

The database-independent Data Objects (PDO) extension provides a unform interface to work with a number of database drivers that implement the interface. Moving between database systems can be as simple as changing one line of your code or your php.ini file. Since PDO is an interface, you can’t perform any database functions using PDO alone, you will need a database-specific driver that implements PDO. While PDO is a portable option since it’s database-independent, it’s often slower than a database-specific extension. However, PDO has many benefits over a database-specific extension and has drivers for almost all databases in existence.

In this lecture note, you will learn how to use PDO to fetch data from the database, insert new records, delete records, and handle errors. We will use PostgreSQL as an example but again this code should work on Oracle database, MySQL, MS SQL Server, and any database that has a PDO database driver.

Using PDO with PostgreSQL

PostgreSQL is one of the most popular open-source relational database systems. With users ranging from big tech companies such as Microsoft and Sony to social network platforms such as Reddit and Instagram, PostgreSQL has proven to be a highly reliable and robust database system that can handle a large number of intensive data workloads. With more than 30 years of development work, PostgreSQL is the primary open-source database choice when migrating from commercial databases such as Oracle.

You will need to enable PHP PDO PostgreSQL driver in the configuration file (php.ini file). In the php.ini file, you can find the line that contains extension=php_pdo_pgsql and uncomment it.

Any line that starts with a semicolon ; is considered a comment. To enable the driver, you need to uncomment the line by removing the semicolon (;) or add the driver to your php.ini file. The file should be located under the PHP root installation directory. For example, if you have installed PHP at C:\php, then the file should be at C:\php\php.ini. If you are not sure where the php.ini is, write a PHP page with the following statement: <?php echo phpinfo() ?>. When you build it and serve it, you should get the path to the configuration file (php.ini) path as shown below:

; Enable php to work with postgreSQL by removing the semicolon.
; On Windows:
;extension=php_pdo.dll
;extension=php_pgsql.dll
; On macOS:
;extension=pdo_pgsql.so
; On Linux:
extension=php_pgsql.so

Making a connection to the database

$dbConfig =array(
    $host => "localhost",
    $port => 5432,
    $username => "dbusername",
    $password => "xxxxxxxxxxx",
    $database => "tododb",
);
// data source name
$dsn= 'pgsql:host=$host;port=$port;dbname=$database;';

try{
    $pdo = new PDO($dsn, $username, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    // throw exception upon errors
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
	die('Could not connect: ' . $e->getMessage());
}