MySQL and PHP are essential components of both the development and deployment of software applications.
Understanding how and when to use PHP script to connect to MySQL can be helpful; it will allow you to modify, view, and manage the tables that have been created in the 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. The best part about MySQL is its ability to withstand massive amounts of data without getting crashed.
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.
MySQL is a free and open-source relational database management system (RDBMS) that includes SQL standards and RDBMS functions. You can use MySQL to power any project, no matter how 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, are supported by PHP.
There are three types of methods in PHP to connect MySQL database through backend:
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.
- Connect MySQL using Localhost Server
- Connect MySQL using Cloudways Server
- Connect MySQL using PDO
- 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.
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.
Now, navigate to the htdocs folder. It can be found in the XAMPP folder. To store all the web files, a new folder will be created in this htdocs folder. XAMPP will use this folder to implement and execute the PHP web pages or 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.
- $dbhost: to specify the host server at localhost.
- $dbuser: To specify the user password, and $dbname will identify the user name. These should be the same credentials you used in PHPMyAdmin.
- $dbname: will describe the requirements of the database that we introduced previously.
Checking 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.
Need help with MySQL?
Hire our experienced professional to assist you in connecting your MySQL database to PHP.
Using the Cloudways Server, connect to the MySQL database
Creating the MySQL Database on 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. Cloudways server can be used by creating an alternative account at https://www.cloudways.com/en/php-hosting.php.
Explore the software tab, check database details, and launch the database manager in the Cloudways server with 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 to the database connection( this is also known as a handle). This function will be used later in the code. 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 allows for both server-side and client-side prepared statements.PDO, unlike MySQLi, is only object-oriented and supports a wide range of PHP-based databases such as MySQL, MSSQL, Informix, and PostgreSQL.
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.
- When you’re finished, click the “Save Changes” button to apply the changes.
Potential Errors with MySQLi and PDO
Passwords in the PHP code must match the password in the database. A correlation to the database can indeed not be formed if the two do not resemble each other. The connection might as well fail, and you may receive 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
If the server’s name is not identified, PHP will not link to the MySQL server. Check to see if the server name is set to localhost.
In the event of other errors, refer to the error log file for assistance in order to resolve issues. The file is stored in the same directory as the script.
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. Keep in mind that MySQLi is only used for MySQL databases. But, if you’d like to switch to a different database, you will have to overhaul the entire code. PDO, on the other hand, can work with 12 various databases, making the transition much easier.
You can work on any of the three methods mentioned above. Because of their readability and code structure, PHP and MySQL have been widely used by many developers. Having to learn both technologies can be advantageous in the long run. This enables you to build dynamic websites with interactive elements.