How to connect My SQL Database with a PHP Website? [Updated Guide]

MySQL and PHP are essential components of both the development and deployment of software applications.

Knowing how and when to use PHP scripts to connect to MySQL can be beneficial, as it enables you to modify, view, and manage the tables in your MySQL database. 

The database management system (DBMS) revenue grew to 65 billion U.S. dollars in 2020. Cloud DBMS accounted for the majority of the overall market growth, as database systems are migrating to Cloud platforms.

When we talk about databases during development, it mostly means MySQL, a powerful DBMS supporting multiple projects of all sizes. MySQL’s greatest strength lies in its ability to handle massive amounts of data without crashing. 

This article will show you the simplest ways to connect databases in PHP using MySQL. But, before that, you should know a bit more about PHP and MySQL databases.

Let’s get started. 

Furthermore, MySQL is a free and open-source relational database management system (RDBMS) that encompasses SQL standards and RDBMS functions. It can power any project, be it big or small. MySQL allows us to manage large amounts of data without compromising quality. 

Ranking of the most popular database management systems worldwide, as of June 2021

You can also use the PHP solution to access large datasets from the connected or distributed database via a web page. Many databases, including MySQL, MariaDB, MongoDB, and Oracle, supported by PHP.

There are three types of methods in PHP to connect MySQL database through backend:

• MySQL
• MySQLi
• PDO

MySQL: This technique is becoming outdated due to safety concerns such as SQL injection.

MySQLi: This API includes a connector function that links the PHP and MySQL databases with the appropriate range of processes and plugins. Despite the fact that nothing has changed from the old iteration, you should consider this method to be more safe and secure to incorporate.

PDO: PDO, or PHP data objects, is a database abstraction layer extension. It serves as a link between both the backend and the MySQL database. It enables you to make adjustments without having to alter any PHP code. 

As a result, you get to work on multiple databases simultaneously using handy and straightforward code. This is the most popular method for connecting your PHP web page to the MySQL database.

To connect MySQL databases on different servers, we’ll also give you an overview of the database using PDO.

1. Connect MySQL using Localhost Server
2. Connect MySQL using Cloudways Server
3. Connect MySQL using PDO
4. Connect MySQL using Remote MySQL

Creating a MySQL database at Localhost

The first and foremost thing to do is install XAMPP to run your database, and its console will appear something like this:

The user name will be generated once XAMPP has been installed.
Then, by clicking on the start button, you can launch the MySQL module.
Choose the ‘Admin’ feature as mentioned in the XAMPP console, or navigate to localhost/PHPMyAdmin in your browser.
Add the password to the account now. Next, navigate to the user account via the link and look for the following.

To adjust the login credentials, press Edit privileges, enter the password and save it for future use. This password will allow you to access the database.

Switching passwords isn’t required, but it’s a good habit to get into.

What is Database Creation?

You can now introduce a new database to your access list. First, go to the PHPMyAdmin page and press the NEW button, as directed below.

Once a window appears, select the type of your active database.
Selecting utf8 general ci as your tabulation option will aid in the handling of all your queries and data. After you’ve filled out the form, click the Create button.
This new database will not display any tables. 

Why Creating a Folder is needed ?

Next, navigate to the htdocs folder which can be found in the XAMPP folder. To store all web files, create a new folder within the htdocs folder. XAMPP will use this folder to implement and run PHP web pages or a website. 

Creating a Database Connection File in PHP

We’ll begin with a PHP file called ‘DB connection.php.’ S save a different file so that there is no need to write a connection code for each file individually. Instead, users can conveniently include this file in the requisite file by calling it and then using the ‘include’ function. 

Creating a different file will come in handy when you move the project to another system and only change one file. Below is the PHP code for the db_connection file.

What is Code Explanation?

• $dbhost: to specify the host server at localhost.

• $dbuser: To specify the user password, and $dbname will identify the user name. Use the same credentials you used in PHPMyAdmin.

• $dbname: will describe the requirements of the database that we introduced previously. 

How to Check Database Connection?

The PHP file ‘index.php,’ which will test your database link, is provided below. The code is as follows:

To check the outcome, open a browser and navigate to link-localhost/practice/index.php. If the interaction is successful, you will receive the following message.

How you can Connect to the MySQL database Using the Cloudways Server

Creating the MySQL Database at Cloudways Server

To begin using this method, you must first have PHP installed. We’re working with PHP 7.2 and a MySQL database. We host PHP applications on the Cloudways server, which ensures easy optimization. Create an alternative account to use the Cloudways server.

Additionally, explore the software tab, verify the database details, and launch the database manager in the Cloudways server for the PHP application..

Creating a Database Connection

To connect to a database, you need to use the MySQL connect function.

This function returns a pointer, also known as a handle, to the database connection. Later in the code, you will use this function. Include your database credentials once you’ve obtained the handle.

Make a new PHP file called dB connnection.php, then save. If you create multiple files where you’d like to insert or select data from databases, there is no need to write the database connection code each time. All you need to do is include it at the top of your code using the PHP custom function, then call and use this function.

At this point, you can use either the MySQLi procedural connection query or the PHP PDO-based database connection.

MySQLi Procedural Query

Connect MySQL Database with PHP Using PDO

PDO is an abbreviation for PHP Data Object. It is more general than MySQL and allows for greater database flexibility. PDO supports both server-side and client-side prepared statements. PDO, which is only object-oriented, works with various PHP-based databases including MySQL, MSSQL, Informix, and PostgreSQL, unlike MySQL.

Check Connection

For PDO, Close the Connection like this

Connect the MySQL database using Remote MySQL

Log in to the Cloudways server using your login credentials for this procedure. Choose ‘Servers’ from the top menu and then the required server. To get started, follow the steps below.

• Then, navigate to the Security menu option in the left menu and then press the MySQL tab
• Fill in the IP address in the “Add IP to Whitelist” text box and press the “Add” button.
• Repeat the process when you have multiple IP addresses.
• After you have finished, click the “Save Changes” button to implement the changes.

Potential Errors with MySQLi and PDO

Invalid Password

If the passwords in the PHP code and the database do not match, a connection to the database will not be successful. This can result in a failed connection and an error message.

Possible solutions include:

• Examine the database information to ensure the password is correct.
• Ascertain that the database has a user assigned to it.

I’m unable to connect to the MySQL server

Identify the name of the MySQL server for PHP to link to it. Check if you have set the server name to “localhost.”

In the event of other errors, refer to the error log file for assistance in order to resolve issues. Store the file in the same directory as the script.

Conclusion 

From the above preceding content, you must have gained a basic understanding of how to connect a PHP script to a MySQL database, as well as MySQLi and PDO.

MySQLi and PDO both have advantages. Remember, MySQLi only works for MySQL databases. However, if you want to switch to a different database with MySQLi, you will have to completely change the code. In contrast, PDO can work with 12 different databases, making the transition much simpler.

You can work on any of the three methods mentioned above. Many developers use PHP and MySQL due to their readability and well-structured code. Learning both technologies can prove beneficial in the long run. This enables you to build dynamic websites with interactive elements.

FAQs