If you are new to website development, you need to know how to use PHP script to connect to MySQL. By this method, you can modify, view, or manage the tables generated in the MySQL databases. In this article, we are going to see in detail how to connect MySQL Database with PHP website.
You can connect MySQL database with PHP in the following ways
1. Connect using MySQL
2. Connect using MySQLi
3. Connect using PDO
4. Connect using Localhost server
5. Connect using Remote MySQL
Both MySQLi and PDO are used actively, But MySQL has become obsolete due to security reasons like SQL injection and more.
Connect Using MySQLi
MySQLi means MySQL improved. It is an API that uses a connector function to link the PHP app to the MySQL database. It offers a set of extensions and functions.
It performs just like their older version, but it is comparatively safer than it. It is a MySQL-extension that adds additional features to a MySQL database’s interface. It is object-oriented. The old version of MySQL split up a task into linear, step-by-step procedures that make it more complex to modify because you have to edit the code from the beginning. On the other hand, MySQLi sees data as a set of interchangeable objects with functions, enabling users to edit the data easily.
Follow the below-mentioned steps to use MySQLi to connect a PHP script to MySQL
Go to the file manager and locate public_html.
Create a New file by clicking the icon from the top menu.
Name the file as per your preferences and save it as databaseconnect.php.
Now click twice to open the file and copy-paste the codes into it. Now replace the first four values with the data you noted earlier.
Connect Using PDO
PDO means PHP Data Object. It is a database abstraction layer and it supports several different database types that use PHP, such as MSSQL, Informix, and PostgreSQL. It is an interface for the backend to interact with the MySQL database and edit without making any change in the PHP code. It also allows you to work with multiple databases. The main advantage of using PDO is that your code stays portable and simple.
A PDO database connection needs you to create a new PDO object with a Data source name( DNS), Password, and Username. The DSN denotes the types of database, names the database, and any other information related to the database if needed.
To connect PHP with MySQL follow the below-mentioned steps.
In the public_html, create a file, name it as pdoconfig.php and insert the code. Replace the credentials with your database information. Save and close, when you are completed.
Create another file name databaseconnect.php in the same directory and ensure that the change values.
Connect MySQL Database Using Localhost Server
In the PHP website, you can manage the database that you have created by accessing the PHPMyAdmin. Open your browser and go to the localhost or click Admin in XAMPP UI.
If you use XAMPP, you need to add a password by yourself. Use a strong password to connect with the database.
Create Database: Go to the homepage of PHPMyAdmin, now click the New button to create a database. In the newly opened window, name your database as per your preference.
Create A Folder In htdocs: Locate the folder where you installed XAMPP and open the docs. Create a folder inside c:/xampp/htdocs/ and name it as per your need.
Create Database Connection File In PHP: Create a new PHP file and save it. You have to create a database connection file because if you have created many files. You need to add it by using the PHP custom function on the top of the code and call its function. It also helps when you are moving your project location from one computer to another. If you change any values on the single file, then it will automatically reflect on other files.
Create a PHP file to Ensure database connection: Create a PHP file to connect with your database and name it.
Connect Using Remote MySQL
To connect with the remote MySQL connection, log into the Cloudways platform with the help of your credentials.
Click on a server in the menu bar and go to your target server from the list.
Now, go to the security menu and click the MySQL tab.
Now enter the IP address to the “Add IP to Whitelist” text area and click the “Add” button.
If you own many IP addresses, repeat the procedure.
When it is done, click the “Save Changes” to complete.
Now run the query.
In this article, we have learned about how to connect a MySQL database with a PHP website. We hope this guide will be helpful for those who have just started their journey in web development. If you need any help in connecting to the MySQL database, you can get help from a PHP Website Development Company.