Add More rows into the existing table with PHP & JQuery

Demo Online

4 years ago
Zaid Bin Khalid
45871 Views
29

This is a very common task to add more rows into the existing table with the help of JQuery. In this tutorial, I will use AJAX that creates table rows into the existing table and save it into the DB.

Create three files index.php, config.php, and action-form.ajax.php. Add config.php file in both other files at the top.

To save user’s data you need to create a table into the database (MySQL). Use the below query to create a table into DB with some records.

CREATE TABLE  `reorderusers` (
  `id` int(11) NOT NULL,
  `username` varchar(64) NOT NULL,
  `useremail` varchar(128) NOT NULL,
  `userphone` varchar(24) NOT NULL,
  `usercountry` int(11) NOT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `reorderusers`
--
 
ALTER TABLE `reorderusers`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `reorderusers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;


INSERT INTO `reorderusers` (`id`, `username`, `useremail`, `userphone`, `usercountry`, `dt`) VALUES
(1, 'Jhon', '[email protected]', '0808254552', 23, '2019-10-26 14:47:31'),
(2, 'Aslam', '[email protected]', '5400897855', 20, '2019-10-26 14:47:31'),
(3, 'Kartik', '[email protected]', '1555420004', 50, '2019-10-26 14:47:31'),
(4, 'Jordan', '[email protected]', '8975421200', 100, '2019-10-26 14:47:31');

You need to create a connection with the database. Create a file with the name of the config.php and past below code init.

<?php
error_reporting(0);
define('DB_NAME', 'YOUR_DB_NAME'); //Your DB Name
define('DB_USER', 'root'); //Your DB User Name
define('DB_PASSWORD', ''); //Your DB Password
define('DB_HOST', 'localhost'); //Your Host Name
  
// Create connection
$db     =   new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

Add more rows into the existing table with the help of ajax. To add more rows consider the below snippet. That sends an AJAX request where you can create an HTML structure and append it into the table. There is another build-in function in JQuery clone() which just create a clone of data.

$(document).ready(function(e) {
	$("#addmore").on("click",function(){
		$.ajax({
			type:'POST',
			url:'action-form.ajax.php',
			data:{'action':'addDataRow'},
			success: function(data){
				$('#tb').append(data);
			}
		});
	});
});

Create a file with the name of action-form.ajax.php that handles all ajax actions. Paste below code in it which is used to create a new row when you click on add more button. And also save more rows data into the DB.

<?php include_once('config.php');
if(isset($_REQUEST['action']) and $_REQUEST['action']=="addDataRow"){
	?>
	<tr>
		<td align="center" class="text-danger"><button type="button" data-toggle="tooltip" data-placement="right" title="Click To Remove" onclick="if(confirm('Are you sure to remove?')){$(this).closest('tr').remove();}" class="btn btn-danger"><i class="fa fa-fw fa-trash-alt"></i></button></td>
		<td align="center"><?php echo date('Y-m-d H:i:s');?></td>
		<td><input type="text" name="username[]" class="form-control" required="required"></td>
		<td>
		<select name="usercountry[]" id="usercountry" class="form-control selectpicker" data-live-search="true" data-size="10" required="required">
			<option value="">Select</option>
			<?php
				$result	=	$db->query("SELECT * FROM countries WHERE 1 ORDER BY countryName ASC ");
				while($val  =   $result->fetch_assoc()){
				?>
				<option value="<?php echo $val['id']?>" data-subtext="(<?php echo $val['continentName']?>)"><?php echo mb_strtoupper($val['countryName'],'UTF-8')?></option>
			<?php }?>
		</select>
		</td>
		<td><input type="email" name="useremail[]" class="form-control" required="required"></td>
		<td><input type="text" name="userphone[]" class="form-control" required="required"></td>
	</tr>
	<?php
	echo '|***|addmore';
}

//Submit data or extra rows
if(isset($_REQUEST['action']) and $_REQUEST['action']=="saveAddMore"){
	extract($_REQUEST);
	
	foreach($username as $key=>$un){
		$db->query('INSERT INTO reorderusers (username, useremail, userphone, usercountry) VALUES ("'.$un.'", "'.$useremail[$key].'", "'.$userphone[$key].'", "'.$usercountry[$key].'") ');
	}
	echo '<div class="alert alert-success"><i class="fa fa-fw fa-thumbs-up"></i> Record added successfully!</div>|***|add';
}

How to Fetch uses data into the table.

Add config.php file into index.php file and paste below code just after the body tag.

<div id="msg"></div>
<form id="form" method="post" onSubmit="return false;">
	<input type="hidden" name="action" value="saveAddMore">
	<table class="table table-bordered table-striped" id="sortable">
		<thead>
			<tr>
				<th width="20">Sr#</th>
				<th width="120" class="text-center">Insetion Date</th>
				<th>User Name</th>
				<th width="250">User Country</th>
				<th>User Email</th>
				<th>User Phone#</th>
			</tr>
		</thead>
		<tbody id="tb">
			<?php 
			$result	=	$db->query("SELECT * FROM reorderusers WHERE userorder IN (1,2,3) ORDER BY userorder ASC ");
			if($result->num_rows>0){
				$s	=	'';
				while($val  =   $result->fetch_assoc()){
					$s++;  ?>
				<tr>
					<td align="center"><?php echo $s;?></td>
					<td align="center"><?php echo date('',strtotime($val['dt'])); ?></td>
					<td><?php echo mb_strtoupper($val['username'],'UTF-8'); ?></td>
					<td><?php echo mb_strtoupper($counrtyName[$val['usercountry']],'UTF-8'); ?></td>
					<td><?php echo $val['useremail']; ?></td>
					<td><?php echo $val['userphone']; ?></td>
				</tr>
				<?php
				}
			}else{ ?>
			<tr>
				<td colspan="6" class="bg-light text-center"><strong>No Record(s) Found!</strong></td>
			</tr>
			<?php } ?>
		</tbody>
		<tfoot>
			<tr>
				<td colspan="6">
					<a href="javascript:;" class="btn btn-danger" id="addmore"><i class="fa fa-fw fa-plus-circle"></i> Add More</a>
					<button type="submit" name="save" id="save" value="save" class="btn btn-primary" hidden><i class="fa fa-fw fa-save"></i> Save</button>
				</td>
			</tr>
		</tfoot>
	</table>
</form>

After creating rows and fill the data click on the SAVE button to submit data into the DB. To send ajax request paste below code after adding more rows script.

$("#form").on("submit",function(){
	$.ajax({
		type:'POST',
		url:'action-form.ajax.php',
		data:$(this).serialize(),
		success: function(data){
			var a	=	data.split('|***|');
			if(a[1]=="add"){
				$('#mag').html(a[0]);
				setTimeout(function(){location.reload();},1500);
			}
		}
	});
});

The complete code of index.php will look like below.

<div class="container">
	<h1><a href="https://learncodeweb.com/jquery/add-more-&-reorder-table-rows-with-php-jquery/">Add More rows into the existing table with PHP & JQuery</a></h1>
	<hr>
	<div class="clearfix"></div>
	<?php
        //Get all countries names array
	$result	=	$db->query("SELECT * FROM countries WHERE 1 ORDER BY countryName ASC ");
	while($val  =   $result->fetch_assoc()){
		$counrtyName[$val['id']]	=	$val['countryName'];
	}
	?>
	<script>
	$(document).ready(function(e) {
		$('.selectpicker').selectpicker();
		
		$('body').on('mousemove',function(){
			$('[data-toggle="tooltip"]').tooltip();
		});
		
		$("#addmore").on("click",function(){
			$.ajax({
				type:'POST',
				url:'action-form.ajax.php',
				data:{'action':'addDataRow'},
				success: function(data){
					$('#tb').append(data);
					$('.selectpicker').selectpicker('refresh');
					$('#save').removeAttr('hidden',true);
				}
			});
		});
		
		$("#form").on("submit",function(){
			$.ajax({
				type:'POST',
				url:'action-form.ajax.php',
				data:$(this).serialize(),
				success: function(data){
					var a	=	data.split('|***|');
					if(a[1]=="add"){
						$('#mag').html(a[0]);
						setTimeout(function(){location.reload();},1500);
					}
				}
			});
		});
		
	});
	</script>
	<div id="msg"></div>
	<form id="form" method="post" onSubmit="return false;">
		<input type="hidden" name="action" value="saveAddMore">
		<table class="table table-bordered table-striped" id="sortable">
			<thead>
				<tr>
					<th width="20">Sr#</th>
					<th width="120" class="text-center">Insetion Date</th>
					<th>User Name</th>
					<th width="250">User Country</th>
					<th>User Email</th>
					<th>User Phone#</th>
				</tr>
			</thead>
			<tbody id="tb">
				<?php 
				$result	=	$db->query("SELECT * FROM reorderusers WHERE userorder IN (1,2,3) ORDER BY userorder ASC ");
				if($result->num_rows>0){
					$s	=	'';
					while($val  =   $result->fetch_assoc()){
						$s++;  ?>
					<tr>
						<td align="center"><?php echo $s;?></td>
						<td align="center"><?php echo date('',strtotime($val['dt'])); ?></td>
						<td><?php echo mb_strtoupper($val['username'],'UTF-8'); ?></td>
						<td><?php echo mb_strtoupper($counrtyName[$val['usercountry']],'UTF-8'); ?></td>
						<td><?php echo $val['useremail']; ?></td>
						<td><?php echo $val['userphone']; ?></td>
					</tr>
					<?php
					}
				}else{ ?>
				<tr>
					<td colspan="6" class="bg-light text-center"><strong>No Record(s) Found!</strong></td>
				</tr>
				<?php } ?>
			</tbody>
			<tfoot>
				<tr>
					<td colspan="6">
						<a href="javascript:;" class="btn btn-danger" id="addmore"><i class="fa fa-fw fa-plus-circle"></i> Add More</a>
						<button type="submit" name="save" id="save" value="save" class="btn btn-primary" hidden><i class="fa fa-fw fa-save"></i> Save</button>
					</td>
				</tr>
			</tfoot>
		</table>
	</form>
	<div class="clearfix"></div>
	
</div> <!--/.container-->

This example depends on the jQuery, and selectpicker bootstrap 4. So add the following lines into your head tag.

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap-select.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap-select.min.js"></script>

Demo Online Download Code

4 years ago
Zaid Bin Khalid
45871 Views
29

Prev Next

Related Linkes

Add More rows into the existing table with PHP & JQuery