How to connect to MySQL database server using PDO object

In this tutorial, we will show you how to connect to MySQL database server using PDO object.Before connecting to a MySQL database, you have to specify the following information:

  • MySQL data source name or DSN: specifies the address of the MySQL database server. You can use IP address or server name e.g., 127.0.0.1 or localhost
  • MySQL database name: indicates the name of the database that you want to connect to.
  • Username and password: specifies username and password of the MySQL’s user that you want to connect to MySQL database server. The account must have sufficient privileges to access the database specified above.

We will to use:

Local MySQL database server so the DSN is localhost.
The classicmodels as the sample database.
The root account with blank password, just for the sake of demonstration.

Connecting to MySQL

First, to make it convenient, we will create a new PHP file for database configuration named  dbconfig.php that holds all configured parameters:

<?php
    $host = 'localhost';
    $dbname = 'classicmodels';
    $username = 'root';
    $password = '';
?>

Second, we create a new PHP file named  phpmysqlconnect.php:

<?php
require_once 'dbconfig.php';
 
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    echo "Connected to $dbname at $host successfully.";
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}
?>

How the script works.

  • We included the dbconfig.php file into the script by using the require_once function.
  • Inside the try block, we created a new PDO object with three arguments: connection string, username and password. The connection string is composed of $host and $dbname variables in the dbconfig.php file.
  • If the connection to the MySQL database established successfully, we displayed a success message. If there was any error, PHP issued a PDOException that contains detailed error message. We call the getMesage() method of the PDOException object to get the detailed message for displaying.

Third, let’s test the script from the web browser.

php-mysql-connect

It works as expected. We’ve successfully connected to the MySQL server.

Let’s try to change something in the code to make the script display an error message. If you set the $username variable to blank, you will get the following error message:

php-mysql-connect-error

The error message says that:

Access denied for user ''@'localhost' to database 'classicmodels'

Because we don’t have any blank user in the classicmodels database.

When the script ends, PHP automatically closes the connection to the MySQL database server. If you want to close the database connection explicitly, you need to set the PDO object to null as follows:

$conn = null;
Please follow and like us:



2 Replies to “How to connect to MySQL database server using PDO object”

Leave a Comment

Your email address will not be published. Required fields are marked *