Main navigation

MySQL Database Tutorial: How to Create MySQL database on a live server and Insert data using PHP

adsense

Pre-requisites:

There will be not much pre-requisite knowledge required for this tutorial. Just some basic knowledge of PHP and MySQL will be enough.

You can create MySQL Database using two methods:

1) Download & Install WAMP Server. But we won’t suggest this method since at the end of the day you have to work on a live server, So why not start working on it.
2) Make a Live Server. We will use free version of 000webhost to create a live server.

Create MySQL Database on a live server

Open link https://www.000webhost.com/ and Sign Up for free. In the Sign Up form enter your Name, Email and Password. One more notable thing is Free Subdomain category. Here you can add the domain that you want to use temporarily. In our case, we chose androidtutorialpoint. So finally my subdomain was created as androidtutorialpoint.comlu.com. For you it could be different. Now after all information filled out correctly, you will get a confirmation email and Bingo, your free hosting site is ready. Now if you copy paste your subdomain or FTP Host name on google (In my case it is androidtutorialpoint.comlu.com) you will see following image:

MysQL Database

So first hurdle cleared. Now next step is to Create MySQL Database. When you are done with create account activity you will be able to see Status as Active and another option Go to CPanel . Click on it. A panel will be opened as shown in following fig.

CPanel

MysQL Database

Click on the MySQL option in the Database tab of CPanel. Fill the details i.e. database name, user name, password etc. Click on create database. In our case we filled:

MySQL database name: a4896787_details
MySQL user name: a4896787_andy

It will look like as follows:

MysQL Database

Following screen appeared after database created:

MysQL Database

Now go back to the CPanel and click on phpMyAdmin. Click Enter phpMyAdmin.

Note: In some cases following error could appear:

Error: #1045 – Access denied for user ‘@localhost’ (using password: YES)

MysQL Database

Solution of Error: #1045 is wait. Generally server takes sometime to create database. It could be 15min to half an hour. In my case it took around 25min. Just logout, have some tea and come after 15-30 mins. Database will be ready. When your database will be ready you will see following image.

MysQL Database

So finally your database is created. Another hurdle cleared ;). The next step is to create Table in MySQL Database.

Create Table in MySQL Database

We have named Table name as people_data and Number of fields as 3 in the above image. Click on Go. Now fill the details of the columns you wants to include in
Table. In our case, we have included three columns i.e. Id (Primary Key and Auto Increment), Name and Email as shown in following figure:

MysQL Database

Click on Save. So finally our Table is also created. Next Step is to insert data values in table.

Insert data into Table using PHP

There could be two ways to insert data into Table:

Insert using web method

This method is the easiest. Here you have to just Click on Insert tab in the above image and add corresponding data you wants to enter. You can also click on Search Tab to see the data values present in Table.

Insert using PHP

This is what used by most of the Web and App developers. We will insert data in MySQL Database using following steps:

1) Create a file named defines.php at your desktop. Include following code in it:

<?php

define("DB_HOST", "mysql12.000webhost.com");
define("DB_USER", "a4896787_andy");
define("DB_PASSWORD", "MyPassword");
define("DB_DATABASE", "a4896787_details");

?>

Above code composed of credentials that we will use to connect to server. Your credentials will be different. You can see your host_name, user_name etc. in MySQL tab of CPanel. Click on it and you will see your MySQL Database credentials.

2) Create a file named db_insert.php and add following code:

<?php
	
    require_once 'defines.php';
        
    $con = mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE) or die('Unable to Connect');

    // Check connection
    if(!$con) { 
    
      // creation of the connection object failed
      die("connection object not created: ".mysqli_error($con));
    
    }

    else {
     
      echo "Connected Successfully <br><br>";
      
      // receiving the post params
      $name = $_POST['name'];
      $email = $_POST['email'];

      if($name == '' || $email == '') {
      	echo "Please fill details again.<br>";
      }
      else {

      	$sql = "INSERT INTO people_data (Name,Email) VALUES ('$name','$email')";

      	if(mysqli_query($con,$sql)) {
      	  echo "Congratulations. Your data is successfully Inserted.<br>";

      	  echo "Following is the data in your table:<br>";
          
      	$mysql = "SELECT * FROM people_data";

        $result = mysqli_query($con, $mysql);

        while($row = mysqli_fetch_array($result))
        {
         echo $row['Name'] . " " . $row['Email'];
         echo "<br />";
        }
        
      }
      else {
        echo "There is some problem. Please try again.";
      }

      }

      mysqli_close($con);
     
    }
    
?>

This is the most important code. It is responsible for capturing data from form and inserting it into MySQL database. Note following points in the above code:

1) mysqli_connect is used to connect to server. If connected successfully then Connected Successfully will be shown.

2) $POST[‘name’] is used to get name as filled by user in the form. Similarly for $POST[’email’]. We will make a form in the next step.

3) Data is inserted in table people_data using MySQL query INSERT INTO. If data is inserted successfully then message will be shown as Congratulations. Your data is successfully Inserted.

4) Finally all the items of Table is shown is using mysqli_fetch_array.

We hope this PHP code is pretty much clear now. Now make a form where we will fill Name and Email which needs to be inserted into database.

Make following form.html:

<!DOCTYPE HTML>
<html>  
<body>

<form action="db_insert.php" method="post">
Name: <input type="text" name="name"><br>
E-mail: <input type="text" name="email"><br>
<input type="submit">
</form>

</body>
</html>

Above code is pretty much self explanatory. So finally all our files are completed. The only work remaining is to upload all these files to server. Let’s do this final step.

Uploading files to Server

Files can be uploaded using FileZilla or normal web method. We will use normal web method to upload. Go to Cpanel and Click on FTP Details. Note down FTP host name, FTP user name, FTP password etc. You can also change your FTP password in case you don’t know it. In my case following page is opened.

MysQL Database

Again go to CPanel and click on File Manager 3. It will probably ask you for FTP credentials which you have already noted down as above. Fill these as I have filled in the following image:

MysQL Database

Click Login. After login you would see a folder named Public_html. Click on it. Here we have to upload all the files that we have just created. There is a Upload Files button at bottom. So now upload all files i.e. defines.php, db_insert.php, form.html. After uploading it will look as below:

MysQL Database

So Finally all work is done. We have been able to upload files on server. Now go to your FTP host name or Subdomain that we created initially. In our case it is “https://androidtutorialpoint.comlu.com/”. Here you would see three files that you just uploaded. Click on form.html. A form will be opened asking your name and Email as shown below.

MysQL Database

Fill any Name and Email and click Submit. Bingo !! Your name and email is inserted in the server. In our case following results came after submitting query:

MysQL Database

It could be possible that you get some PHP error but that will happen only if any of the above steps is missed or some Syntax is incorrect. Let us know if you get any problem. We will try to resolve it. All the best :).



What’s Next?

Now that you know how to work on a live server, you can try to make a login and registration form. Nevertheless in the next tutorial we will cover this tutorial.

Till then stay tuned !! Don’t forget to subscribe our blog for latest android tutorials. Also do Like our Facebook Page or Add us on Twitter.


Reader Interactions

Advertisment ad adsense adlogger