Guide for PHP MySQL Connection Setup Under cPanel

PHP MySQL connection setting up on a cPanel hosting is generally challenging for beginners. To make things simple, we have created this guide, which offers a detailed overview of how to set up a MySQL database and connect it to a PHP application using cPanel hosting. This article also explains about how to deploy a local project (running on XAMPP, LAMP, WAMP, etc.) in cPanel Hosting.

To make your PHP project with MySQL live, just follow below simple steps.

How to Setup MySQL Database in cPanel?

To setup MySQL database in cPanel, basically you need to create MySQL Database, create MySQL User, assign User to Database & manage User Privileges and import database using .sql file. Steps for the same are explained in below sections:

Create MySQL database in cPanel:

1. Start by logging into cPanel and selecting the MySQL databases option under the Databases section.
2. Enter the appropriate database name in the text field next to Create New Database (for example, username_test) and press the Create Database button.

Creating User to access MySQL Database:

You have to create a MySQL User and give the necessary privileges, before they may access and control the database.

1. To create the MySQL User, Enter the appropriate user’s name in the text field next to MySQL Users >> Add New User and set a password for the new user.
2. It is highly recommended to use the Password Generator to create strong password. Use it by clicking create a secure password.

Note: Remember to save the database name, username, and password to use in PHP programs to connect to the database.

Assign User to MySQL Database:

Now as we have both a MySQL Database and a MySQL User, we must assign the MySQL User to the Database in order to set the appropriate privileges. To do this, follow the below steps:

1. Select Add User To Database under the Database section. Make sure you select the appropriate Database and User before proceeding. Once confirmed, simply click the Add button.
2. You have to assign the appropriate privileges to the MySQL User in the next screen. Here, we simply select the All Privileges option to give users full access to our MySQL database so they may manage it without any limitations and click the Make Changes button.

NOTE: You can assign multiple MySQL Users with different sets of privileges to the same MySQL database.

Import Database to cPanel:

1. Using phpMyAdmin in XAMPP or LAMP, export the local database to a .sql or .sql.gz file to import it into cPanel.
2. Then, select Databases >> phpMyAdmin option under cPanel. On the phpMyAdmin page, select your database and click Import. The next window will provide you the option to browse and upload SQL script to the server.

• Before uploading, make sure you have chosen the correct database. Press Go to upload that file to the database. Wait for operation to finish.

How to connect to MySQL database using PHP in cPanel?

Connect MySQL database with PHP Script in cPanel

1. First, select the File Manager option under Files section from the cPanel home page. 
2. In the next window, navigate to the directory public_html and upload the local file by selecting Upload from the top menu. You can change the name of file if you want. However, make sure the page’s extension is .php.
3. Select that file and then click Edit. The file will open in the next window in a text editor; set the correct database name, username, and password as shown below.

<?php
$servername = "localhost";
$database = "databasename";
$username = "username";
$password = "password";
// Create connection using musqli_connect function
$conn = mysqli_connect($servername, $username, $password, $database);
// Connection Check
if (!$conn) {
    die("Connection failed: " . $conn->connect_error);
}

else{
   echo "Connected Successfully!";
   $conn->close();
}

?>

• To test this PHP script open a browser and enter the following URL  www.domainname.com/yourfilename.php
• In case of database connection error, the PHP result will show “Access denied” or “Could not connect to database”.

MySQL Insert operation with PHP Script in cPanel

Now we can perform the data insert operation in the table. Create another file with .php extension. Write the following code with the table name, columns and values.

<?php
$servername = "localhost";
$database = "databasename";
$username = "username";
$password = "password";
// Create connection using musqli_connect function
$conn = mysqli_connect($servername, $username, $password, $database);
// Connection Check
if (!$conn) {
    die("Connection failed: " . $conn->connect_error);
}

else{
  //insert query
$qu="INSERT INTO table_name(column1, column2, column3) VALUES ('values1', 'values2', 'values3')";
//query execution
if($conn->query($qu)===TRUE){

echo "Inserted Successfully";
   $conn->close();
}else{
echo "Insert Failed ".$conn->error;
$conn->close();
}
}

?>

In Summary:

By following steps in this guide we successfully performed PHP MySQL connection and uploaded the PHP MySQL project on cPanel. Let us know in the comments below if you face any difficulties in following this guide.

MediaStroke provides the best suitable hosting option from all startups to big enterprises. Visit our hosting planes to get the best services in the market.