List of content you will read in this article:
The following article talks about how you can connect the MySQL database with the PHP website. For this, you should know about PHP and MySQL databases.
MySQL is an open-source RDBMS (i.e. Relational Database Management System) that comes with SQL standards and RDBMS functions. With MySQL, you can power projects irrespective of their size. MySQL allows you to handle large data volumes without impacting the performance. The PHP solution also enables you to access dynamic data from the connected database through a web page. PHP comes with support for many databases like MySQL, MariaDB, MongoDB, Oracle, etc.
You can use the following PHP methods to connect the MySQL database from the backend.
- Using MySQL: this method has become obsolete as it comes with security issues like SQL injection.
- Using MySQLi: this API comes with a connector function that connects both the PHP and MySQL database with a proper set of processes and extensions. Though there is nothing different from the previous version, you can consider this process to be more safe and secure to implement.
- PDO: PDO or PHP data objects, an extension to the database abstraction layer. It acts as an interface between the backend and the MySQL database. It allows you to make changes without changing any PHP code. You can also work on multiple databases with easy and portable code. This method is commonly used to connect your PHP website to the MySQL database.
Now, we will connect the MySQL database to different servers and discuss how you can connect the database with the help of PDO.
- Connect the MySQL database using the Localhost Server
- Connect the MySQL database using the Cloudways Server
- Connect the MySQL database using PDO
- Connect the MySQL database using Remote MySQL
Note ** PHPMyAdmin is a control panel that helps you to manage the database.
Method 1- Connect the MySQL Database Using the Localhost Server
Creating a MySQL database at Localhost
Install XAMPP to run your database, and its console will look like below.
Once you install the XAMPP, the user name will be created. First, start the MySQL module by clicking start. Then, select the 'Admin' option as shown in the above XAMPP console or go to localhost/PHPMyAdmin in your browser. Now you can add the password to the account. From the link, go to the user account and look for the below.
To change the Admin password, now click Edit privileges, enter the password and save it so that you can use it later. This password will help you to connect to the database.
Changing passwords is not essential, but you can use this step as a good practice.
Creating a Database
Now you can add a new database to access. Navigate to the PHPMyAdmin page and click the NEW button, as shown below.
After a window appears, you can enter the name of your working database. Select utf8_general_ci as your collation option, and it will help in handling all your queries and data. Once you enter the details, now click the create option. We named our database as 'practice.'
As this is the new database, it will not show any tables under it.
Creating Folder
Now, open the htdocs folder. You can find it under the XAMPP folder. In this htdocs folder, we will create a new folder named our database 'practice' to store our web files. XAMPP will use this 'practice' folder to execute and run the PHP web pages or website.
Creating a Database Connection File in PHP
We will start with a PHP file, 'db_connection.php.' We prefer to save a separate file so that we do not have to write the connection code every time for every file. You can easily include this file to the required file using the 'include' function to call this file and use it. Having a separate file will be useful when you move the project to another system, and you only have to change one file instead of multiple codes. Below is the PHP code for the db_connection file.
Code explanation:
- $dbhost will specify the server's host at localhost.
- $dbuser will identify the user name, and $dbpass will specify the user password. These credentials should be the same as you used in PHPMyAdmin.
- $dbname will specify the database name that we have created earlier.
Checking database connection
Below is the PHP file 'index.php' that will test for your database connection. Below is the code:
Go to the browser and go to link- localhost/practice/index.php to check the result. You will get the following message if the connection is successful.
Method 2- Connect the MySQL Database Using the Cloudways Server
Creating the MySQL Database at Cloudways Server
To start with this method, you should have PHP installed on your system. We are using PHP 7.2 and MySQL database. We host PHP applications on the Cloudways server that ensures optimization without any hassle. You can use the Cloudways server from https://www.cloudways.com/en/php-hosting.php by creating an account for free.
Once you are in the Cloudways server with PHP application, navigate to the application tab to check database details, and launch database manager.
Creating a Database Connection
We will use the mysql_connect function for setting up a database connection. This function will return the pointer to the created database you provide in the code, which can be used later.
We will again create the db_connection.php file as above once you create and save that file. We can use the MySQLi procedural query or the PHP PDO based database connection.
MySQLi Procedural Query
Database Connection Using PDO
Checking the Database Connection
Closing the PDO connection
$conn = null;
Method 3- Connect the MySQL database using Remote MySQL
For this method, log in to the Cloudways server with your login details. Select the 'Servers' option from the top menu and select the required server. Now follow the below step to get started.
- Select the MySQL tab from the security menu on the left.
- Add the required IP address to "Add IP to Whitelist" and click add.
- You can add multiple IP addresses using the above step.
- After applying all the changes, select "save changes."
After the changes, you can quickly run queries.
Conclusion
This article will help you to get through the database connection from your PHP website or web page. You can consider working on any of the three methods described above. Many developers have widely used both PHP and MySQL due to their ease of understanding and code structure. Learning both technologies can be beneficial in the long term. This allows you to create dynamic websites with interactive features. Once you get to know your application and database connection, you can perform various actions from the front end to reflect the database's changes without going directly to the database.
