Database Connection in PHP

Before starting to database connection I would like to explain why we need a database connection in PHP. Usually, we do two types of web development i.e. Static web development and dynamic web development.

Static Web Development VS Dynamic Web Development

We can divide web development into two basic categories – Static Web development and Dynamic web development.

Static web development is done by making web pages in plain HTML, which means what is in the code is simply displayed.

Dynamic Web development is one which is written using any of the server-side programming or scripting languages like PHP, Asp, JSP, Python, etc., with some logic working on the server side. The content displayed is formed by the logic applied through those programming or scripting languages along with some database which depends on the user actions and behavior with the website

What is a database?

A database is a structured set of data held in a computer system.

To establish a database connection, a variety of options is available for us. Here we are going to learn it by using PDO. PDO is PHP Data Objects, an extension that defines a lightweight, consistent interface for accessing databases in PHP.

PDO can support any database, unlike MySQLi which provides support only for MySQL. That means if you need to switch from MySQL to any other database at any time, you just need to change the PDO options or say DRIVERS to connect to any other database like Oracle, Ms SQL, PostgreSQL, etc. It is not necessary that database drivers of all the databases are present on your system as it varies from machine to machine, you can check this using the following code.

echo "Available drivers on this system are:<br>";
print_r(PDO::getAllAvailableDrivers());

Let’s start with establishing the connection to the database. I am taking examples in further code for MsqSql Database.

How to connect to the database with PDO in PHP?

To set up a connection by PDO in PHP first create an object of PDO Class. Below I am showing a few database connection examples.

MySQL Connection by PDO

$DB = new PDO("mysql:host=yourhost;dbname=database_name", 'db_username', 'db_password');

#example:
$DB = new PDO("mysql:host=localhost;dbname=test_db", 'root', '');

Microsoft SQL Server Connection by PDO

$DB = new PDO("mssql:host=yourhost;dbname=database_name, db_username, db_password");

#example:
$DB = new PDO("mssql:host=localhost;dbname=test_db, root, ");

Below I am giving a complete example to connect MySQL Database using PDO.

<?php
$my_host = "localhost";
$my_db = 'test_db';
$my_db_username = "root";
$my_db_passwd = "";
try {
	$connected = new PDO("mysql:host=$my_host;dbname=$my_db", $my_db_username, $my_db_passwd);
	
	if($connected){
		echo "Database connection successfull.";
	}

} catch (Exception $ex) {
	echo $ex->getMessage();
}
?>

Explanation:

I have enclosed the connection code in a try-catch block which is recommended as if there is any exception in the connection the catch() will catch and we can get notified about the issue by $ex->getMessage();. All the useful information/ credentials are stored in a variable and can be changed at any time without changing in the connection string. Above code is used to connect the “test_db” database on localhost from user root and password blank (which is a default in MySQL database).

Let’s see one more example:

How to fetch data from MySQL using PDO

To fetch data from any database we use a select statement of SQL. If you are not familiar with SQL then get introduced to some basic statements and queries of SQL first.

<?php
$my_host = "localhost";
$my_db = 'test_db';
$my_db_username = "root";
$my_db_passwd = "";
try {

	$DB = new PDO("mysql:host=$my_host;dbname=$my_db", $my_db_username, $my_db_passwd);
	
	if($DB){
		$statement = $DB->prepare("SELECT * FROM users");
		$statement->execute();
		$result = $statement->fetchAll();

		if($result){
			echo '<table cellpadding="5" border="1" width="400">
					<tr>
						<th>ID</th>
						<th>First Name</th>
						<th>Last Name</th>
					</tr>';
			foreach ($result as $rs) {
				echo '<tr>
						<td>'.$rs['uid'].'</td>
						<td>'.$rs['f_name'].'</td>
						<td>'.$rs['l_name'].'</td>
					  </tr>';
			}
		}else{
			echo "No result Found";
		}

	}else{
		echo "Database connection can not be established.";
	}

} catch (Exception $ex) {
	echo $ex->getMessage();
}
?>

What is in the code?

Firstly I have established a connection as in the previous example.

$statement = $DB->prepare("SELECT * FROM users");

Prepared a select statement for execution to get results.

$statement->execute();

In this line, the prepared select statement is executed.

$result = $statement->fetchAll();

When this line is executed all the result rows are assigned to the $results variable which is in the form of an array.

After that, a foreach loop is used just to display the data in a tabular format which shows it is understandable. Below is the result of this code.

I am including the screenshot of the database table below where the data is actually stored.