In this tutorial, I will describe how you can use Cpanel, PHPMyAdmin for your projects and how you can connect databases with .php files/applications/webpages to retrieve data from it.
To begin with, you will need a hosting service and domain name for your website, once you’ve set up your hosting and domain name, login to your Cpanel or a similar dashboard.
Now you can follow the steps below to setup database, username, password, SQL table and connect it to a webpage using PHP script.
Preparing MySQL Database
1. Creating a Database to Store and Organize all your Data
1. Select the “MySQL Database Wizard” from the databases section or you can use the search option to find it and then click on it.
2. In the “Create A Database” step, enter your desired database name ( I am naming it as ‘sampleDatabase’ ) and hit “Next Step” button.
Later on while using this database you’ll have to use the name with the prefix that your host gives you (my database prefix in the screenshot above is colored/blurred). I’ll refer to it as “host_sampleDatabase” from now on.
3. Now you have to create users for your database, go ahead and type your database username and set a password and hit the “Create User” button.
Make sure to note it down/save the details somewhere because you’ll always need these credentials while accessing the database or for establishing connections to this database.
(I am setting the username as my name (Malhar) and using the in-built password generator to set the password, here also username is prefixed with host-name so we’ll call it “host_Malhar” from now on.)
4. In the next step you’ll be asked to set privileges for your users, you can set it as per your requirement or just select “All Privileges” and hit the “Next Step” button.
That’s it, you have successfully created a database and added a user to it. Now save the details as you’ll need them in the subsequent steps.
-
Database Name: host_sampleDatabase
-
Username: host_Malhar
-
Password: [your password]
2. Creating a SQL Table
1. Now go back to cPanel dashboard and select “PHPMyAdmin” or search for it from the search bar on your dashboard and click on it.
2. From the left sidebar, select your recently created database (host_sampleDatabase) and open the “SQL” tab from top bar.
Make sure you’ve first opened the database “host_sampleDatabase” and then SQL tab or else the SQL query we’ll run in the next step might end up creating table in the wrong database.
3. Enter the following SQL query in the SQL Query Field in the SQL tab and press “Go” to execute it. You can use the same query for learning or your desired query to create your desired table.
CREATE TABLE sampleData (
value1 INT AUTO_INCREMENT PRIMARY KEY,
value2 INT NULL,
value3 INT NULL,
value4 INT NULL
)
Kudos! You should now be able to see your newly created table “sampleData” in the “host_sampleDatabase” database.
3. Adding Data in the Table
We’ll now add some dummy data to the “sampleData” table for us to retrieve it using php on to our webpage.
1. Select the newly created table “sampleData” from the left sidebar, then open the “SQL” tab from the top and enter the following query in the query field and then hit the “Go” button to execute it.
INSERT INTO sampleData (value1, value2, value3, value4) VALUES (11, 22, 33, 44)
You can select the browse tab to see the table and check if the data is correctly entered into it.
(Select the newly created table “sampleData” from the left sidebar, then open the “Browse” tab from the top.)
You can see the query is executed successfully and dummy data is added into the table.
We’ve finished preparing MySQL Database and adding data to it. Next we’ll create a PHP script and retrieve data from our table on to the webpage.
PHP Script to Retrieve Data from the MySQL Database and Print it on a Webpage
Now that we have a backend database and data table, let us use php to fetch the data on to a simple webpage.
1. Head on to cPanel dashboard again and select & open “File Manager” this time.
2. Now select the “public_html” option from the left sidebar and click on “+File” from the top bar.
3. Create a new file in /public_html with a new file name and .php extension: sample.php
4. Now select your newly created file and click on “Edit” from the top bar and copy the following code snippet.
<?php
echo "This is a simple Webpage"."<br><br>";
$servername = "localhost";
// Database Variables
$dbname = "Your_Database_Name_Here";
$username = "Your_Database_Username_Here";
$password = "Your_Password";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Checking Connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to fetch data
$sql = "SELECT * FROM sampleData";
$result = $conn->query($sql);
if ($result = $conn-> query($sql))
{
while ($row = $result->fetch_assoc())
{
echo $row['value1']." ";
echo $row['value3']." ";
echo $row['value2']." ";
echo $row['value4'];
}
}
else {
echo "Error:" . $sql . "<br>" . $conn->error;
}
// Closing the connection
$conn->close();
?>
Before saving the file, you should modify the database variables ($dbname, $username, $password) with your database details.
$dbname = "host_sampleDatabase"; // Your Database Name
$username = "host_Malhar"; // Your Database User
$password = "Your_Password"; // Your Database User password
After adding your database name, username and password, save the file.
5. Last step is to access your domain name in the next URL path and you can see your data fetched and printed on the page.
https://YOUR_DOMAIN.com/sample.php
That’s it! You can see your fetched data printed in your browser. Congrats!