Mengubah Trigger ke dalam file PHP

ant · Sep 26, 2012
Pagi mas Ellyx, saya mau tanya soal memindah file trigger ke file php bagaimana ya? ini file trigernya?
* Trigger structure for table `detail_pengadaan` */
 
DELIMITER $$
 
/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `t_insert_pengadaan` */$$
 
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `t_insert_pengadaan` AFTER INSERT ON `detail_pengadaan` FOR EACH ROW BEGIN
update barang set brg_stok = brg_stok + new.dada_qty WHERE brg_id = new.brg_id;
END */$$
 
 
DELIMITER ;
 
/* Trigger structure for table `detail_tau` */
 
DELIMITER $$
 
/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `t_insert_tau` */$$
 
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `t_insert_tau` AFTER INSERT ON `detail_tau` FOR EACH ROW BEGIN
update barang set brg_stok = brg_stok - new.dtau_qty WHERE brg_id = new.brg_id;
END */$$
 
 
DELIMITER ;
Mohon bantuannya, terimakasih.....
Silahkan login untuk menjawab!
0
Ellyx Christian · Sep 26, 2012 · 0 Suka · 0 Tidak Suka
bisa lihat kode untuk inset data ke tabel detail_pengadaan?
0
ant · Sep 27, 2012 · 0 Suka · 0 Tidak Suka
Selamat pagi mas Ellyx, Ini mas kodenya;
<?php
	ob_start();
	session_start();
	include "conn-db.php";
 
	function nota($tabel, $digit, $kolom, $pre) {
		$urut = @mysql_result(@mysql_query("SELECT mid(".$kolom.",-".$digit.")+1 FROM ".$tabel." order by ".$kolom." DESC limit 0,1"),0,0);
		$max  = $digit - strlen($urut);
		$no_nota = $pre;
		for ($i=1;$i<=$max;$i++) {
			//if($urut=="") {
				$no_nota .= "0";
			//}
		}
		$no_nota .= $urut;
		return $no_nota;
	}
	
	$nota = nota('pengadaan',5,'ada_nota','ADA');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link type="text/css" href="../css/form.css" rel="stylesheet" />
<script language="JavaScript" src="jquery.js"></script>
<script>
	
	function addTableRow(jQtable){
		jQtable.each(function(){
			var $table = $(this);
			var n = parseInt(document.getElementById('nomor').value) + 1;
			var brg = document.getElementById('barang').value;
			var qty = document.getElementById('qty').value;
			var harga_beli = document.getElementById('harga_beli').value;
			
			if ((qty<=0) || (harga_beli<=0)) {
				alert('QTY dan HARGA tidak boleh kosong atau minus');
			}
			else {
				var brgs= brg.split('-');
				var tds = '<tr>';
				tds += '<td align=center>'+brgs[1]+'<input type="hidden" name="brg['+n+']" value="'+brgs[0]+'" /></td>';
				tds += '<td>'+brgs[2]+'</td>';
				tds += '<td align=center>'+qty+'<input type="hidden" name="qty['+n+']" id="qty['+n+']" value="'+qty+'" /></td>';
				tds += '<td align=center>'+harga_beli+'<input type="hidden" name="harga_beli['+n+']" id="harga_beli['+n+']" value="'+harga_beli+'" /></td>';
				tds += '<td align=center class="delete" onClick="$(this).parent().remove(); minTotal('+qty+'); minHarga('+harga_beli+') "><a href="javascript:void(0)">Hapus</a></td>';
				tds += '</tr>';
				if($('tbody', this).length > 0){
					$('tbody', this).append(tds);
				}else {
					$(this).append(tds);
				}
				document.getElementById('nomor').value =  n;
			}
		});
	}
	function hitTotal() {
		var no = parseInt(document.getElementById('nomor').value);
		var tQty = parseInt(document.getElementById('total').innerHTML);
		var lastQty = parseInt(document.getElementById('qty['+no+']').value);
		tQty += lastQty;
		document.getElementById('total').innerHTML = tQty;
	}
	
	function hitHarga() {
		var no = parseInt(document.getElementById('nomor').value);
		var tHrg = parseInt(document.getElementById('total_harga').innerHTML);
		var lastHrg = parseInt(document.getElementById('harga_beli['+no+']').value);
		tHrg += lastHrg;
		document.getElementById('total_harga').innerHTML = tHrg;
	}
 
	function minTotal(qty) {
		var tQty = parseInt(document.getElementById('total').innerHTML);
		tQty -= parseInt(qty);
		document.getElementById('total').innerHTML = tQty;
	}
	
	function minHarga(harga_beli) {
		var tHrg = parseInt(document.getElementById('total_harga').innerHTML);
		tHrg -= parseInt(harga_beli);
		document.getElementById('total_harga').innerHTML = tHrg;
	}
	
	function deleteAllRows() {
		$('#myTable tbody').remove();
		document.getElementById('total').innerHTML = 0;
		document.getElementById('total_harga').innerHTML = 0;
	}
	
</script>
<style type="text/css">
<!--
.style1 {color: #FF0000}
-->
</style>
</head>
 
<body>
<form id="forms" method="POST" onSubmit="return submitForm('<?php echo $_SERVER['PHP_SELF'];?>')">
<table class="defaultText" width="50%"  border="0" cellpadding="5" cellspacing="1" >
	<?php
		if ($msg!='') {
			echo "
			<tr>
				<td> </td>
				<td> </td>
				<td> $msg </td>
			</tr>";
		}
	?>
	<tr>
        <td colspan="3" valign="top" nowrap="nowrap" class="left_titlebar style1">Tanda * harus diisi !</td>
    </tr>
	<tr>
		<td> No. Transaksi </td>
		<td> : </td>
		<td> <input type='text' class="yellowBGField" name='ada_nota' value='<?php echo "$nota";?>' readonly="da_nota"> </td>
	</tr>
	<tr>
		<td> Tanggal </td>
		<td> : </td>
		<td> <input type='text' class="yellowBGField" name='ada_tgl' value='<?php echo date('Y-m-d');?>'> </td>
	</tr>
	<tr>
		<td> Supplyer <span class="style1">*</span></td>
		<td> : </td>
	  <td> 
		  <select name='sup_id' class='inputbox'><option value=''></option>
			  <?php
					$sup = mysql_query("SELECT * FROM supplyer");
					while ($dsup = mysql_fetch_array($sup)) {
						echo "<option value='".$dsup[0]."'>".
								$dsup[1]." | ".$dsup[2].
							 "</option>";
					}
				?>
		  </select>
	  </td>
	</tr>
		<tr>
		<td> Faktur Pajak </td>
		<td> : </td>
		<td> <input type='text' class="whiteBGField" name='faktur_pajak' > </td>
	</tr>
	<tr>
		<td> No Invoice </td>
		<td> : </td>
		<td> <input type='text' class="whiteBGField" name='no_invoice' > </td>
	</tr>
	<tr>
		<td> No Kwitansi </td>
		<td> : </td>
		<td> <input type='text' class="whiteBGField" name='no_kwitansi' > </td>
	</tr>
	<tr>
		<td valign="top"> Note</td>
		<td valign="top"> : </td>
		<td> <textarea name="keterangan" class="whiteBGField" style="width:100%"></textarea> </td>
	</tr>
		<tr>
		<td> Nama Barang </td>
		<td> : </td>
		<td>
			<select name='barang' id="barang" class='inputbox'><option value=''>[ Pilih Barang ]</option>
				<?php
					$q = mysql_query("SELECT * FROM barang");
					while($d = mysql_fetch_array($q)) {
						echo "<option value='".$d[0]."-".$d[1]."-".$d[2]."'>".$d[1]." | ".$d[2]."</option>";
					}
				?>
				
			</select> 
			QTY <input type='text' class="yellowBGField" name='qty' id='qty' size='2' > 
			Harga Total <input type='text' class="yellowBGField" name='harga_beli' id='harga_beli' size='10' > 
			<input type="button" name="tambah" value=" Tambahkan " id="tambah" onClick="addTableRow($('#myTable')); hitTotal();hitHarga();" />
			<input type='hidden' name='nomor' id='nomor' value='0' >
		</td>
	</tr>
		<tr>
		<td></td>
		<td></td>
		<td>
			<table width="100%" border="1" style="border-collapse:collapse" id="myTable">
				<thead>
				<tr align="center" bgcolor="#009999">
					<td>Kode</td>
					<td>Nama</td>
					<td>Qty</td>
					<td>Harga</td>
					<td>Act</td>
				</tr>
				</thead>
				<tfoot>
				<tr align="center">
					<td colspan="2" align="right">TOTAL</td>
					<td id="total">0</td>
					<td id="total_harga">0</td>
				</tr>
				</tfoot>
			</table>
		</td>
	</tr>
 
	<tr>
		<td> </td>
		<td> </td>
		<td align="right"> 
			<input type='Submit' name='simpan' value=' Simpan '/>  
			<input type='Reset' name='reset' value=' Reset ' onClick='deleteAllRows()' /> 
		</td>
	</tr>
	<tr style="visibility:hidden;" >
		<td> Staff</td>
		<td> : </td>
		<td> <input type='text' name='staff' readonly="staff" value="<?php echo '' . $_SESSION['user_id'] . '';?>" > </td>
	</tr>
	<tr style="visibility:hidden;">
		<td> Jam </td>
		<td> : </td>
		<td> <input type='text' name='jam' readonly="jam" value="<?php include('../sales/jam.php'); ?>" > </td>
	</tr>
</table>
</form>
 
<?php
	//if(isset($_POST['simpan'])) {
	
	if($_POST){
		$nota = $_POST['ada_nota'];
		$tgl  = $_POST['ada_tgl'];
		$sup  = $_POST['sup_id'];
		$faktur = $_POST['faktur_pajak'];
		$kwitansi = $_POST['no_kwitansi'];
		$invoice = $_POST['no_invoice'];
		$note = $_POST['keterangan'];
		$staff = $_POST['staff'];
		$jam = $_POST['jam'];
		if ($sup!='') {
		 $q1= "insert into pengadaan set ada_nota='$nota',ada_tgl='$tgl', sup_id='$sup', faktur_pajak='$faktur',no_invoice='$invoice',no_kwitansi ='$kwitansi', keterangan='$note',staff='$staff',jam='$jam'";
 
			$r1 = mysql_query($q1) or die ($q1);
			if($r1) {
			$msg = "SIMPAN DATA, SUKSES..!!!";
		}
		else {
			$msg = "Ada kesalahan";
		}
	}
  
			if($r1) {
				$adaID = mysql_result(mysql_query("SELECT ada_id FROM pengadaan WHERE ada_nota = '".$nota."'"),0,0);
				if($_POST['brg']!='') {
					$brg = $_POST['brg']; 
					$hrg = $_POST['harga_beli'];
					foreach ($_POST['qty'] as $key => $val) {
						$q2 = "INSERT INTO detail_pengadaan VALUES (null,'".$val."','".$hrg[$key]."','".$brg[$key]."', '".$adaID."')";
						$r2 = mysql_query($q2);
					}
				}
			}
			header("location:pengadaan.php");
	}
?>
</body>
</html>

Mohon bantuanya mas Ellyx, terimakasih.....
0
Ellyx Christian · Sep 27, 2012 · 0 Suka · 0 Tidak Suka
saya akan kasi contoh untuk trigger pada insert detail_pengadaan
Quote:
/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `t_insert_pengadaan` AFTER INSERT ON `detail_pengadaan` FOR EACH ROW BEGIN
update barang set brg_stok = brg_stok + new.dada_qty WHERE brg_id = new.brg_id;
END */$$
dimana setiap proses insert data ke detail_pengadaan akan dilakukan proses update tabel barang.
Maka ubah kode php pada baris ke-257 menjadi:
foreach ($_POST['qty'] as $key => $val) {
	$q2 = "INSERT INTO detail_pengadaan VALUES (null,'".$val."','".$hrg[$key]."','".$brg[$key]."', '".$adaID."')";
	$r2 = mysql_query($q2);
	//update data barang
	$sql = "UPDATE barang set brg_stok = brg_stok + ".intval($val)." WHERE brg_id='".$brg[$key]."'";
	mysql_query($sql);
}
0
ant · Sep 27, 2012 · 0 Suka · 0 Tidak Suka
Oiya mas, maksudnya ini apa ya?
$sql = "UPDATE barang set brg_stok = brg_stok + ".intval($val)." WHERE brg_id='".$brg[$key]."'";

kok brg_id = '".$brg[$key]."'"; ya mas? bukan id nya?
0
ant · Sep 27, 2012 · 0 Suka · 0 Tidak Suka
untuk yang pengurangan kok gak bisa ya mas? mohon bantuannya:
    foreach ($_POST['qty'] as $key => $val) {
    $q2 = "INSERT INTO detail_tau VALUES (null,'".$val."','".$hrg[$key]."','".$brg[$key]."', '".$adaID."')";
    $r2 = mysql_query($q2);
    //update data barang
    $sql = "UPDATE barang set brg_stok = brg_stok - ".intval($val)." WHERE brg_id='".$brg[$key]."'";
    mysql_query($sql);
    }