In this chapter we will see how to add form input dynamically using jQuery and insert data in the database using PHP.
First thing we need is a database connection using MySQLi. I am making a dbconnect.php file to connect to the database.
dbconnect.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dynamic_field";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
error_reporting(0);
Then we need two database tables for inserting data in my MYSQL database.
- tbl_product
- tbl_student
Use the following code to create a database & insert some dummy data is a MySQL data you have to import in your database.
data.sql
-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jul 15, 2020 at 09:01 AM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `dynamic_field`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_product`
--
CREATE TABLE `tbl_product` (
`id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`item` varchar(100) NOT NULL,
`quantity` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbl_product`
--
INSERT INTO `tbl_product` (`id`, `student_id`, `item`, `quantity`) VALUES
(43, 10, 'computer', '2'),
(44, 10, 'Mouse', '3'),
(47, 9, 'computer', '3'),
(52, 11, 'blender spide', '2'),
(53, 11, 'Sctodh', '3');
-- --------------------------------------------------------
--
-- Table structure for table `tbl_student`
--
CREATE TABLE `tbl_student` (
`student_id` int(11) NOT NULL,
`student_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbl_student`
--
INSERT INTO `tbl_student` (`student_id`, `student_name`) VALUES
(9, 'Saroj2'),
(10, 'Manoj'),
(11, 'Rama');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_product`
--
ALTER TABLE `tbl_product`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `tbl_student`
--
ALTER TABLE `tbl_student`
ADD PRIMARY KEY (`student_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_product`
--
ALTER TABLE `tbl_product`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=54;
--
-- AUTO_INCREMENT for table `tbl_student`
--
ALTER TABLE `tbl_student`
MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;Last thing, create index.PHP file & put the following code to add form rows dynamically and insert data in database.
index.php
<?php include_once("dbconnect.php"); ?>
<head>
<title>Add form row</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<?php
if (isset($_POST['submit'])) {
if ($_GET['cid'] != "") {
$query = "UPDATE tbl_student set student_name='" . $_POST['student_name'] . "' where student_id='" . $_GET['cid'] . "'";
mysqli_query($conn, $query);
// delete the product
$qys = "DELETE FROM `tbl_product` where student_id='" . $_GET['cid'] . "'";
mysqli_query($conn, $qys);
//Update product
foreach ($_POST['item'] as $key => $value) {
# code...
$query1 = "INSERT INTO tbl_product(student_id,item,quantity)VALUES ('" . $_GET['cid'] . "','" . $_POST['item'][$key] . "','" . $_POST['quantity'][$key] . "')";
$result1 = mysqli_query($conn, $query1);
}
} else {
$query = "INSERT INTO tbl_student(student_name)VALUES ('" . $_POST['student_name'] . "')";
$result = mysqli_query($conn, $query);
$student_id = $conn->insert_id;
//Update product
foreach ($_POST['item'] as $key => $value) {
# code...
$query1 = "INSERT INTO tbl_product(student_id,item,quantity)VALUES ('" . $student_id . "','" . $_POST['item'][$key] . "','" . $_POST['quantity'][$key] . "')";
$result1 = mysqli_query($conn, $query1);
}
}
}
?>
<div class="container">
<h2>Dynamic add rows in Jqury with Add, Edit in PHP and MySQLi</h2>
<form action="" method="post" enctype="">
<div class="row">
<div style="float: right;"><a href='index.php' class="btn btn-primary">Add New </a></div>
<div class="col-sm-4">
<?php
if ($_GET['cid'] != "") {
$stu = "SELECT * FROM tbl_student WHERE student_id='" . $_GET['cid'] . "'";
$result_stu = mysqli_query($conn, $stu);
if ($result_stu->num_rows > 0) {
$i = 1;
while ($row_stu = $result_stu->fetch_assoc()) {
$name = $row_stu['student_name'];
}
}
}
?>
<div class="form-group"><level>Name</level>
<input type="text" name="student_name" value="<?php
if (isset($name)) {
echo $name;
}
?>" class="form-control">
</div>
</div>
</div>
<div class="row">
<div class="col-sm-4">
<div class="form-group">
<label>Add Products</label>
<table class="table table-bordered" id="dynamic_field">
<?php
if ($_GET['cid'] != "") {
$product = "SELECT * FROM tbl_product WHERE student_id='" . $_GET['cid'] . "'";
$result_product = mysqli_query($conn, $product);
if ($result_product->num_rows > 0) {
$i = 1;
while ($row_product = $result_product->fetch_assoc()) {
?>
<tr id="row<?php echo $i - 1; ?>">
<td><input type="text" name="item[]" placeholder="Item" value="<?php echo $row_product['item']; ?>" class="form-control name_list" /></td>
<td><input type="text" name="quantity[]" value="<?php echo $row_product['quantity']; ?>" placeholder="Quantity" class="form-control name_list" /></td>
<?php if ($i == 1) { ?> <td><button type="button" name="add" id="add" class="btn btn-success"><i class=" fa fa-plus-square"></i></button></td>
<?php } else { ?>
<td> <button type="button" name="remove" id="<?php echo $i - 1; ?>" class="btn btn-danger btn_remove"><i class="fa fa-trash"></i></button></td>
<?php } ?>
</tr>
<?php
$i++;
}
}
} else {
?>
<tr>
<td><input type="text" name="item[]" placeholder="Item" value="" class="form-control name_list" /></td>
<td><input type="text" name="quantity[]" value="" placeholder="Quantity" class="form-control name_list" /></td>
<td><button type="button" name="add" id="add" class="btn btn-success"><i class="fa fa-plus"></i></button></td>
</tr>
<?php } ?>
</table>
</div>
</div>
</div>
<?php if ($_GET['cid'] != "") { ?>
<!-- Save button div starts -->
<button type="submit" id='submit' name="submit" class="btn btn-primary" value="Save">Update</button>
<?php } else {
?>
<button type="submit" id='submit' name="submit" class="btn btn-primary" value="Save">Save</button>
<?php }
?>
</form>
<hr/>
<table class="table">
<thead>
<tr>
<th>SL No</th>
<th>Name</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
$users = " SELECT * FROM tbl_student";
$result = mysqli_query($conn, $users);
if ($result->num_rows > 0) {
$i = 1;
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $i++; ?></td>
<td><?php echo $row['student_name']; ?> </td>
<td> <a href='index.php?cid=<?php echo $row['student_id']; ?>'>Edit </a></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</div>
<script>
$(document).ready(function () {
var i = 1;
$('#add').click(function () {
i++;
$('#dynamic_field').append('<tr id="row' + i + '"><td><input type="text" name="item[]" placeholder="Item" class="form-control name_list" /></td><td><input type="text" name="quantity[]" placeholder="Quantity" class="form-control name_list" /></td><td><button type="button" name="remove" id="' + i + '" class="btn btn-danger btn_remove"><i class="fa fa fa-trash"></i></button></td></tr>');
});
$(document).on('click', '.btn_remove', function () {
var button_id = $(this).attr("id");
// alert(button_id);
$('#row' + button_id + '').remove();
});
});
</script>output:
Thanks, May this example help you.