Memanipulasi Data Menggunakan PHP MYSQL

5 December 2009 at 2:58 PM (Bahasa Pemrograman, PHP)


Istilah memanipulasi hampir sering kita dengar dimanapun dan kapanpun.! istilah manipulasi itu berarti menambah, merubah, dan menghapus data yang akan  disimpan dalam database untuk keperluan pribadi maupun kepentingan umum..!

Agar program ini bisa dijalankan, ada beberapa hal yang mesti dilakukan terlebih dahulu, Yaitu :

1. Instal Macromedia Dreamweaver sebagai sarana untuk menulis Script PHP

2. Instal Wamp Server sebagai Server Offline

3. Instal HeidiSQL sebagai Database.! HeidiSQL ini tidak wajib diinstal, karena di Wamp Server sendiri sudah ada program SQL yang bisa digunakan untuk membuat database.! keuntungan memakai HeidiSQL adalah adanya gambar Visual yang digunakan untuk membuat database, sehingga memudahkan User untuk membuat database apalagi untuk yang masih awam menggunakan SQL.!

Berikut ini adalah contoh program memanipulasi database dengan menggunakan PHP MYSQL..!

Program Penambahan Data

A. Script Input Untuk Menambahkan Data

<form method=”post” action=”tugas_add2.php”>

<div align=”center”>

<table width=”400″ border=”1″ align=”center”>

<tr>

<td width=”120″><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>NIP</font></td>

<td width=”264″><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<input type=”text” size=”18″ maxlength=”5″>

</font></td>

</tr>

<tr>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>Nama</font></td>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<input type=”text” size=”18″ maxlength=”20″>

</font></td>

</tr>

<tr>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>Kota</font></td>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<input type=”text” size=”18″ maxlength=”10″>

</font></td>

</tr>

<tr>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>Tanggal Lahir </font></td>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<select id=”tgl”>

<option>tgl</option>

<option>1</option>

<option>2</option>

<option>3</option>

<option>4</option>

<option>5</option>

<option>6</option>

<option>7</option>

<option>8</option>

<option>9</option>

<option>10</option>

<option>11</option>

<option>12</option>

<option>13</option>

<option>14</option>

<option>15</option>

<option>16</option>

<option>17</option>

<option>18</option>

<option>19</option>

<option>20</option>

<option>21</option>

<option>22</option>

<option>23</option>

<option>24</option>

<option>25</option>

<option>26</option>

<option>27</option>

<option>28</option>

<option>29</option>

<option>30</option>

<option>31</option>

</select>

<select id=”bln”>

<option selected>bulan</option>

<option>Januari</option>

<option>Febuari</option>

<option>Maret</option>

<option>April</option>

<option>Mei</option>

<option>Juni</option>

<option>Juli</option>

<option>Agustus</option>

<option>September</option>

<option>Oktober</option>

<option>November</option>

<option>Desember</option>

</select>

<select id=”thn”>

<option selected>tahun

<?php

for($i = 1945; $i <= 2009; $i++){

echo “<option selected>$i”;

}

?>

</option>

</select>

</font></td>

</tr>

<tr>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>Jenis Kelamin </font></td>

<td><p>

<font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<label></label>

<select name=”sex”>

<option selected>-</option>

<option>Pria</option>

<option>Wanita</option>

</select>

<br>

</font></p></td>

</tr>

<tr>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>Gaji</font></td>

<td><select name=”gj”>

<option selected>Gaji Pokok</option>

<option>Rp 1.500.000</option>

<option>Rp 2.000.000</option>

<option>Rp 2.500.000</option>

<option>Rp 3.000.000</option>

<option>Rp 3.500.000</option>

<option>Rp 4.000.000</option>

<option>Rp 4.500.000</option>

<option>Rp 5.000.000</option>

</select>

</td>

</tr>

<tr>

<td><font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>Bagian</font></td>

<td><select id=”bg”>

<option selected>Bagian</option>

<option>Akutansi</option>

<option>PDE</option>

<option>Pemasaran</option>

<option>Produksi</option>

<option>SDM</option>

</select>              </td>

</tr>

<tr>

<td colspan=”2″><div align=”right”>

<font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<input type=”submit” value=”Simpan”>

<input type=”reset” value=”Kosongkan”>

</font></div></td>

</tr>

</table>

<font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”><br>

</font></div>

<font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”><label></label>

</font>

</form>

B. Script Proses Penambahan Data

<?php

$nip = $_REQUEST[“nip”];

$nm = $_REQUEST[“nm”];

$kt = $_REQUEST[“kt”];

$tgl = $_REQUEST[“tgl”];

$bln = $_REQUEST[“bln”];

$thn = $_REQUEST[“thn”];

$sex = $_REQUEST[“sex”];

$gj = $_REQUEST[“gj”];

$bg = $_REQUEST[“bg”];

$conn = mysql_connect(“localhost”,”root”,””);

if (($nip == “”) and ($nm == “”) and ($kt == “”) and ($tgl == “tgl”) and ($bln == “bulan”) and ($thn ==”tahun”) and ($sex ==”-“) and ($gj == “Gaji Pokok”) and ($bg == “Bagian”)){

echo “Akses Error.! Mohon isi semua FIELD”;

echo “<br>”;

echo ”     <a href=’signUp.php’>Back</a>”;

}elseif ($nip == “”){

echo “Mohon Masukkan NIP Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($nm == “”){

echo “Mohon Masukkan Nama Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($kt == “”){

echo “Mohon Masukkan Kota Asal Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($tgl == “tgl”){

echo “Mohon Masukkan Tanggal Lahir Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($bln == “bulan”){

echo “Mohon Masukkan Bulan Lahir Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($thn ==”tahun”){

echo “Mohon Masukkan Tahun Lahir Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($sex ==”-“){

echo “Mohon Masukkan Jenis Kelamin Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($gj == “Gaji Pokok”){

echo “Mohon Masukkan Gaji Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($bg == “bagian”){

echo “Mohon Masukkan Posisi Bagian Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_add.php’>Back</a>”;

}elseif ($conn) {

mysql_select_db(“tugas”);

echo “<strong>NIP Pegawai</strong>”;

echo “<br>”;

echo “$nip”;

echo “<br>”;

echo “<br>”;

echo “<strong>Nama Pegawai </strong>”;

echo “<br>”;

echo “$nm”;

echo “<br>”;

echo “<br>”;

echo “<strong>Bagian </strong>”;

echo “<br>”;

echo “$bg”;

echo “<br>”;

echo “<br>”;

echo “============================================”;

echo “<br>”;

echo “<br>”;

$sqlstr = “insert into infopribadi (nip,nama,tgl,bln,thn,kota,sex,gaji,bagian) values (‘$nip’,’$nm’,’$tgl’,’$bln’,’$thn’,’$kt’,’$sex’,’$gj’,’$bg’)”;

$hasil = mysql_query($sqlstr,$conn);

echo “Data telah tersimpan”;

echo “<br>”;

echo “<br>”;

echo “<a href=’tugas_add.php’>Tambah </a>Data Lagi.?”;

echo “<br>”;

echo ” click <a href=’tugas_liat.php’>disini</a> untuk Lihat Daftar Pegawai”;

mysql_close($conn);

}else {

echo “Server Not Connected”;

}

?>

Program Pengeditan Data / Perubahan Data

A. Script Untuk Menginput Data melalui NIP sebagai Primary Key

<DIV class = “scroll style 13 style 1>

<div align=”left”>

<?php

$conn = mysql_connect(“localhost”,”root”,””);

if ($conn) {

mysql_select_db(“tugas”);

$sqlstr = “select * from infopribadi order by nip asc”;

$hasil = mysql_query($sqlstr,$conn);

$row = mysql_fetch_row($hasil);

if($row) {

do {

list($nip,$nm,$tgl,$bln,$thn,$kt,$sex,$gj,$bg) = $row;

echo “<strong>NIP Pegawai</strong>”;

echo “<br>”;

echo “$nip”;

echo “<br>”;

echo “<strong>Nama Pegawai </strong>”;

echo “<br>”;

echo “$nm”;

echo “<br>”;

echo “<strong>Tanggal Lahir </strong>”;

echo “<br>”;

echo “$tgl  $bln  $thn”;

echo “<br>”;

echo “<strong>Kota </strong>”;

echo “<br>”;

echo “$kt”;

echo “<br>”;

echo “<strong>Sex </strong>”;

echo “<br>”;

echo “$sex”;

echo “<br>”;

echo “<strong>Gaji </strong>”;

echo “<br>”;

echo “$gj”;

echo “<br>”;

echo “<strong>Bagian </strong>”;

echo “<br>”;

echo “$bg”;

echo “<br>”;

echo “===================================”;

echo “<br>”;

echo “<br>”;

}while ($row = mysql_fetch_row($hasil));

}else{

echo “Data Masih Kosong!!”;

echo”<br>”;

}

mysql_close($conn);

}else{

echo “Server Not Connected..!!”;

}

?>

</div>

</DIV>

<p class=”style1″>&nbsp;</p>

<p align=”center”>Mengubah Data Pegawai </p>

<form action=”tugas_update2.php” method=”post”>

<div align=”center”>

<table width=”400″ border=”1″ align=”center”>

<tr>

<td width=”108″><span class=”style6″>NIP</span></td>

<td width=”276″>

<input name=”nip” size=”18″ maxlength=”5″>                <span>Masukkan NIP yang akan diedit          </span></td>

</tr>

<tr>

<td><span class=”style6″>Nama</span></td>

<td>

<input name=”nm” size=”18″ maxlength=”20″>

<span class=”style4″>*</span> </td>

</tr>

<tr>

<td><span class=”style6″>Kota</span></td>

<td>

<input name=”kt” size=”18″ maxlength=”10″>

<span class=”style4″>*</span> </td>

</tr>

<tr>

<td><span class=”style6″>Tanggal Lahir </span></td>

<td>

<select name=”tgl”>

<option>tgl</option>

<option>1</option>

<option>2</option>

<option>3</option>

<option>4</option>

<option>5</option>

<option>6</option>

<option>7</option>

<option>8</option>

<option>9</option>

<option>10</option>

<option>11</option>

<option>12</option>

<option>13</option>

<option>14</option>

<option>15</option>

<option>16</option>

<option>17</option>

<option>18</option>

<option>19</option>

<option>20</option>

<option>21</option>

<option>22</option>

<option>23</option>

<option>24</option>

<option>25</option>

<option>26</option>

<option>27</option>

<option>28</option>

<option>29</option>

<option>30</option>

<option>31</option>

</select>

<select name=”bln”>

<option selected>bulan</option>

<option>Januari</option>

<option>Febuari</option>

<option>Maret</option>

<option>April</option>

<option>Mei</option>

<option>Juni</option>

<option>Juli</option>

<option>Agustus</option>

<option>September</option>

<option>Oktober</option>

<option>November</option>

<option>Desember</option>

</select>

<select name=”thn”>

<option selected>tahun

<?php

for($i = 1945; $i <= 2009; $i++){

echo “<option selected>$i”;

}

?>

</option>

</select>

<span class=”style4″>*</span> </td>

</tr>

<tr>

<td><span class=”style6″>Jenis Kelamin </span></td>

<td><p>

<label></label>

<select name=”sex”>

<option selected>-</option>

<option>Pria</option>

<option>Wanita</option>

</select>

<span class=”style4″>*</span><br>

</p></td>

</tr>

<tr>

<td><span class=”style6″>Gaji</span></td>

<td><select name=”gj”>

<option selected>Gaji Pokok</option>

<option>Rp 1.500.000</option>

<option>Rp 2.000.000</option>

<option>Rp 2.500.000</option>

<option>Rp 3.000.000</option>

<option>Rp 3.500.000</option>

<option>Rp 4.000.000</option>

<option>Rp 4.500.000</option>

<option>Rp 5.000.000</option>

</select>

<span class=”style4″>*</span> </td>

</tr>

<tr>

<td><span class=”style6″>Bagian</span></td>

<td><select name=”bg”>

<option selected>Bagian</option>

<option>Akutansi</option>

<option>PDE</option>

<option>Pemasaran</option>

<option>Produksi</option>

<option>SDM</option>

</select>

<span class=”style4″>*</span> </td>

</tr>

<tr>

<td colspan=”2″><div align=”right”>

<input type=”submit” value=”Simpan”>

<input type=”reset” value=”Kosongkan”>

</div></td>

</tr>

</table>

<br>

</div>

<label></label>

Ket

(*) akan diedit berdasarkan NIP yang di masukkan <br>

</form>

B. Script Untuk menyimpan hasil editan data

<?php

$nip = $_REQUEST[“nip”];

$nm = $_REQUEST[“nm”];

$kt = $_REQUEST[“kt”];

$tgl = $_REQUEST[“tgl”];

$bln = $_REQUEST[“bln”];

$thn = $_REQUEST[“thn”];

$sex = $_REQUEST[“sex”];

$gj = $_REQUEST[“gj”];

$bg = $_REQUEST[“bg”];

$conn = mysql_connect(“localhost”,”root”,””);

if (($nip == “”) and ($nm == “”) and ($kt == “”) and ($tgl == “tgl”) and ($bln == “bulan”) and ($thn ==”tahun”) and ($sex ==”-“) and ($gj == “Gaji Pokok”) and ($bg == “Bagian”)){

echo “Akses Error.! Mohon isi semua FIELD”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($nip == “”){

echo “Mohon Masukkan NIP Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($nm == “”){

echo “Mohon Masukkan Nama Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($kt == “”){

echo “Mohon Masukkan Kota Asal Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($tgl == “tgl”){

echo “Mohon Masukkan Tanggal Lahir Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($bln == “bulan”){

echo “Mohon Masukkan Bulan Lahir Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($thn ==”tahun”){

echo “Mohon Masukkan Tahun Lahir Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($sex ==”-“){

echo “Mohon Masukkan Jenis Kelamin Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($gj == “Gaji Pokok”){

echo “Mohon Masukkan Gaji Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($bg == “bagian”){

echo “Mohon Masukkan Posisi Bagian Pegawai”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Back</a>”;

}elseif ($conn) {

mysql_select_db(“tugas”);

echo “<strong>NIP Pegawai</strong>”;

echo “<br>”;

echo “$nip”;

echo “<br>”;

echo “<strong>Nama Pegawai </strong>”;

echo “<br>”;

echo “$nm”;

echo “<br>”;

echo “<strong>Tanggal Lahir </strong>”;

echo “<br>”;

echo “$tgl  $bln  $thn”;

echo “<br>”;

echo “<strong>Kota </strong>”;

echo “<br>”;

echo “$kt”;

echo “<br>”;

echo “<strong>Sex </strong>”;

echo “<br>”;

echo “$sex”;

echo “<br>”;

echo “<strong>Gaji </strong>”;

echo “<br>”;

echo “$gj”;

echo “<br>”;

echo “<strong>Bagian </strong>”;

echo “<br>”;

echo “$bg”;

echo “<br>”;

echo “================================================”;

echo “<br>”;

echo “<br>”;

$sqlstr = “update infopribadi set nama = ‘$nm’, tgl = ‘$tgl’,bln = ‘$bln’,thn = ‘$thn’,kota = ‘$kt’,sex = ‘$sex’,gaji = ‘$gj’,bagian = ‘$bg’ where nip = ‘$nip'”;

$hasil = mysql_query($sqlstr,$conn);

echo “Pegawai dengan nip $nip telah berhasil diedit”;

echo “<br>”;

echo ”     <a href=’tugas_up.php’>Edit Data Lagi</a>”;

echo “<br>”;

echo ”     <a href=’tugas_liat.php’>Melihat Data Pegawai</a>”;

mysql_close($conn);

}else {

echo “Server Not Connected”;

}

?>

Program Penghapusan Data dari database

A. Script untuk Menginput Data melalui NIP sebagai Primary Key

<DIV class =”scroll style 13 style 1″>

<div align=”left”>

<?php

$conn = mysql_connect(“localhost”,”root”,””);

if ($conn) {

mysql_select_db(“tugas”);

$sqlstr = “select * from infopribadi order by nip asc”;

$hasil = mysql_query($sqlstr,$conn);

$row = mysql_fetch_row($hasil);

if($row) {

do {

list($nip,$nm,$tgl,$bln,$thn,$kt,$sex,$gj,$bg) = $row;

echo “<strong>NIP Pegawai</strong>”;

echo “<br>”;

echo “$nip”;

echo “<br>”;

echo “<strong>Nama Pegawai </strong>”;

echo “<br>”;

echo “$nm”;

echo “<br>”;

echo “<strong>Tanggal Lahir </strong>”;

echo “<br>”;

echo “$tgl  $bln  $thn”;

echo “<br>”;

echo “<strong>Kota </strong>”;

echo “<br>”;

echo “$kt”;

echo “<br>”;

echo “<strong>Sex </strong>”;

echo “<br>”;

echo “$sex”;

echo “<br>”;

echo “<strong>Gaji </strong>”;

echo “<br>”;

echo “$gj”;

echo “<br>”;

echo “<strong>Bagian </strong>”;

echo “<br>”;

echo “$bg”;

echo “<br>”;

echo “===================================”;

echo “<br>”;

echo “<br>”;

}while ($row = mysql_fetch_row($hasil));

}else{

echo “Data Masih Kosong!!”;

echo”<br>”;

}

mysql_close($conn);

}else{

echo “Server Not Connected..!!”;

}

?>

</div>

</DIV>

<p class=”style1″>&nbsp;</p>

<p class=”style1″>Masukkan NIP Pegawai yang akan didelete</p>

<form action=”tugas_delete2.php” method=”post” name=”form1″>

<input name=”nip” size=”3″ maxlength=”5″>

<input type=”submit” value=”Proses Berikut”>

</form>

B. Script proses penghapusan Data dari database

<font size=”2″ face=”Verdana, Arial, Helvetica, sans-serif”>

<?php

$nip = $_REQUEST[“nip”];

$conn = mysql_connect(“localhost”,”root”,””);

if ($nip == “”){

echo “Gagal melakukan penghapusan karena NIP masih kosong”;

echo “<br>”;

echo “Click<a href=’tugas_delete.php’> disini</a> untuk kembali”;

}elseif ($conn) {

mysql_select_db(“tugas”);

$sqlstr = “delete from infopribadi where nip = ‘$nip'”;

$hasil = mysql_query($sqlstr,$conn);

echo “Data Pegawai dengan NIP : $nip telah diDELETE dari database”;

echo “<br>”;

echo ”     <a href=’tugas_liat.php’>Melihat Data Pegawai</a>”;

mysql_close($conn);

}else {

echo “Server Not Connected”;

}

?>

</font>

Itulah contoh program yang saya kembangkan melalui buku – buku PHP dan MYSQL..! semoga saja bermanfaat bagi User – User yang membutuhkan..!

2 Comments

  1. Nian christian said,

    thanks tutornya…
    akan kucoba deh….. di webku oke..

  2. tono said,

    gmn dengan kenaikan golongan yang otomatis karena telah berada pada golongan itu selama 2 tahun? mhn bantuannya

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: