PHP MySQL: Connecting to MySQL Database

Summary: in this tutorial, you will learn how to connect to a MySQL server using a PDO object.

Before connecting to a MySQL database, you need to have the following database information:

• MySQL data source name or DSN :  Specify the address of the MySQL server. You can use an IP address or server name such as 127.0.0.1  or localhost.
• Database name: Indicate the name of the database to which you want to connect.
• Username and password: Specify the username and password of the MySQL user that you use to connect to the MySQL server.

Note that the user account must have sufficient privileges to access the database.

We will use the following MySQL server information:

• The local MySQL database server, so the DSN is localhost .
• The todo databases.
• The root account with a password.

Creating a sample database

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server using the mysql client tool:

mysql -u root -p

Second, create a new database called todo:

CREATE DATABASE todo;Code language: SQL (Structured Query Language) (sql)

Third, exit the mysql client tool:

exitCode language: SQL (Structured Query Language) (sql)

Connecting to a MySQL server

First, create a new PHP file config.php to store the connection parameters:

<?php

$host = 'localhost';
$dbname = 'todo';
$username = '<user>';
$password = '<password>';Code language: PHP (php)

You need to replace the username/password with the actual one.

Second, create a new PHP file named connect.php :

<?php
require_once 'config.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());
}Code language: PHP (php)

How the script works.

First, include the config.php file in the script using the require_once function:

require_once 'config.php';Code language: PHP (php)

Second, establish a connection to the MySQL server by creating a new PDO object with the database connections from the config.php file:

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);Code language: PHP (php)

Display a success message if the connection to the MySQL server is established successfully:

 echo "Connected to $dbname at $host successfully.";Code language: PHP (php)

Otherwise, show the error message from the PDOException object:

die("Could not connect to the database $dbname :" . $pe->getMessage());Code language: PHP (php)

The try...catch statement catches and handles any exceptions that may occur.

Third, open the connect.php file on the web browser such as:

http://localhost/phppdo/connect.phpCode language: JavaScript (javascript)

The following output message indicates that you have successfully connected to the MySQL server from PHP:

Connected to todo at localhost successfully.

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

$conn = null;Code language: PHP (php)

Summary

• Create a new instance of the PDO object to establish a connection to a MySQL server from PHP.