Introduce

This tutorial will be very helpful if you are just starting to learn about website programming. We will show you how to connect PHP to MySQL database . If you work on the web, make sure you need to manage, adjust, display, or delete tables in the database.

What do you need to prepare?

Before you begin you need to prepare:

  • Access the hosting control panel account

Step 1 – Create MySQL Database

Hostinger Control Panel MySQL Databases Section

This step is necessary if you do not have a MySQL database yet. At Hostinger, the MySQL database can be easily created in MySQL Databases section . If necessary, you can read more instructions for creating MySQL database on cPanel (English).

Create database in mysql database

Once you have entered the MySQL Databases section , enter the information and press the Createbutton .

IMPORTANT: Save the information you have just filled. You need it to connect the database in php in the next step.

Step 2 – Write code to connect PHP to the MySQL database

You are trying to connect the database in php, which means that the website needs a PHP code to do this. In this example, the database might be u266072517_name and the related user database is  u266072517_user . You also need to know the password, in this case the password we set in Step 1 is buystuffpwd .

You also need to enter the correct servername or hostname. In Hostinger, MySQL hostname is also located in MySQL Databases section . In this example, the hostname would be: mysql.hostinger.com .

In the case when you want to connect mysql with php at the local machine (the script takes the database of the website located on the server with the database), you can use the name localhost to set the hostname. If this is not the case, or if you are connecting php to a remote mysql database (the connection script and the database are not on the same server), you may have to use the database server’s IP address or database hostname. . For more details, contact your hosting provider for proper hostname information.

There are currently two methods to connect to MySQL databases: MySQLi and PDO . An important change is that both methods support ‘prepared statements’, which enhances SQL injection resistance when connecting mysql with php to correct information. The old function ‘mysql_’ has been deprecated (obsolete) and is no longer used and developed, because it is completely insecure.

READ  Dowload free Android Dating App 3.7

MySQLi stands for MySQL Improved,  it adds features in the interface of MySQL. PDO stands for PHP Data Object cluster The main difference between PDO and MySQLi is that PDO supports many different types of databases (MySQL, MS SQL, Postgre DB) in the same script, you only need to write data related functions once. PDO is ‘object oriented’, the connection between website and database is created by object variables. For example when creating an object:

  1. $ my_Object = new OBJECT ( ) ;

MySQLi

The following is a basic example of how to get a website’s database through a simple PHP code:

  1. <? php
  2. $ servername = “mysql.hostinger.com” ;
  3. $ database = “u266072517_name” ;
  4. $ username = “u266072517_user” ;
  5. $ password = “buystuffpwd” ;
  6. // Create connection
  7. $ conn = mysqli_connect ( $ servername, $ username, $ password, $ database ) ;
  8. // Check connection
  9. if ( ! $ conn ) {
  10. die ( “Connection failed:” . mysqli_connect_error ( ) ) ;
  11. }
  12. echo “Connected successfully” ;
  13. mysqli_close ( $ conn ) ;
  14. ?>

The main method of this script is the function ysqli_connect () . This is the PHP function, you can learn more here .

At the top of the code, we see there are declared variables and values ​​assigned to them. Normally, we need four variables to set up database connection in php code: $ servername, $ database, $ username and $ password . In this code, we have set the correct database information for these variables, so that later when using the mysqli_connect () function we can use this variable to give this function.

Next, the mysqli_connect () function will try to establish a connection to the database with the information provided in the code, the next part is the code that will execute if the mysqli_connect ()function succeeds. As you can see, after connecting successfully, it will continue with the following function:

  1. if ( $ conn-> connect_error ) {
  2. die ( “Connection failed:” . $ conn-> connect_error ) ;
  3. }

Function die () is executed here, it will cancel the script and produce the result we set. By default, it displays Connection failed: and includes an error message to let us know where the error is.

If the connection is successful, the following code will be executed.

  1. echo “Connected successfully” ;

This line simply shows the message that we specified, because we used the IF function that was only activated when connecting an error.

The last part of the code is:

  1. mysqli_close ( $ conn ) ;

It will close the connection. Without this line, the connection will be closed after the script finishes. You can use this line to close the connection to the database before the script finishes.

You can also run code by accessing scripts from the domain.

PDO

A PDO database connection needs to create a ‘PDO object’ with Data Source Name (DSN), username and password. DSN defines your database type, database name, and any other required information. The DSN can be a basic variable used as a parameter when creating a PDO object, shown as the code below.

PDO supports many different database types and DSN is where many different connections are defined, replacing the ‘mysql:’ line with another database type name. In fact, the script can help the user choose which connection to use and write code that selects the appropriate variable in the DSN. In this tutorial, we will choose to use MySQL. Here’s how to get a database of websites based on PHP and MySQL.

When creating the PDO object for database connection, you can change it to code ‘try … catch …’. This means that this script will try to connect using the specified code, if there is a problem, the code in the ‘catch’ section will run. You can use catch block to display error codes or other code if the test fails. In our example, a simple error code will appear to report an error when any information is incorrect.

  1. <? php
  2. $ servername = “mysql.hostinger.com” ;
  3. $ username = “u266072517_user” ;
  4. $ database = “u266072517_name” ;
  5. $ password = “buystuffpwd” ;
  6. // Tạo một kết nối mới vào MySQL cơ sở dữ liệu dùng PDO
  7. $ conn = new mysqli ( $ servername, $ username, $ password ) ;
  8. // Check connection
  9. if ( $ conn-> connect_error ) {
  10. die ( “Connection failed:” . $ conn-> connect_error ) ;
  11. }
  12. echo “Connected successfully” ;
  13. ?>

Step 3 – Check connection and fix common errors

successful connection

If the PHP connection code with mysql is successful and there are no other problems, you will see this message after accessing the website:

In case the connection fails, you will see a different message. The error code may be slightly different between MySQLi and PDO.

Fix PHP MySQL Connection: access denied for user error

For example, if you enter the wrong database password, the error will appear as follows:

db connection failed

Error on MySQLi :

Error on PDO:

pdo connection failed
database hostinger

So in the case of seeing this error, the first thing to check the password information of the database. The common error is caused by incorrect database password. Another reason may be that you do not have a user assigned to the database. In Hostinger’s control panel, database information and password can be reset in the  MySQL Databases section :

cPanel MySQL Database Details

And in cPanel, you will see information like below, make sure you have assigned the user to the database:

Fix PHP MySQL Connection: connection failed. Can’t connect to MySQL server

With MySQLi:

Can’t connect to server MySQL on ‘server’ (110) means that the script cannot find the database of the website because the database server is not responding. This error may cause us to misplace the servername  Maybe because we still leave the default server name as  localhost  instead of the server name provided by the hosting provider.

With PDO:

  • [HY000] means general error .
  • [2002] means that  it cannot connect to the MySQL server locally . The rest of the error code is extra information, explaining that the “host” could not be found.

Also, when fixing errors, check the error code (English) stored in the error file. You can find it in the same script folder that is running. For example, if we were running the script in the public_html directory , you would see the error_log file is also in the public_html directory.

Open it, you will see an error when the database connection script in php creates, it will help you find a way to fix database connection error.

Epilogue

In this tutorial, we have shown you how to connect php to a MySQL database using MySQLi and PHP Data Objects (PDO). You can use this document in combination with other advanced scripts, but make this step successful because connecting the database in php first is the first step in the process of manipulating the database.

Read more :
How to connect PHP to MySQL Database on wordpress

Introduce This tutorial will be very helpful if you are just starting to learn about website programming. We will show you how to connect PHP

Editor's Rating:
5

Leave a Reply

Your email address will not be published. Required fields are marked *