Insert Multiple Rows Into MySQL With PHP Using Foreach Arrays
In this tutorial, we are going to learn how to insert multiple rows into MySQL with PHP using foreach arrays. To insert multiple form data, we need to generate multiple form rows and we will do it by using click function in jquery to generate the rows on clicking the button multiple times, and on the other hand, we will use the remove() function to remove the desired row.
Generating multiple rows on clicking Add new row button using jquery before submitting the form
- When we click add new row button the first time, then we will get the length of the row by using the class of the field ‘SL No’ from the class attribute of SL No field.
- Now, we will increase the length of the rows by adding 1 with parseInt(1) and store in a variable i.
- Now, we will add the next row using the append() function in jquery and use the length variable i with all the remaining fields of the form.
- We will also remove the added rows with the help of remove function as mentioned in the code given below.
Submitting the multiple form fields in the PHP scripts
- Here, we will receive the submitted rows using the field sl no.
- Now, we will check the total rows submitted in the for loop with the help of count() function.
- We will store the remaining fields in the array variable of the for loop and insert the field values in the table of the database.
Here I am using a table name ‘student’ to insert the student information.
Also Read, How to append data to the dropdown list using jquery ajax and php
DDL information of the table ‘student’
— — — — — — — — — — –
CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`student_name` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone_no` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int(20) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Complete Code:-
<?php
//session_start();
include('connect.php');
if(isset($_POST['submit'])){
for($i=0;$i<count($_POST['slno']);$i++){
$student_name = $_POST['student_name'][$i];
$phone_no = $_POST['phone_no'][$i];
$age = $_POST['age'][$i];
$date_of_birth = $_POST['date_of_birth'][$i];
if($student_name!=='' && $phone_no!=='' && $age!=='' && $date_of_birth!==''){
$sql="INSERT INTO student(student_name,phone_no,age,date_of_birth)VALUES('$student_name','$phone_no','$age','$date_of_birth')";
$stmt=$con->prepare($sql);
$stmt->execute();
//echo '<div class="alert alert-success" role="alert">Submitted Successfully</div>';
}
else{
echo '<div class="alert alert-danger" role="alert">Error Submitting in Data</div>';
}
}
echo "<script type='text/javascript'>";
echo "alert('Submitted successfully')";
echo "</script>";
}
?>
<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:80%;
height:30%;
padding:20px;
}
</style>
</head>
<body>
<div class="container">
<h3 align="center"><u>Inserting Multiple Rows in PHP</u></h3>
<br/><br/><br/>
<form class="form-horizontal" action="#" method="post">
<div class="row">
<div class="col-sm-1">
<label for="Age">Sl No:</label>
<input type="text" class="form-control sl" name="slno[]" id="slno" value="1" readonly="">
</div>
<div class="col-sm-3">
<label for="Student Name">Student Name:</label>
<input type="text" class="form-control" name="student_name[]" id="st_name" placeholder="Enter Student Name">
</div>
<div class="col-sm-3">
<label for="Phone">Phone No*:</label>
<input type="text" class="form-control" name="phone_no[]" id="pn" placeholder="Enter Phone No">
</div>
<div class="col-sm-1">
<label for="Age">Age:</label>
<input type="text" class="form-control" id="age" name="age[]" placeholder="Enter Age">
</div>
<div class="col-sm-3">
<label for="DateofBirth">Date of Birth:</label>
<input type="date" id="dob" name="date_of_birth[]" class="form-control"/>
</div>
</div><br/>
<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" name="submit" class="btn btn-info pull-left">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="row"><div class="col-sm-1"><label for="Age">Sl No:</label><input type="text" class="form-control sl" name="slno[]" value="'+i+'" readonly=""></div><div class="col-sm-3"><label for="Student Name">Student Name:</label><input type="text" class="form-control" name="student_name[]" id="st_name'+i+'" placeholder="Enter Student Name"></div><div class="col-sm-3"><label for="Phone">Phone No*:</label><input type="text" class="form-control" name="phone_no[]" id="pn'+i+'" placeholder="Enter Phone No"></div><div class="col-sm-1"><label for="Age">Age:</label><input type="text" class="form-control" id="age'+i+'" name="age[]" placeholder="Enter Age"></div><div class="col-sm-3"><label for="DateofBirth">Date of Birth:</label><input type="date" id="dob'+i+'" name="date_of_birth[]" class="form-control"/></div><input type="button" class="btnRemove btn-danger" value="Remove"/></div><br>');
});
// 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.
Also Read, How to insert ajax result in a text field
CONCLUSION:- I hope this article will help you to insert multiple rows into MySQL with PHP using foreach arrays.
