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

  • PHP MySQL Development Services

  • Published On August 6, 2021

Featured Image
How to connect My SQL Database with a PHP Website

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

How to connect My SQL Database with a PHP Website

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:

How to connect My SQL Database with a PHP Website


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.

How to connect My SQL Database with a PHP Website

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.

How to connect My SQL Database with a PHP Website

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.

How to connect My SQL Database with a PHP Website

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. 

How to connect My SQL Database with a PHP Website

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

How to connect My SQL Database with a PHP Website

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.

How to connect My SQL Database with a PHP Website

Check Connection

How to connect My SQL Database with a PHP Website

For PDO, Close the Connection like this

How to connect My SQL Database with a PHP Website

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.
How to connect My SQL Database with a PHP Website

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

How do I connect my SQL database to my website?

– Check that your needs for the contact us page’s HTML form are met.
– Construct a MySQL database and table.
– Build a data entry form in HTML to communicate with the backend.
– Build an HTML form, and then use PHP to submit the data to a MySQL database.

How to use mysql database in WordPress?

– MySQL relies on tabular data structures to keep information organized. The admin panel for MySQL is often included with web hosting packages. This open-source program simplifies the execution of SQL queries in databases, and it’s free to download and use. Tables, rows, and fields may all be altered, removed, or added.
– WordPress automatically creates 12 new tables in your database after a basic installation. They are seen in the picture on the left. Valuable data for your site’s operation is stored in these tables.

What is PHP MySQL database?

As the most widely used programming language for websites, PHP is indispensable. It’s open-source, cost-free, and hosted on the server (the code is executed on it). On the other hand, MySQL is a Structured Query Language (SQL)-based RDBMS (Relational Database Management System) (SQL). Furthermore, it has a zero-cost and open-source license.

How do I connect to an external MySQL database?

You may expand the Drivers node of the Database Explorer to see the MySQL driver. When you right-click the MySQL (Connector/J driver) icon, the “Connect to Server” menu option appears. On the page titled “Basic Settings,” type “HOST>: PORT>/DB>” to connect to your database.

    Ready for Digital Transformation?

    Ask our team for custom made business growth plan.

    Related Articles

    • what are the benefits of using php mysql development services
      What are the benefits of using PHP MySQL Development Services?

      What is PHP MySQL and What are the major benefits? Talking about What are the benefits of using PHP MySQL Development Services? With the increased use of the internet, websites

    • why prefer cakephp for web development
      Why Prefer CakePHP For Web Development

      There is no deny that CakePHP is the most popular and powerful PHP framework that offers rapid application development platform to the web developers. This amazing framework brings a whole

    • benefits of switching to symfony 2
      Benefits of switching to Symfony 2

      PHP, a scripting language for web applications has matured big time through years. It has been used extensively by the developers to build websites as well as web applications. PHP