PHP: Connecting to a MySQL database.

This is a tutorial on how to connect to a MySQL database using PHP.

Here, we hope to provide a simple explanation about the functions that are used and the connection details that are required.

There are three main parameters that you need to understand.

Server / Host: This is the location of the database server. In most cases, this is referred to as the host. Basically, it is like a URL to your database. On your local machine, it will probably be “localhost”. On your web host, it might be something such as “mysql38.example.com”.
User: The username of the MySQL user that you want to connect as.
Password: The password of the MySQL user that you want to connect as.

When connecting to a MySQL server, you will need to provide these three details.

mysql_connect

Although the mysql_* functions are deprecated, I felt as though I couldn’t exclude them from this tutorial. Using the function mysql_connect, we can connect to MySQL like so.

//Our server/host parameter.
$server = 'localhost';

//Our MySQL username.
$user = 'root';

//Our MySQL password.
$password = '123456';

//Attempt to connect.
$connection = mysql_connect($server, $user, $password);

//Check if we are connected.
if($connection === false){
    echo 'Failed to connect!';
} else{
    echo 'We are connected!';
}

Note that you will obviously need to use the connection details that are relevant to your database server. In many cases, a local MySQL installation will have the following default details.

Server: localhost
User: root
Password: *blank* Most default MySQL installations have a blank password by default. In these cases, we have to supply an empty string. If we attempt to supply a password when there is no password, then we will receive the following error: “Access denied for user ‘root’@’localhost’ (using password: YES)”.

If you are using a default account on a local installation, then your connection code will probably look like this.

$connection = mysql_connect('localhost', 'root', '');

Note that if a connection fails, the mysql_connect function will return a boolean FALSE value. However, if the connection is successful, then it will return a MySQL connection resource that can be used to query the database.

Selecting the database.

Before you can run any SQL queries on your tables, you will need to do two things.

Connect to MySQL (already covered above).
Select a database.

Remember that you can have multiple databases on the same MySQL server. Hence the reason that we must select a database before attempting to run any SQL queries on our tables.

An example of connecting to MySQL and selecting a database with PHP.

//Connect to the MySQL server.
$connection = mysql_connect('localhost', 'root', '');

//Select our database.
mysql_select_db('our_database_name', $connection);

//Run our SQL queries.

Using the function mysql_select_db, we were able to select our database AFTER connecting to MySQL. The process will always be the same:

Connect to the server.
Select your database.
Query your tables.

A lot of beginners struggle to grasp this, simply because many tutorials toss them straight into the deep end without explaining the basic steps that need to be taken.

PDO.

As we mentioned above, the mysql_* functions are deprecated. This means that you should not use them to build new web applications. Instead, you should use something like the PDO object.

$user = 'root';
$password = 'super_secret_password';

$pdo = new PDO('mysql:host=localhost;dbname=our_db', $user, $password);

If you look closely, you’ll see that we are using the same parameters.

We have our server (aka, the host), which is ‘localhost’. We have our username, which is ‘root’. And then we have our password, which is ‘super_secret_password’.

Note how you can also supply a parameter called dbname. This means that we can connect to our MySQL server and select our database in one line of code.

Let’s connect using the default MySQL details.

//Our MySQL user account.
$user = 'root';

//Our MySQL password.
$password = '';

//The server / hostname of MySQL.
$server = 'localhost';

//The name of our database.
$database = 'my_database';

//Connect using PDO.
$pdo = new PDO("mysql:host=$server;dbname=$database", $user, $password);

//Run SQL queries on our tables.

Hopefully, separating the details onto different lines has made it a bit clearer! Feel free to use the code above; inserting your own details where needed.

PHP won’t connect to MySQL.

This can be a frustrating issue. Thankfully, there are ways to debug our connection issues. The first order of business is to check for connection errors. How you catch these errors depends on which library you are using.

PDO connection error.

Detecting connection errors w/ PDO is pretty straight-forward, as the library allows you to throw exceptions.

//Our connection details.
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test';

//An options array.
//Set the error mode to "exceptions"
$pdoOptions = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);

//Connect
$pdo = new PDO("mysql:host=$host;dbname=$database", $user, $password, $pdoOptions);

If you fail to connect for whatever reason, the code snippet above will throw an exception error. Some common examples:

Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known. ‘

This occurs when you have entered an incorrect host name / server name.

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

This occurs when you use the wrong password (or you enter a password, even though the account in question doesn’t have one).

Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [1049] Unknown database ‘blah”

This one is pretty straightforward. Basically, the database “blah” does not exist.

mysql_connect connection error.

Here’s an example of error-checking with the mysql_* functions.

//Our connection details.
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test';

//Connect to MySQL.
$conn = mysql_connect($host, $user, $password);
if($conn === false){
    throw new Exception(mysql_error());
}

//Select our database.
$selectDb = mysql_select_db($database, $conn);
if($selectDb === false){
    throw new Exception(mysql_error($conn));
}

If you purposely use incorrect details (for testing purposes), you’ll see that the errors are relatively the same as the ones that are thrown while using the PDO object.

Querying MySQL.

Follow-on tutorials on how to query MySQL with PHP: