Create Update Delete database dengan PHP MySQL dan jQuery

Contents

1 Author Profile
- 1.1 Latest entries

Seperti yang admin janjikan pada tutorial sebelumya “jQuery DataTables with Custom Search Filter“, pada tutorial kali ini admin akan membuat tutorial mengenai CUD (Create Update Delete) dengan database MySQL.

Adapun yang harus di siapkan yaitu:

Tabel Database
Admin akan melanjutkan pada table database sebelumnya (tbl_employee) bisa di lihat pada tutorial “Menampilkan Database Server Side dengan jQuery Datatables“
Form Input
Seperti biasa, admin akan menggunakan bootstrap template AdminLTE
Library jQuery
Link library sudah tersedia pada tutorial “Menampilkan data dengan jQuery DataTables“
Es teh manis (untuk menemani supaya tidak dehidrasi)

Lanjutkan!

Kembali ke tutorial sebelumnya “jQuery DataTables with Custom Search Filter” tambahkan kolom Action pada tabel di akhir, seperti berikut.

Tambahkan juga link untuk tombol +Add kemudian arahkan link tersebut pada halaman form. misal datatables_form_cud.php

Sehingga untuk link edit data form bisa menggunakan datatables_form_cud.php?id=1 untuk parameter querynya.

Untuk menambahkan kolom Action diatas, berikut script penambahannya.

"columns": [
	{ data: 'emp_id', orderable: false }, 
	{ data: 'emp_name' }, 
	{ data: 'emp_position' },
	{ data: 'emp_office' },
	{ data: 'emp_age' },
	{ data: 'emp_startdate' },
	{ data: 'emp_salary' },
	{ data: 'control', className: "text-center", orderable: false }
]

Data control ini nanti akan di tambahkan juga pada data.php untuk menampilkan tombol Edit dan Delete

foreach($empQuery as $rows)
{
	$control = '<div class="btn-group">';
		$control .= '<a class="btn btn-primary btn-xs" title="Edit Data" href="datatables_form_cud?id='.$rows["emp_id"].'"><i class="fa fa-pencil"></i></a> ';
		
		$control .= '<button type="button" class="btn btn-danger btn-xs" title="Hapus Data" onClick="Delete('submit.php?del=', 'table_id', ''.$rows["emp_id"].''); return false;"><i class="fa fa-close"></i></button> ';
	$control .= '</div>';
	
	$data[] = array(
		"emp_id"		=> $i+1,
		"emp_name"		=> $rows['emp_name'],
		"emp_position"	=> $rows['emp_position'],
		"emp_office"	=> $rows['emp_office'],
		"emp_age"		=> $rows['emp_age'],
		"emp_startdate"	=> to_date($rows['emp_startdate'],'ymd-dmy'),
		"emp_salary"	=> rp_digit($rows['emp_salary']),
		"control"		=> $control
   );
   $i++;
}

onClick="Delete('submit.php?del=', 'table_id', ''.$rows["emp_id"].''); return false;"

function Delete(func, data, id)
{
	var confm = confirm("Hapus data ini?");
	
	if(confm)
	{
		$.ajax({
			url: func+""+id,
			type: "POST",
			cache: false,
			success: function(html){
				if($.trim(html) == 'OK')
				{
					if(data == 'REFRESH')
					{
						document.location.reload();
					}
					else{
						$("#"+data).DataTable().ajax.reload(null, false);
					}
					eModal.alert('<div class="alert alert-success" role="alert">Data berhasil di hapus!!</div>', 'Info');
				}
				else{
					eModal.alert('<div class="alert alert-danger" role="alert">'+$.trim(html)+'</div>', 'Warning');
				}
			},
			error:function(xhr, status, error){
				alert(error);
			}
		});
	}
}

Siapkan form html nya, kurang lebih seperti dibawah

<form id="runForm" method="POST" action="" class="form-horizontal">
	<div class="row">
	
		<div class="col-md-5">									
			<div class="box box-widget">
				<div class="box-header with-border">
					<div class="user-block">
						<h3 class="box-title">Data <?php echo ($id) ? $data[0]['emp_id'] : "";?></h3>
					</div>
				</div>
				<div class="box-body">
					
					<div class="form-group">
						<label class="col-sm-4 control-label box_labels">Name*</label>
						<div class="col-md-8"><input type="text" name="emp_name" class="form-control emp_name" value="<?php echo ($id) ? $data[0]['emp_name'] : "";?>"></div>	
					</div>
					
					<div class="form-group">
						<label class="col-sm-4 control-label box_labels">Position*</label>
						<div class="col-md-8"><input type="text" name="emp_position" class="form-control" value="<?php echo ($id) ? $data[0]['emp_position'] : "";?>"></div>	
					</div>
					
					<div class="form-group">
						<label class="col-sm-4 control-label box_labels">Office*</label>
						<div class="col-md-8"><input type="text" name="emp_office" class="form-control" value="<?php echo ($id) ? $data[0]['emp_office'] : "";?>"></div>	
					</div>
					
					<div class="form-group">
						<label class="col-sm-4 control-label box_labels">Age*</label>
						<div class="col-md-5"><input type="text" name="emp_age" class="form-control" value="<?php echo ($id) ? $data[0]['emp_age'] : "";?>"></div>	
					</div>
					
					<div class="form-group">
						<label class="col-sm-4 control-label box_labels">Salary*</label>
						<div class="col-md-5"><input type="text" name="emp_salary" class="form-control" value="<?php echo ($id) ? $data[0]['emp_salary'] : "";?>"></div>	
					</div>
					
					<div class="form-group">
						<label class="col-sm-4 control-label box_labels">Start Date*</label>
						<div class="col-md-5">
							<div class="input-group">
								<div class="input-group-addon"><i class="fa fa-calendar"></i></div>
								<input type="text" name="emp_startdate" class="form-control datepicker" value="<?php echo ($id) ? to_date($data[0]['emp_startdate'],'ymd-dmy') : date('d/m/Y');?>" autocomplete="off">
							</div>
						</div>
					</div>
						
				</div>
			</div>
		</div>
		
		<div class="col-md-3">
			<div class="box box-widget">
				<div class="box-header with-border">
					<div class="user-block">
						<span class="pull-right">
							<a href="<?php echo BASE_URL; ?>datatables_form" class="btn btn-default btn-sm"><i class="fa fa-reply"></i> Back</a>
							<input type="submit" class="btn btn-primary btn-sm save" value="Save" />
						</span>
					</div>
				</div>
			</div>
			
			<div style="display:nome;" id="loadAlert"></div>
		</div>
		
	</div>
	
	<input type="hidden" name="emp_id" value="<?php echo ($id) ? $data[0]['emp_id'] : "";?>">
</form>

Karna pengiriman parameter form ini menggunakan ajax jQuery, jadi saat tombol Save di klik, maka atribut ID=runForm pada form akan di kenali oleh function jquery dibawah.

$(function() {
		
   $(".emp_name").focus();
   $("#runForm").submit(function(e) {
		e.preventDefault();
		var form = $(this);
		$.ajax({
			url: "submit.php",
			type: "POST",
			dataType : 'json',
			data: form.serialize(),
			cache: false,
			beforeSend: function(){ $(".save").val('Saving...');},
			success: function(html){
				if($.trim(html.msg) == 'OK'){
					var xx = window.location.href = "datatables_form/";
					if(xx){
						$("#loadAlert").html('<div class="alert alert-success alert-dismissible"><button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>Data berhasil di simpan!!</div>');
					}
				}
				else{
					$(".save").val('Save');
					$(".emp_name").focus();
					$("#loadAlert").html('<div class="alert alert-warning alert-dismissible"><button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>'+$.trim(html.msg)+'</div>');
				}
			},
			error:function(xhr, status, error){
				$("#loadAlert").html('<div class="alert alert-danger alert-dismissible"><button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>'+error+'</div>');
			}
		});
		return false;
	});
});

Kemudian siapkan file submit.php untuk action post ketika save di click.

if(is_ajax())
{
	if(isset($_REQUEST['del'])){
		delForm($_REQUEST['del']);
	}
	else{
		$data	= array(
			'emp_name'		=> $_POST['emp_name'],
			'emp_position'	=> $_POST['emp_position'],
			'emp_office'	=> $_POST['emp_office'],
			'emp_age'		=> to_number($_POST['emp_age']),
			'emp_salary'	=> to_number($_POST['emp_salary']),
			'emp_startdate'	=> to_date($_POST['emp_startdate'],"dmy-ymd")
		);
		runForm($data, $_POST['emp_id']);
	}
}

fungsi is_ajax() untuk menecek http request

function is_ajax() 
{
	return isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest';
}

Setelah pengiriman data pada form, terakhir siapkan tangkapan untuk menampung data post submit.php yang akan di kirim ke database.

### runForm
function runForm($gData, $id) 
{
	global $db_mysql;
	
	$arr 	= array();
	
	if($gData['emp_name'] == "")
	{
		$arr['msg'] = 'Name is empty!';
	}
	else if($gData['emp_position'] == "")
	{
		$arr['msg'] = 'Position is empty!';
	}
	else if($gData['emp_office'] == "")
	{
		$arr['msg'] = 'Office is empty!';
	}
	else if($gData['emp_age'] == "")
	{
		$arr['msg'] = 'Age is empty!';
	}
	else if($gData['emp_salary'] == "")
	{
		$arr['msg'] = 'Salary is empty!';
	}
	else if($gData['emp_startdate'] == "")
	{
		$arr['msg'] = 'Start date is empty!';
	}
	else{
		if(empty($id)){
			$data = $db_mysql->insert("tbl_employee", $gData);
		}
		else{
			$data = $db_mysql->update("tbl_employee", $gData, "emp_id = '$id'");
		}
		
		if($data > 0)
		{			
			$arr['msg'] = 'OK';
		}
		else{
			$arr['msg'] = 'Failed!!';
		}
	}
	echo json_encode($arr);
}
function delForm($id = null)
{	
	global $db_mysql;
	
	if($id != null){
		$db_mysql->delete("tbl_employee", "emp_id = '$id'");
		echo 'OK';
	}
	else{
		echo "Error : delete data.";
	}
}

Hasil akhir yang di harapkan akan jadi seperti berikut:

Demikian, untuk kode lengkap bisa di cek pada Link download dibawah

Share to Unlock Content

Unlock this exclusive content by using one of the sharing buttons below.

Author Profile

Ricki K

Hi my name is Ricki, I am a blogger from Indonesia. Founder of erkamoo.com, Besides creating Web Applications, I also write about Blogging Tips and Tutorials on Programming, Databases, HTML.

Latest entries

Shortcodes22 April 2024Displays table details in Datatables
Shortcodes22 April 2024Capture image through webcam using WebcamJS
Shortcodes22 April 2024Add and Delete rows in the tabel Dynamically
JS & JQUERY18 April 2024Display datatables with json and groups based on certain columns

Pages

Categories

Create Update Delete database dengan PHP MySQL dan jQuery

Author Profile

Latest entries