MySQL Database connection in PHP using PDO – PHP PDO

Database connection in PHP using PDO – PHP PDO

Database connection in PHP using PDO

Database Connection in PHP

Before starting to database connection I would like to explain why we need 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 in two basic categories – Static Web development and Dynamic web development.

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

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

What is database?

Database is a structured set of data held in a computer system.

To establish a database connection, 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 database in PHP.

PDO can support any database unlike MySQLi which provide 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 following code.

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

How to connect to database with PDO in PHP?

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

MySQL Connection by PDO

Microsoft Sql Server Connection by PDO

Below i am giving complete example to coonect MySQL Database using PDO.

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 variable and can be changed any time without changing in connection string. Above code is used to connect “test_db” database on localhost from user root and password blank (which is 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 select statement of SQL. If you are not familiar with SQL then get introduced to some basic statements and queries of SQL first.

What is in the code?

Firstly i have established connection as in the previous example.

Prepared a select statement for execution to get result.

In this line the prepared select statement is executed.

When this line is executed all the result rows are assigned to $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 understandable. Below is the result of this code.

pdo select statement

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

pdo select statement table

Leave a Reply