How to Connect PHP to MySQL Database
For beginners in website development, understanding how to use PHP scripts to connect to MySQL can be highly advantageous. This enables you to modify, view, or manage the tables within the MySQL database. In this article, we’ll guide you through the simplest methods to achieve this. Let’s get started!
Creating a MySQL Database (Optional)
This step is required if you do not have a MySQL database. If you are a Hostinger user, you can easily make a new one via Hostinger’s hPanel in a few steps:
- Locate MySQL Databases menu under the Databases section.
- Fill in the necessary fields and hit Create.
For instructions on how to create a MySQL database, check our cPanel tutorial. Keep in mind, however, that these are blank databases and you will need to fill in data before you can manipulate it.
Important! Write down the credentials of the MySQL database you just created for the next step. Don’t forget to remember the database username and username password as well!
Two Ways a PHP Script can Connect to MySQL
There are two methods to connect to a MySQL database using PHP: MySQLi and PDO.
MySQLi stands for MySQL Improved. It is a MySQL-exclusive extension that adds new features to a MySQL database’s interface. MySQLi is both procedural and object-oriented, with the former being the attribute inherited from the older version of MySQL.
The original MySQL breaks down a task into linear, step-by-step procedures, which makes it difficult to modify because you have to edit the code from the top. Meanwhile, MySQLi sees data as a set of interchangeable objects with functions, allowing users to add or remove data easily.
PDO stands for PHP Data Object. Unlike MySQLi, PDO is only object-oriented and supports a number of different database types that use PHP, such as MySQL, MSSQL, Informix, and PostgreSQL.
Important! The original mysql_ functions are deprecated and should not be used as they are unsafe and no longer being maintained or developed.
One of the most important features they both support is prepared statements, which accelerates the time needed for MySQL to execute the same query multiple times. It is also used to prevent SQL injection attacks when making changes to the database or inserting user-supplied input into a database query or statement.
Whichever method you use, you will need the correct information so you can connect to the MySQL database you have made. This is where the MySQL database details you have previously saved come in handy.
You also need the correct server name or hostname for the configuration. Hostinger uses “localhost” as its MySQL server’s hostname. In general, this is the name that you’ll want to use if you uploaded your PHP script to the same server as the database.
In contrast, if you’re using a MySQL remote connection to connect to a database, you will have to use the IP address of the MySQL server. For more details, contact your hosting provider so they could provide you with the correct information on what to use as the hostname.
Using MySQLi to Connect a PHP Script to MySQL
Follow these steps to use MySQLi to connect a PHP script to MySQL:
- Head over to File Manager -> public_html.
- Create a New File by clicking the icon from the sidebar menu.
- Save the file as databaseconnect.php. You can replace the name with whatever you like, just make sure it is using php as the extension.
- Double-click to open the file and copy-paste the following lines of code into it. Change the first four values below <?php with the credentials you noted earlier:
<?php $servername = "localhost"; $database = "u123456789_DatabaseName"; $username = "u123456789_User"; $password = "MyStr0ngPa$!"; // Create connection $conn = mysqli_connect($servername, $username, $password, $database); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } echo "Connected successfully"; mysqli_close($conn); ?>
MySQLi Code Explained
The main method used in this script is mysqli_connect(). This is an internal PHP function to establish a new connection to a MySQL server.
At the beginning of our code, we see a few variable declarations and values assigned to those variables. Usually, we need four of them to establish a proper database connection: $servername, $database, $username, and $password. In the code, we set our database details as values for those variables, so they can be passed into the function.
If the connection is not successful, the die() function is executed. This basically kills our script and gives us a connect error message that we have set. By default, the MySQL connect error will say Connection failed followed by an exact error message describing the issue.
On the other hand, if the MySQL connection is successful, the code will print Connected successfully instead.
The last part of the code is mysqli_close, which will simply close the connection to the database manually. If not specified, the MySQL connections will close by itself once the script ends.
Using PDO to Connect a PHP Script to MySQL
The other method using PHP script to connect to MySQL is by using PDO. This is similar to the previous method, but with a slight variation:
- In the public_html, create a file named pdoconfig.php and insert the following code. As always, don’t forget to replace the placeholder values with your database information. Save and Close it once you’re done:
<?php $host = "localhost"; $dbname = "u123456789_DatabaseName"; $username = "u123456789_User"; $password = "MyStr0ngPa$!";
- Create another file named databaseconnect.php in the same directory, but with the following code. If you named the previous file differently, make sure to change the value of require_once.
<?php require_once 'pdoconfig.php'; try { $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); echo "Connected to $dbname at $host successfully."; } catch (PDOException $pe) { die ("Could not connect to the database $dbname :" . $pe->getMessage()); }
PDO Code Explained
A PDO database connection requires you to create a new PDO object with a Data Source Name (DSN), Username, and Password.
The DSN defines the type of database, the name of the database, and any other information related to the database if required. These are the variables and values we stated inside the dbconfig.php file, referenced one time by the line require_once in the databaseconnect.php.
In the latter, you will find the try…catch.. code. This means that the script will try to MySQL connect using the code provided, but if there is a problem, the code in the catch section will run. You can use the catch block to display connect error messages or run alternate code if the try block fails.
If the connection is successful, it will print out the message “Connected to $dbname at $host successfully.” However, if the attempt fails, the catch code will show a simple error message and kill the script.
Unlike when using MySQLi, after you’re finished using a PDO script, you don’t need to manually close the connection. It is automatically closed when the PDO object is destroyed, or your script ends.
Checking Connectivity and Troubleshooting Common Errors
To check whether the connection is successful, access your domain like so: yourdomain/databaseconnect.php. If you name the PHP file with something different, make sure to change it accordingly.
You will see “Connected successfully” or variants of this message if everything is running without any issue.
Now if the connection was not successful, you will see something different. The error messages look slightly different for MySQLi and PDO.
Incorrect Password Error
This error happens if we change the password or any credential in the PHP code (but do not change it in the actual database).
In case you see an “Access denied” or “Could not connect to database” message accompanied by “(using password: YES)” at the end, the first thing to do is to check the database details. There could be a typo or a part that’s missing.
Cannot Connect to MySQL Server
If you get “Can’t connect to MySQL server on ‘server’ (110)” in MySQLi, it means the script did not get a response from a server. This happens when we set “server” instead of “localhost” as the $servername, and this name is not recognized.
The error message in PDO will look like “Connection failed: SQLSTATE[Hy000] [2002]” followed by more details stating that the My SQL host was not found. But the way to troubleshoot it is the same as the above.
And of course, it is always important to remember one golden rule of troubleshooting an error: checking your site error log.
The log can be found in the same folder where the script is running. For example, if we are running a script in public_html, we will find the error_log in the same folder.
Conclusion
In this tutorial, we have learned the very basic knowledge about how to connect a PHP script to a MySQL database using MySQLi and PHP Data Objects (PDO).
Hopefully, this guide was helpful for those who are just starting out with web development. After all, connecting to a database is the first, most important step when working with more advanced scripts and configurations.
Let us know in the comments below if you face any issue following this guide.
Comments
June 15 2017
Hi, it gives me an error after it tries to connect: Warning:mysqli_connect():(HY000/2002): then something about the connection attempt failed because the connected component didnt respond correctly after a certain period of time, or the established connection failed because the host did not respond It is not yet implemented in the website on hostinger, just wamp
June 15 2017
Hey Carlos, This is strange. Can you paste full error message here?
June 15 2017
It is on portuguese
June 16 2017
You can still paste it here :) Google translate will help
June 15 2017
Warning: mysqli_connect(): (HY000/2002): Uma tentativa de ligação falhou porque o componente ligado não respondeu corretamente após um periodo de tempo, ou a ligação estabelecida falhou porque o anfitrião ligado não respondeu. in D:\wamp64\www\Projeto\conn.php on line 6
June 15 2017
And in Line 6 of the said file is: $conn = mysqli_connect($server_name,$mysql_username,$mysql_password,$db_name);
June 15 2017
$server_name="mysql.hostinger.co.pt"; Is this what is wrong?
June 16 2017
Hey, This is wrong. hostinger.co.pt does not exists. Please check your MySQL hostname in MySQL databases section inside Hostinger panel.
June 16 2017
Hey, Yes, it's correct.
June 16 2017
Hey Carlos, It seems that you are on localhost. Re-check your MySQL database credentials and make sure you are trying to connect to localhost database.
November 06 2017
hello please how can i do to create two or three database in the same mysqluser
August 04 2019
Sorry, my link to w3schools was removed, see https://www.w3schools.com/php7/php7_mysql_connect.asp or search for w3schools w3schools php7 mysql_connect
July 06 2020
Thanks
November 21 2020
I am confused on the connectivity part of the tutorial. I have created the php file and have created the database. How do I run the php file to check the connectivity?
February 09 2021
Hi there! In order to run the file, you'd need to enter the URL into your browser: http://yourdomain/databaseconnect.php. If you changed the name of your file to something else, you should enter that instead of databaseconnect.php
November 30 2020
How do I separate the PHP file containing my connections credentials from other PHP scripts that insert or otherwise manipulate the data? In short, I don't want to put this first php file in the public_html directory.
February 09 2021
Hi there, Mohsen! As long as there's only one database connection file, you should have no issue - no other files from your public_html will interfere ;)
May 14 2021
I am having a small issue I made my coding in xampp in my local server, and now i am testing it in live, but it works perfect in xampp but it is giving me a error "can't currently handle this request." "HTTP ERROR 500" my credentials are correct and when i try the above code it successfully connects to the database!
May 19 2021
Hiya Gulra, If you are coding in XAMPP and getting the 500 Error there may be a problem in your XAMPP servers root .htaccess file. Try navigating to your XAMPP servers doc xampp directory. Then, delete the .htaccess file Stop and restart Apache If that doesn't solve the error, please contact Customer Success to help you.
October 21 2021
Hi, I'm having trouble connecting. I think the cause is "Incorrect Password Error". Because the page I accessed had the phrase “(using password: YES)". My guess is that the reason for the error is that one of "MySQL Database", "MySQL User", "MySQL Host", or "password" is wrong. And here is what I need help with. ・Check if mysql password and hostinger login password are the same. I am assuming they are the same. ・Check if the MySQL Host is “localhost". I created a new database in hostinger and was trying to connect to it. In that case, is it OK to use “localhost" as the MySQL Host? Or is this wrong? This is what I want to know.
October 27 2021
Hi there, your MySQL password should be that of the database you've created. Due to security reasons we suggest to never reuse your passwords and have them different. If you need help changing your password have a look at our guide here. As for the "host", generally "localhost" should work, but if you're not able to succeed with it, you can also try out "mysql". I also suggest to check with our Customer Success team for more urgent assistance :)
January 22 2022
Hi, can i know if it is possible for to connect my html form to the mysql database in wordpress through hostinger ? And if it is possible, can i know how to connect it ? I'm really need some help ASAP please.
January 25 2022
Hi! Yes, you can connect your HTML form to your database using PHP code. You can find a guide on how to create the connection here and another more generic about PHP to MySQL connection here. In order to create a database on Hostinger, simply follow this guide and then to see how to edit your WordPress in HTML in order to insert the form, check this tutorial.
March 17 2022
Hello please can you help me out with a login script that would be suitable in this hostinger hpanel your registration script was really helpful, thanks
March 22 2022
Hey there, can you clarify where you intend this script to help login?
May 14 2022
Hello, I established connection to my database successfully but now trying to insert data from a form is not going through.
May 18 2022
Hi! Can you clarify what error message are you getting?
May 17 2023
how to connect the multiple database in the one web page in hostinger please guide to do this
May 19 2023
Hello there! Usually websites come with a single database. However if you want to connect multiple databases, that's also possible, but you would need to consult with a developer for more information as that is a custom solution.