June 16, 2020
5min Read
Tautvydas V.
If you are a beginner in website development, knowing how to use PHP script to connect to MySQL can be beneficial. This way, you can modify, view, or manage the tables created in the MySQL database. This article will show you the easiest ways to do it, let’s begin.
Get up to 90% OFF for PHP Hosting Plans.
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:
For instructions on how to create a MySQL database using cPanel, see this tutorial. Keep in mind, however, that these are blank databases and you will need to fill in data before you can manipulate it.
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!
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.
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.
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 are connecting to a database from a remote location (for example, your computer) 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.
Follow these steps to use MySQLi to connect a PHP script to MySQL:
<?php $servername = "localhost"; $database = "databasename"; $username = "username"; $password = "password"; // 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); ?>
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.
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:
<?php $host = 'localhost'; $dbname = 'databasename'; $username = 'username'; $password = 'password';
<?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()); }
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.
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.
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.
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.
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.
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
Replied on June 15 2017
It is on portuguese
Replied on June 16 2017
You can still paste it here :) Google translate will help
Replied on 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
Replied on June 15 2017
And in Line 6 of the said file is: $conn = mysqli_connect($server_name,$mysql_username,$mysql_password,$db_name);
Replied on June 15 2017
$server_name="mysql.hostinger.co.pt"; Is this what is wrong?
Replied on 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.
Replied on June 16 2017
Hey, Yes, it's correct.
Replied on 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
Domantas G.
Replied on June 15 2017
Hey Carlos, This is strange. Can you paste full error message here?