PDO Connecting to MySQL

Summary: this tutorial shows you step by step how to connect to MySQL database using PHP PDO.

Prerequisites

Before creating a connection to a MySQL database server, you must have:

  • A MySQL database server installed in your local system or in a remote server.
  • A sample database in the MySQL database server.
  • A MySQL account with username and password that can access the sample database.

Suppose we have a local MySQL database server that contains the pdodemo sample database and an account with root username and blank password, we can put these parameters into a database configuration file named dbconfig.php as shown below:

We will include the dbconfig.php configuration file by using the require_once() function in other script files that need to access this configuration information. Check it out how to include a file in other files in the PHP include file tutorial.

To check whether the PDO MySQL driver is enabled, you need to open the  php.ini file and uncomment the following line by removing the semicolon ( ;) at the front of the entry:

MySQL data source name

PDO uses a data source name (DSN), which contains the database server name, database name, and other parameters, that helps create a connection to a database server. Different database system requires different data source name. To connect to MySQL, you use the following data source name:

The MySQL data source name contains the host name and database name which you want to connect to. We can define the DSN for our sample database as follows:

Connecting to MySQL

The following index.php script illustrates how to connect to the pdodemo database in MySQL database server with the root account:

How it works.

  • To connect to MySQL database server, you need to create a new connection object with the data source name, user name and password. The connection object is an instance of the PDO class.
  • If something went wrong while establishing a connection to the MySQL server, an error message will display. The try catch block is used to catch any exceptions that occurs during creating the database connection.

If you have everything setup correctly, you will see the following message:

There are some common issues when you connect to the MySQL database as indicated below:

  • If the MySQL driver is not enabled in the php.ini file, you will get the error message:
  • If you provide incorrect database account, you get the following error message:

SQLSTATE[HY000] [1045] Access denied for user ‘root’@’localhost’ (using password: YES)

  • If you provide an invalid database name or the database name does not exist in the MySQL database server, you get the following error message:
  • If you provide an invalid database host name, the following error message will display:
In this tutorial, you have learned how to connect to the MySQL database server using PHP PDO.