In this tutorial, we will learn how to insert multiple rows using the same id in PHP. For example, we have some fruit items like orange, mango, banana, etc. Now, we will consider the fruit as an item category and fruit-related items as a fruit subcategory. We will insert all the subcategory items using the parent category inside the MySQL database. I hope it’s clear.
Next, we will select the parent category as shown in the above image and create multiple subcategories with the help of jquery so that we can insert the multiple subcategories under one category.
Now, we will create multiple rows for subcategories with jquery. If you don’t know then read also,
Insert multiple rows using the same id into the database after the form submission
- When we submit the form data then the parent category will be inserted into a table name ‘items’ in the database and the subcategories will be inserted into another table ‘item_details’ using the category id from the ‘items’ table.
Steps of inserting the subcategories using the parent category id
- First of all, we have to create two tables in the database.
- The first table contains the row including the information of the parent category.
- The second table contains the rows including the information corresponding to the parent id of the first table.
- Now, we will check the submitted category with the help of rowCount() function whether a row exists or not. If there is any row then we will take the category id using the lastInsertId() function.
- We will count the total subcategory submitted with the help of the hidden id mentioned in the form.
- We will run a for loop to insert the subcategories using the last inserted id into the ‘item_details’ table.
In order to do this, we have to create two tables, the first table contains only the category name, and the other table contains the subcategory name and the category id.
Here, I am using two tables, items and item_details.
DDL information of the table ‘items’
CREATE TABLE items (id int(10) unsigned NOT NULL AUTO_INCREMENT,item_name varchar(225) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
DDL information of the table ‘item_details’
CREATE TABLE item_details (id int(10) unsigned NOT NULL AUTO_INCREMENT,item_id int(10) DEFAULT NULL,item_subcategory varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Complete Code:-
<?php
//session_start();
include('connect.php');
if(isset($_POST['save']))
{
//insert item_name into items table
$item_name = $_POST['item_name'];
$sql="INSERT INTO items(item_name)VALUES('$item_name')";
$stmt=$con->prepare($sql);
$stmt->execute();
//get the item id by lastinsertid function
if($stmt->rowCount()>0)
{
$lastid = $con->lastInsertId();
for($i=0;$i<sizeof($_POST['serial']);$i++)
{
$item_id = $lastid;
$item_subcategory = $_POST['item_subcategory'][$i];
$sql1="INSERT INTO item_details(item_id,item_subcategory)VALUES('$item_id','$item_subcategory')";
$stmt1=$con->prepare($sql1);
$stmt1->execute();
}
}
}
?>
<html>
<head>
<title>ajax example</title>
<link rel="stylesheet" href="bootstrap.css" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="bootstrap-theme.css" crossorigin="anonymous">
<style>
.container{
width:50%;
height:30%;
padding:20px;
}
</style>
</head>
<body>
<div class="container">
<h3 align="center"><u>INSERT MULTIPLE ROW INTO DATABASE USING SINGLE ID</u></h3>
<br/><br/><br/>
<form class="form-horizontal" action="#" method="post">
<div id="newuser"></div>
<div class="form-group">
<label class="control-label col-sm-2" for="item">Items*:</label>
<div class="col-sm-6">
<select class="form-control" name="item_name" id="it" required="">
<option value="0">--Please Select--</option>
<option value="FRUITS">FRUITS</option>
<option value="ANOTHER FOOD ITEMS">ANOTHER FOOD ITEMS</option>
</select>
</div>
</div>
<br/>
<br>
<h4><u>Insert Item Subcategory</u></h4>
<br>
<div class="form-group">
<label class="control-label col-sm-2" for="item">Item Subcategory*:</label>
<div class="col-sm-6">
<input type="text" class="form-control" name="item_subcategory[]" id="itsubcat">
</div>
</div>
<input type="hidden" name="serial[]" class="sl" id="hd" value="1">
<div id="next"></div>
<br/>
<button type="button" name="addrow" id="addrow" class="btn btn-success pull-right">Add New Row</button>
<br><br>
<button type="submit" class="btn btn-primary pull-right" name="save">Submit</button>
</form>
</div>
<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>
<script>
$('#addrow').click(function(){
var length = $('.sl').length;
var i = parseInt(length)+parseInt(1);
var newrow = $('#next').append('<div class="form-group"><input type="hidden" name="serial[]" class="sl" value="'+i+'"><label class="control-label col-sm-2" for="item">Item Subcategory*:</label><div class="col-sm-8"><input type="text" class="form-control" name="item_subcategory[]" id="itsubcat'+i+'"></div><input type="button" class="btnRemove btn-danger col-sm-2" value="Remove"/></div>');
});
// Removing event here
$('body').on('click','.btnRemove',function() {
$(this).closest('div').remove()
});
</script>
</body>
</html>
NOTE*
Download the bootstrap CSS and js files from google and include the path of the files in the href attribute of link tag and src attribute of the script tag respectively.
CONCLUSION:- I hope this article will help you to insert multiple rows using the same id in PHP. If you have any doubt then please leave your comment below.
