Insert data into database and display in HTML Table using PHP & MYSQ

Inserting data into the database using PHP

Insert data in the database is done by the insert query. In this type of project, you have to perform two queries.First for insertion (insert query is used to insert the data into the database table )and second for display(select query is used to fetch(display) the data from the database ) data into table from the MYSQL database table usinig PHP. In the type of project, we used an HTML table and PHP script. When the user fills the form and click on submit button the values should be saved (insert) in the database and after the data should display into the HTML table

MYSQL insert query

As we have discussed above, we have to perform the SQL Insert Query for putting the information in the table of the MySQL database. In the previous tutorial, we have created the registration form using PHP and MYSQL. To create a registration form, also be aware of the INSERT query. To insert data into the database table, you should understand the following SQL query.

 
INSERT INTO `records`(`column_name-1`, `column_name-2, `column_name-3`, `column_name-4`, `column_name-5`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5]);

Create database table using query

Whenever insert data via PHP, we first create a table of MySQL database. HTML form data becomes to fill in the database table. You can create the table in MYSQL by using simple phpmyadmin panel or you can create a database table using SQL query. Before creating the database table, the fields are decided and after columns are created in the table for all fields. Let's create an MYSQL database table.


CREATE TABLE  `crud2` (

 `id` INT( 49 ) NOT NULL ,
 `pname` VARCHAR( 40 ) NOT NULL ,
 `pcode` VARCHAR( 30 ) NOT NULL ,
 `pprice` VARCHAR( 40 ) NOT NULL
);

In above query -

Table Name ="crud2"

Table fields - pname(denotes for Product name),pcode(Product Code),pprice(Product Price )

Make an HTML form for data INSERT

The HTML form has to be created to insert the data into the database table. Creating an HTML form is a very simple task. When a user fills data in the form and then clicks on the Insert button, the form data is inserted into the MYSQL database table. In the HTML form, we create input boxes for all the fields and also create a button to submit.

HTML Form (Insert Form)

<html>
<head>
    Insert Data and Display in Table 
</head>
<body>
 <form action="" method="post" name="form1">
Product Name
Product Code
Product Price
</form> </body> </html>

Designing INSERT form using CSS

With the help of the CSS stylesheet, we can design the INSERT form.Let's design HTML form.


	
After this the output comes out. insert data into database using PHP and MYSQL

Create a connection config file

The config file hanles the connection between PHP form and MYSQL database.

Now, create a file and save with name and extension "config.php".

<?php

$databaseHost = '127.0.0.1';//or localhost
$databaseName = 'dbhandle'; // your db_name
$databaseUsername = 'root'; // root by default for localhost 
$databasePassword = '';  // by defualt empty for localhost

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName);
 
?>

In above config file , we used mysqli_connect() function for a new connection. We passed this information as a parameter and stored in a variable $mysqli. You can change database host, database name, database username, and database Password according to your need.

Create a PHP script for INSERT data into MYSQL database table

Now we will create a PHP code to insert the data into the MYSQL database table .In this script, we will use the insert query to add form data into the database table.

1. First of all, we include config.php file using PHP include() function. You can PHP PHP require() function to include connection file.

2.Create a if condition for decision making.If input values are set then insert the values into the database table.If there are some errors, then display an error message and don't execute insert code.

3. Now we will perform a small validation. If user does not enter the value in input boxes then.
display the statement (Please enter the product name or product code or Product price).

4. After that, we will use Insert query to insert the data into the database.

PHP Script code (Insertion)

<??php
//including the database connection file
include("config.php");
if(isset($_POST['Submit'])) {    
$pname = $_POST['pname'];
$pcode = $_POST['pcode'];
$pprice = $_POST['pprice'];
        
// checking empty fields
if(empty($pname) || empty($pcode) || empty($pprice)) {                
if(empty($pname)) {
echo "Product Name field is empty.
"; } if(empty($pcode)) { echo "Product Code field is empty.
"; } if(empty($pprice)) { echo "Product Price field is empty.
"; } //link to the previous page echo "
Go Back"; } else { // if all the fields are filled (not empty) //insert data to database $result = mysqli_query($cser, "INSERT INTO crud2(pname,pcode,pprice) VALUES('$pname','$pcode','$pprice')"); //display success message echo "Data added successfully."; } } ?>

MYSQL SELECT query

You have inserted data into the MYSQL database table. Now you have to display the data. SELECT query is used to display data from a database. You can select data by columns. Fetch data from the database and display in an HTML table. Let's have a look of MYSQL select query.

 
SELECT* FROM `table_name` ORDER by id DESC;
			

Display data in HTML table using PHP

Now , we fetch the data from the MYSQL database using PHP script. As we discussed above , the SELECT query is used to get data from the database table and didplay in HTML table. 1. Include the config.php file . 2. Apply SELECT Query on MYSQL database. 3. Select coulumns for display data into HTML table . 4. Use while loop to get data from the database table through the loop one by one. 5.Use echo() built in function for display data into table.

Display in Table (Select data from database)


<?php
//including the database connection file
include("config.php");
$result = mysqli_query($cser, "SELECT * FROM crud2 ORDER BY id DESC"); // using mysqli_query instead
?>
 <html>

<?php 
while($res = mysqli_fetch_array($result)) {         
echo "";
echo "";
echo "";
echo "";    
                    
}
?>
Product Name Product Code Product Price
".$res['pname']."".$res['pcode']."".$res['pprice']."
</html>
Download the free source code here.