Insert Multiple Rows Using Same/Single Id In PHP

In this tutorial, we will learn how to insert multiple rows using same id in php. For example, we have some fruit items like orange, mango, banana etc. Now, we will consider the fruit as item category and fruit-related items as 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 the 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, how to create multiple rows using jquery.

Insert data 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 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 same/single id in php.

--

--

--

Hi, This is Biplab and I am web developer well versed in PHP and other open source technologies. I am here to share my experience with the community.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Embedded web server in Flutter

Getting started with Progressive Web Apps

TOP 5 LATEST TECHNOLOGIES THAT EVOLVED WEBSITE DEVELOPMENT

Bézier Surfaces (CENG469 HW1)

Make Your Cloud Transformation Faster Using Amazon Web Services

Introduction to Git and Github

Testing ActiveRecord associations in the Rails console

KNAPSACK Dynamic Programing

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Bipsmedium

Bipsmedium

Hi, This is Biplab and I am web developer well versed in PHP and other open source technologies. I am here to share my experience with the community.

More from Medium

A Mini Php Mvc Structure: Zerdesht

Cookies With PHP

INTRODUCTION TO PHP

Generate monthly chart data with Eloquent & Carbon