MySQL Basics and Inserting Data in Database

Before we learn how to enter data fetched using a form in a database, it is first necessary to establish connection between the PHP script and the database. This connection is generally established in a config file. This config file is then included in all the webpages that are created.

Create database and table

First we create the database named sample and table named sampletable.

We will be fetching 3 types of data from the form – name, email id and description. All these fields are shown in the table along with one other field – id.

This id field is defined as a separate entity and it works as a primary key. Primary key is a field that is unique for each row or tuple in a table. Hence, we can use it to separately identify each entry or row in a table.

Here if you see, we have defined the type of id field as INT and the index as primary. We have also ticked the Auto Increment field which means that whenever a new entry is added to the table, the Id field will auto increment itself for the next row so that it is unique for each row. The initial value of id is 1.

The type of different fields are VARCHAR and TEXT. VARCHAR is used to store a string with length upto 255 characters and the length of the field has to be specified here. TEXT is used to store a large string.

Config File

We use config.php file to create connection between database and PHP pages. This file is based on 4 parameters – hostname, user, password and database. The host is the server address where the database is hosted, user is the user who is allowed to access the database and database is simply the name of the database.

The code of config.php file is as shown

<?php
    $mysql_hostname = "localhost";
    $mysql_user = "username";
    $mysql_password = "password";
    $mysql_database = "sample";
    $bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
    mysql_select_db($mysql_database, $bd) or die("Could not select database");
?>

mysql_connect() function connects the user to host and mysql_select_db() function selects the specified database.

The default username in XAMPP is root and there is no password that is a blank password must be used.

Config file is included in all the other files so that it is not required to create a new connection for each file.

Now, we will see four files form.php , target.php, link.php and delete.php where we will create the form, direct the user upon submission of form, list all the entries in the table and delete data from the table respectively.

Form.php

The code is built simply as shown in previous tutorials. However, this time we also include the config file. There are three fields to be filled by the user – name, email id and description. The code is as follows –

<?php
    include 'config.php';
?>

<html>
<body>
    <form method = "POST" action="target.php">
        Name : <input type="text" name="name" /><br><br>
        Email Id : <input type="text" name="email" /><br><br>
        Description : <br>
        <textarea cols="100" rows="5" name="description"></textarea><br><br>
        <input type="submit" value="Submit" />
    </form>     
</body>
</html>

Target.php

<?php
    include 'config.php';

    if(isset($_POST['name']) && isset($_POST['email']) && isset($_POST['description'])){
        
        $name = $_POST['name'];
        $email = $_POST['email'];
        $description = $_POST['description'];
        
        if( !empty($name) && !empty($email) && !empty($description) ){
            
            
            // This query is used to enter the information in the database. mysql_query() function executes the query
            $query = "INSERT INTO `sampletable` (`name`,`email`,`description`) VALUES('$name', '$email', '$description')";
            mysql_query($query);
            
            echo "<p style='color:green;'>Your submission has been recorded</p>";
            
        }
        else
            echo "<p style='color:red;'>Please fill in all the details.</p>";
        
    }
    
    else 
        echo "<p style='color:red;'>Please submit the form properly.</p>";
    
?>

Link.php

<?php
    include 'config.php';

    //This query is ised to fetch information from the database. mysql_fetch_array() function is used to fetch rows successivelt that is one by one.
    $query_fetch = mysql_query("SELECT * FROM `sampletable`");
    while($row = mysql_fetch_array($query_fetch) ){
        
        $id = $row['id'];
        $name = $row['name'];
        $emailid = $row['email'];
        $des = $row['description'];
        
        echo "<br><p>Id : ".$id."</p>";
        echo "<p>Name : ".$name."</p>";
        echo "<p>Email Id : ".$emailid."</p>";
        echo "<p>Description : ".$des."</p><br>";
    }
?>

Delete.php

<?php
    include 'config.php';

    echo "<h2>Deleting the entry with id 2 and displaying data in the table</h2>";
    
    //This query is ised to delete information from the database with id 2.
    $query = mysql_query("DELETE * FROM `sampletable` WHERE `id`=2");
    
    while($row = mysql_fetch_array($query_fetch) ){
        
        $id = $row['id'];
        $name = $row['name'];
        $emailid = $row['email'];
        $des = $row['description'];
        
        echo "<br><p>Id : ".$id."</p>";
        echo "<p>Name : ".$name."</p>";
        echo "<p>Email Id : ".$emailid."</p>";
        echo "<p>Description : ".$des."</p><br>";
    }
?>

For instance, we can fill the form 2 times and then delete data with id 2.

This is how the table is shown with data in phpmyadmin.

Image of link.php after filling the form twice.

Image of delete.php after deleting data with id 2

In a nutshell, we have learned how to create a connection between PHP script and database, how to create form and database table according to it and then enter details in the database that were filled in the form.

This content has been helpful to you?

Thanks for contributing!

Yes No