Connect MySQL Using PHP in 2 Simple Methods

Try this guide with our instant dedicated server for as low as 40 Euros

connect php to mysql

It is rare to find a PHP application with a database. In almost all cases, these applications rely on a database to store information. During the course of operations, these applications use the database interface to send and receive data.

PHP can work with a wide range of databases. In fact, one of the reasons why developers still opt for PHP is its compatibility with popular database engines, such as MySQL. PHP offers dedicated libraries to work with MySQL and similar databases. 

The first step in this process is to connect the PHP application to the MySQL database.

In this tutorial, we will show you how to use MySQLi Extension and PHP Data Objects. Note that we won’t talk about the old-fashioned mysql_ functions anymore because they’re not recommended.

Table Of Contents

  1. Connect PHP to a MySQL Database
  2. The Prerequisites
  3. Two Methods to Link up with a MySQL Database Using PHP
    1. Method #1: Connect To MySQL With PDO
    2. Method #2: Connect to MySQL with MySQL Improved (MySQLi) Extension
  4. Potential Errors in Using MySQLi and PDO
  5. Conclusion
  6. FAQs

Connect PHP to a MySQL Database

Before we go into the details of the two methods of connecting PHP to MySQL databases, it is a good idea to look into the prerequisites. 

The Prerequisites

Before you try out the two methods we will mention in later sections, make sure you have the following:

  • You have MySQLi or PDO extension on your server
  • A MySQL Database
  • A user account with sudo or administrator privileges 

Two Methods to Link up with a MySQL Database Using PHP

Two common methods for connecting to a MySQL database with PHP are:

  1. PHP Data Objects (PDO)
  2. PHP’s MySQLi Extension

We will now detail the specifics of these steps and the common mistakes you might encounter when using MySQLi and PDO.

Method #1: Connect To MySQL With PDO

PHP Data Objects (PDO) is like a bridge that helps PHP talk to databases. 

Unlike MySQLi, which is mostly for MySQL, PDO can work with many types of databases. It’s more versatile and can handle various databases, not just MySQL. PDO can also prepare and execute statements on both the server and the client side.

NOTE: PDO won’t work if your PHP version is older than 5.0. It’s bundled with PHP 5.1 and up.

Here’s how you write PHP code to connect to a MySQL database using PDO:

<?php

$servername = "localhost";

$database = "database";

$username = "username";

$password = "password";

$charset = "utf8mb4";

try {

$dsn = "mysql:host=$servername;dbname=$database;charset=$charset";

$pdo = new PDO($dsn, $username, $password);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo “Connection Okay”;

return $pdo;

}

catch (PDOException $e)

{

echo “Connection failed: ”. $e->getMessage();

}

?>

Credentials Syntax

First, we set up five variables (server name, database name, username, password, and character encoding) and give them their values. Make sure these values match your connection details.

If you are developing locally, the server name should be localhost. On the other hand, if you’re connecting to an online server, use its name.

The charset variable tells the database what character encoding to use for data. The recommended option is utf8mb4.

Credentials Syntax

Try and Catch Blocks

PDO has a helpful feature: an exception class that handles any possible issues with database queries. It uses try and catch blocks to deal with database connection problems.

If there’s a hiccup during connection, the code stops and tries to fix the problem. Catch blocks can be set to display error messages, run correction or backup snippets.

Try and Catch Blocks

In this specific try and catch block, the first variable is the dsn, which stands for the data source name. This part is important because it tells PDO what type of database it connects to and its name, among other details.

In our case, we’re connecting to a MySQL database, but PDO can work with many other types of databases. If you’re using a different database, just replace mysql in the syntax with the one you’re using.

try

Next comes the pdo variable. This variable is used to create a connection to the database. It requires three parameters:

  1. DSN: data source name
  2. Username of database
  3. Password of database

pdo

Next, we use the setAttribute method to add two settings to the PDO:

  • PDO::ATTR_ERRMODE
  • PDO::ERRMODE_EXCEPTION

This method tells PDO to throw an exception if a query doesn’t work as expected.

PDOATTR_ERRMODE and ERRMODE_EXCEPTION

We recommend including the line echo “Connection Okay; to make sure that the connection has been successfully established.

echo Connection Okay

Return the pdo variable to connect to the database.

return pdo

After handing back the pdo variable, set up the catch block to handle any connection errors. Instruct it to show a message if the connection fails using the PDOException.

pdo exception

Method #2: Connect to MySQL with MySQL Improved (MySQLi) Extension

MySQLi is an extension designed specifically for MySQL databases. 

Starting from version 4.1 and up, it gives access to advanced features in MySQL systems. 

It offers both an object-oriented and a procedural way to work with databases. While it supports server-side prepared statements, it doesn’t support client-side ones. This is an important difference between PDO and MySQLi.

MySQLi comes bundled with PHP versions 5 and higher.

The following PHP code to connect to a MySQL database using the procedural approach with MySQLi:

<?php

$servername = "localhost";

$database = "database";

$username = "username";

$password = "password";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

echo “Connected successfully”;

mysqli_close($conn);

?>

Credentials Explained

The script starts with four variables: server name, database name, username, and password, each assigned with its specific value. Make sure these values match your connection details.

Credentials Explained

Next comes the main PHP function mysqli_connect(). This is a critical component that actually sets up the connection with the database you’ve specified.

create connection

Next, there’s an if statement.” It’s the part of the code that checks if the connection was successful. If it fails, it displays the message “Connection failed.” The die function then prints the message and stops the script.

If the connection works, it shows “Connected successfully.”

successful connection

When the script finishes, it automatically closes the connection with the database. If you need to close the connection yourself, you can use the mysqli_close function.

mysqli_close function

Potential Errors in Using MySQLi and PDO

When working with PDO and MySQLi, you will encounter several deal-breaker errors that could block database connections. 

We will quickly go through the two most critical errors (and their fixes) that you should be aware of when connecting to MySQL using PHP.

Incorrect Password(s)

The password in the PHP code must match the one in the database. If they don’t match, you won’t be able to connect to the database. You’ll see an error message saying the connection failed in that case.

Possible solutions

  1. Double-check the database information to make sure the password is right.
  2. Confirm that there’s a user set up for database access using the credentials you provided in the code..

Unable to Connect to MySQL Server

If PHP can’t connect to the MySQL server, it might be because it doesn’t recognize the server name. 

If you are working with a local server, make sure you use localhost as the server name. Alternatively, you should always provide the proper server name in for a remote server. 

For other problems, check the error_log file in the same folder as your script. It can give you clues on how to solve any issues you encounter.

Conclusion

Connecting to MySQL using PHP is essential for many web applications. By following the steps outlined in this guide, you can establish a secure and reliable connection to your MySQL database. 

We mentioned sample code snippets that highlighted how you can use the PDO and MySQLi to connect PHP to MySQL. In addition, we briefly described two critical issues that can break your code. 

FAQs

Q. What is a database connection?

A database connection is a link established between a PHP application and a MySQL database server. It allows the PHP script to interact with the database, perform queries, and retrieve or modify data.

Q. How do I create a database connection in PHP files?

You can create a database connection in PHP files using functions like mysqli_connect() or PDO::__construct() by providing the necessary database credentials such as server name, username, password, and database name.

Q. What is the error mode in database connection?

Error mode in database connection determines how errors are handled during database operations. In PHP, you can set error modes using functions like mysqli_report() or PDO::setAttribute() to control error handling behavior.

Q. What are database credentials and why are they important?

Database credentials refer to the server name, username, password, and database name required to establish a connection to the MySQL database. They are essential for authentication and access control.

Q. Can I connect to a database system remotely?

Yes, you can connect to a database system remotely by specifying the correct server IP address or domain name in your PHP script, along with the appropriate credentials.

Q. What is the default username for MySQL database connection?

The default username for MySQL database connection is often ‘root’, but it may vary depending on the database setup and configuration.

Q. What are mysql_ functions and should I use them for database access?

mysql_ functions are a set of functions used for interacting with MySQL databases in PHP. However, they are deprecated and no longer recommended for use due to security vulnerabilities and lack of support. It’s better to use MySQLi or PDO extensions for database access.

Q. How do I ensure proper database connection in my PHP application?

To ensure a proper database connection in your PHP application, double-check the database details, such as server name, username, password, and database name. Also, handle connection errors gracefully using try-catch blocks or error handling functions.

Q. What should I do if I encounter unauthorized access errors during database connection?

If you encounter unauthorized access errors during database connection, verify that the database credentials (username and password) are correct and have the necessary permissions to access the database. Also, check the server’s access control settings.

Q. What database tasks can I perform once I have a successful database connection?

Once you have a successful database connection in your PHP script, you can perform various database tasks such as querying data, inserting, updating, or deleting records, executing stored procedures, and managing transactions to ensure data integrity and consistency.

Try this guide with our instant dedicated server for as low as 40 Euros