SQL (Structure Query Language)

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI SQL (Structure Query Language) C:\Users\walidumar>cd / C:\>cd xampp/mysql/bin C:\xampp\mysql\bin>mysql -u root -p Enter password: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cdcol | | mysql | | performance_schema | | phpmyadmin | | test | | webauth | +--------------------+ 7 rows in set (3.26 sec) mysql> create database sekolah; Query OK, 1 row affected (0.36 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cdcol | | mysql | | performance_schema | | phpmyadmin | | sekolah | | test | | webauth | +--------------------+ 8 rows in set (0.00 sec) mysql> drop database sekolah; Query OK, 0 rows affected (0.64 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cdcol | | mysql | | performance_schema | | phpmyadmin | | test | | webauth | +--------------------+ 7 rows in set (0.00 sec) mysql> create database sekolah; Query OK, 1 row affected (0.02 sec) mysql> use sekolah; Database changed mysql> create table siswa(nisn int(10), nama varchar(30), umur int(3)); Query OK, 0 rows affected (0.42 sec) mysql> desc siswa; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama | varchar(30) | YES | | NULL | | | umur | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.14 sec) mysql> show tables; +-------------------+ | Tables_in_sekolah | +-------------------+ | siswa | +-------------------+ 1 row in set (0.00 sec) mysql> alter table siswa -> add alamat varchar(30); Query OK, 0 rows affected (0.42 sec) Records: 0 Duplicates: 0 Warnings: 0

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI mysql> desc siswa; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama | varchar(30) | YES | | NULL | | | umur | int(3) | YES | | NULL | | | alamat | varchar(30) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> alter table siswa -> add tempatlahir varchar(20) -> after umur; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc siswa; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama | varchar(30) | YES | | NULL | | | umur | int(3) | YES | | NULL | | | tempatlahir | varchar(20) | YES | | NULL | | | alamat | varchar(30) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 5 rows in set (0.03 sec) mysql> alter table siswa add tgllahir DATE; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc siswa; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama | varchar(30) | YES | | NULL | | | umur | int(3) | YES | | NULL | | | tempatlahir | varchar(20) | YES | | NULL | | | alamat | varchar(30) | YES | | NULL | | | tgllahir | date | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec) mysql> alter table siswa -> drop tgllahir; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc siswa; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama | varchar(30) | YES | | NULL | | | umur | int(3) | YES | | NULL | | | tempatlahir | varchar(20) | YES | | NULL | | | alamat | varchar(30) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 6 rows in set (0.05 sec) mysql> alter table siswa -> add tgllahir date -> after tempatlahir; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc siswa; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama | varchar(30) | YES | | NULL | | | umur | int(3) | YES | | NULL | | | tempatlahir | varchar(20) | YES | | NULL | | | tgllahir | date | YES | | NULL | | | alamat | varchar(30) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 6 rows in set (0.05 sec)

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI mysql> alter table siswa -> change nama -> nama_lengkap varchar(40); Query OK, 0 rows affected (0.45 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc siswa; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | nisn | int(10) | YES | | NULL | | | nama_lengkap | varchar(40) | YES | | NULL | | | umur | int(3) | YES | | NULL | | | tempatlahir | varchar(20) | YES | | NULL | | | tgllahir | date | YES | | NULL | | | alamat | varchar(30) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 6 rows in set (0.13 sec) mysql> insert into siswa -> values -> ('1001','Krisdewanto','19','Gorontalo','19960816','Liluwo'); Query OK, 1 row affected (0.03 sec) mysql> insert into siswa -> values -> ('1002','Ferlianto Saramadi','20','Gorontalo','19960716','Pulubala'), -> ('1003','Priya Anggraini','17','Gorontalo','19981206','Awara'); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from siswa; +------+--------------------+------+-------------+------------+----------+ | nisn | nama_lengkap | umur | tempatlahir | tgllahir | alamat | +------+--------------------+------+-------------+------------+----------+ | 1001 | Krisdewanto | 19 | Gorontalo | 1996-08-16 | Liluwo | | 1002 | Ferlianto Saramadi | 20 | Gorontalo | 1996-07-16 | Pulubala | | 1003 | Priya Anggraini | 17 | Gorontalo | 1998-12-06 | Awara | +------+--------------------+------+-------------+------------+----------+ 3 rows in set (0.00 sec) mysql> update siswa -> set tempatlahir = 'Mongondow' -> where nisn ='1002'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from siswa; +------+--------------------+------+-------------+------------+----------+ | nisn | nama_lengkap | umur | tempatlahir | tgllahir | alamat | +------+--------------------+------+-------------+------------+----------+ | 1001 | Krisdewanto | 19 | Gorontalo | 1996-08-16 | Liluwo | | 1002 | Ferlianto Saramadi | 20 | Mongondow | 1996-07-16 | Pulubala | | 1003 | Priya Anggraini | 17 | Gorontalo | 1998-12-06 | Awara | +------+--------------------+------+-------------+------------+----------+ 3 rows in set (0.02 sec) mysql> update siswa -> set umur='18', nama_lengkap='Priya Anggrayni' -> where nisn='1003'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from siswa; +------+--------------------+------+-------------+------------+----------+ | nisn | nama_lengkap | umur | tempatlahir | tgllahir | alamat | +------+--------------------+------+-------------+------------+----------+ | 1001 | Krisdewanto | 19 | Gorontalo | 1996-08-16 | Liluwo | | 1002 | Ferlianto Saramadi | 20 | Mongondow | 1996-07-16 | Pulubala | | 1003 | Priya Anggrayni | 18 | Gorontalo | 1998-12-06 | Awara | +------+--------------------+------+-------------+------------+----------+ 3 rows in set (0.00 sec) mysql> delete from siswa -> where nisn='1001'; Query OK, 1 row affected (0.05 sec) mysql> select * from siswa; +------+--------------------+------+-------------+------------+----------+ | nisn | nama_lengkap | umur | tempatlahir | tgllahir | alamat | +------+--------------------+------+-------------+------------+----------+ | 1002 | Ferlianto Saramadi | 20 | Mongondow | 1996-07-16 | Pulubala | | 1003 | Priya Anggrayni | 18 | Gorontalo | 1998-12-06 | Awara | +------+--------------------+------+-------------+------------+----------+ 2 rows in set (0.00 sec) mysql> delete from siswa -> where umur >=17; Query OK, 2 rows affected (0.06 sec) mysql> select * from siswa; Empty set (0.00 sec)

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI mysql> select * from siswa; +------+-------------------+--------+------+-------------+------------+-----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+-------------------+--------+------+-------------+------------+-----------+ | 1001 | Nadiyanti Paris | Wanita | 20 | Gorontalo | 1997-07-08 | Gorontalo | | 1002 | Merlin Daud | Wanita | 20 | Gorontalo | 1998-07-06 | Gorontalo | | 1003 | Rifka R. Pina | Wanita | 20 | Jawa Barat | 1997-06-08 | Gorontalo | | 1004 | Rin Yusria Amrain | Wanita | 20 | Gorontalo | 1998-12-12 | Kabila | | 1005 | Nur Afni Teu | Wanita | 20 | Gorontalo | 1998-10-10 | Awara | | 1006 | Simon Olii | Pria | 22 | Gorontalo | 1995-04-06 | Pulubala | | 1007 | Jerianto Launuha | Pria | 22 | Gorontalo | 1995-06-06 | Gorontalo | +------+-------------------+--------+------+-------------+------------+-----------+ 7 rows in set (0.00 sec) mysql> select * from siswa -> where gender='Pria'; +------+------------------+--------+------+-------------+------------+-----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+------------------+--------+------+-------------+------------+-----------+ | 1006 | Simon Olii | Pria | 22 | Gorontalo | 1995-04-06 | Pulubala | | 1007 | Jerianto Launuha | Pria | 22 | Gorontalo | 1995-06-06 | Gorontalo | +------+------------------+--------+------+-------------+------------+-----------+ 2 rows in set (0.05 sec) mysql> select * from siswa where umur <=20; +------+-------------------+--------+------+-------------+------------+-----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+-------------------+--------+------+-------------+------------+-----------+ | 1001 | Nadiyanti Paris | Wanita | 20 | Gorontalo | 1997-07-08 | Gorontalo | | 1002 | Merlin Daud | Wanita | 20 | Gorontalo | 1998-07-06 | Gorontalo | | 1003 | Rifka R. Pina | Wanita | 20 | Jawa Barat | 1997-06-08 | Gorontalo | | 1004 | Rin Yusria Amrain | Wanita | 20 | Gorontalo | 1998-12-12 | Kabila | | 1005 | Nur Afni Teu | Wanita | 20 | Gorontalo | 1998-10-10 | Awara | +------+-------------------+--------+------+-------------+------------+-----------+ 5 rows in set (0.00 sec) mysql> select * from siswa -> where nama_lengkap like 'Ri%'; +------+-------------------+--------+------+-------------+------------+-----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+-------------------+--------+------+-------------+------------+-----------+ | 1003 | Rifka R. Pina | Wanita | 20 | Jawa Barat | 1997-06-08 | Gorontalo | | 1004 | Rin Yusria Amrain | Wanita | 20 | Gorontalo | 1998-12-12 | Kabila | +------+-------------------+--------+------+-------------+------------+-----------+ 2 rows in set (0.06 sec) mysql> select * from siswa -> where umur !=20; +------+------------------+--------+------+-------------+------------+-----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+------------------+--------+------+-------------+------------+-----------+ | 1006 | Simon Olii | Pria | 22 | Gorontalo | 1995-04-06 | Pulubala | | 1007 | Jerianto Launuha | Pria | 22 | Gorontalo | 1995-06-06 | Gorontalo | +------+------------------+--------+------+-------------+------------+-----------+ 2 rows in set (0.02 sec) mysql> select * from siswa -> where nama_lengkap like '%a'; +------+------------------+--------+------+-------------+------------+-----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+------------------+--------+------+-------------+------------+-----------+ | 1003 | Rifka R. Pina | Wanita | 20 | Jawa Barat | 1997-06-08 | Gorontalo | | 1007 | Jerianto Launuha | Pria | 22 | Gorontalo | 1995-06-06 | Gorontalo | +------+------------------+--------+------+-------------+------------+-----------+ 2 rows in set (0.00 sec) mysql> select * from siswa -> where nama_lengkap like '%ii%'; +------+--------------+--------+------+-------------+------------+----------+ | nisn | nama_lengkap | gender | umur | tempatlahir | tgllahir | alamat | +------+--------------+--------+------+-------------+------------+----------+ | 1006 | Simon Olii | Pria | 22 | Gorontalo | 1995-04-06 | Pulubala | +------+--------------+--------+------+-------------+------------+----------+ 1 row in set (0.00 sec) mysql> select nama_lengkap from siswa -> where umur >=20 AND tempatlahir !='Gorontalo'; +---------------+ | nama_lengkap | +---------------+ | Rifka R. Pina | +---------------+ 1 row in set (0.02 sec)

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI mysql> select nama_lengkap, tempatlahir, tgllahir from siswa; +-------------------+-------------+------------+ | nama_lengkap | tempatlahir | tgllahir | +-------------------+-------------+------------+ | Nadiyanti Paris | Gorontalo | 1997-07-08 | | Merlin Daud | Gorontalo | 1998-07-06 | | Rifka R. Pina | Jawa Barat | 1997-06-08 | | Rin Yusria Amrain | Gorontalo | 1998-12-12 | | Nur Afni Teu | Gorontalo | 1998-10-10 | | Simon Olii | Gorontalo | 1995-04-06 | | Jerianto Launuha | Gorontalo | 1995-06-06 | +-------------------+-------------+------------+ 7 rows in set (0.00 sec) mysql> select nama_lengkap from siswa -> where tempatlahir like '%lo' OR gender='Pria'; +-------------------+ | nama_lengkap | +-------------------+ | Nadiyanti Paris | | Merlin Daud | | Rin Yusria Amrain | | Nur Afni Teu | | Simon Olii | | Jerianto Launuha | +-------------------+ 6 rows in set (0.00 sec)

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI

PHP Struktur Dasar PHP - Komentar

Struktur Dasar PHP – Komentar multibaris

Menampilkan Teks String

HTML Inner PHP ini kolaborasi HTML & PHP'; ?>

Variabel 1

Variabel 2 Salam Kenal!'; ?>

PHP Operator Operator Aritmatika

Operator

Deskripsi

+

Penjumlahan

-

Pengurangan

*

Perkalian

/

Pembagian

%

Modulus (Sisa Bagi)

++

Peningkatan

--

Penurunan

Contoh x=2 x+2 x=2 5-x x=4 x*5 15/5 5/2 5%2 10%8 10%2 x=5 x++ x=5 x--

Hasil 4 3 20 3 2.5 1 2 0 x=6 x=4

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI

Operator Perbandingan

Operator == != <> > < >= <=

Deskripsi Sama dengan Tidak sama dengan Tidak sama dengan Lebih dari Kurang dari Lebih dari sama dengan Kurang dari sama dengan

Contoh 5==8 false 5!=8 true 5<>8 true 5>8 false 5<8 true 5>=8 false 5<=8 true

Operator Logika Operator &&

DAN

Deskripsi

||

ATAU

!

TIDAK

Contoh x=6 y=3 (x < 10 && y > 1) adalah true x=6 y=3 (x==5 || y==5) adalah false x=6 y=3! (x==y) adalah true

Operasi Aritmatika Variabel '; $kurang=$a-$b; echo 'hasil kurang echo '
'; $jumlah=$a+$b; echo 'hasil jumlah echo '
'; $bagi=$a/$b; echo 'hasil jumlah echo '
';

'.$kali;

= '.$kurang;

= '.$jumlah;

= '.$bagi;

?>

Operasi Aritmatika Variabel Rumus Menghitung Luas Segitiga
'; $panjang=10; $lebar=30; $luas=$panjang*$lebar; echo 'Luas Segitiga :: '.$panjang.' x '.$lebar.'='.$luas; ?>

Operator Logika & Perbandingan IF-ELSE = 75){ echo "Selamat! anda Lulus Ujian"; }else{ echo "Maaf ! anda harus remedial"; } ?>

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI Operator Logika & Perbandingan IF-ELSE 75){ echo "Selamat! anda Lulus Ujian"; }else{ echo "Maaf ! anda harus remedial"; } ?>

Operator Logika & Perbandingan if,elseif-else = 98){ echo "Nilai anda }else if ($nilai >=85){ echo "Nilai anda }else if ($nilai >=75){ echo "Nilai anda }else if ($nilai >=60){ echo "Nilai anda }else{ echo "Anda Harus }

: A"; : B"; : C"; : D"; Mengulang '_' ";

?>

Join String

Menghitung panjang String SMK Negeri 4 Gorontalo
Adalah : "; echo strlen("SMK Negeri 4 Gorontalo"); ?>

Menghitung panjang String (variabel)

Memposisikan letak value dalam string

Perulangan (looping) - while "; while($i<=10) { echo "Angka ". $i . " adalah ". $i . "
"; $i++; } ?>

Perulangan (looping) - for looping-for"; for ($i=0; $i<20; $i++){ echo "Angka : ". $i; echo "
"; } ?>

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI Perulangan (looping) – for looping-for
"; for ($i=1; $i<=20; $i++){ echo $i."..."; } ?>

Pengenalan Fungsi PHP - function '; echo 'bilangan A : '.$a.'
'; echo 'bilangan B : '.$b.'
'; $kali = $a * $b; echo 'adalah sama dengan > '; return $kali; } echo kali(5,3); ?>

HTML Form dan method POST

Nama	:
Jurusan	:

"; echo $jurusan . " is the best !" ?>

Operasi Penjumlahan 2 bilangan dengan method POST

A = B =

Operasi Penjumlahan 2 bilangan dengan method GET

A = B =

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI Fungsi koneksi php ke mysql

Menampilkan data query sql dengan metode perulangan (while) ke halaman web

Data Siswa TKJ SMK Negeri 4 Gorontalo "; while ($baris = mysql_fetch_array($query)){ echo ""; echo ""; } echo "

NISN	Nama	Gender	Umur	Tempat Lahir	Tanggal Lahir	Tanggal Lahir
". $baris['nisn']." "; echo "	". $baris['nama_lengkap']." "; echo "	". $baris['gender']." "; echo "	". $baris['umur']." "; echo "	". $baris['tempatlahir']." "; echo "	". $baris['tgllahir']." "; echo "	". $baris['alamat']." "; echo "

"; ?>

Menginput data query sql dengan tag form html

NISN	:
Nama Siswa	:
Gender	:
Umur	:
Tempat Lahir	:
Tanggal Lahir	:
Alamat	:

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI

Latihan (create, read, update, delete) Struktur Tabel CREATE TABLE `siswa` ( `siswa_id` int(11) NOT NULL auto_increment, `siswa_nis` int(20) NOT NULL, `siswa_nama` varchar(50) NOT NULL, `siswa_kelas` varchar(10) NOT NULL, `siswa_jurusan` varchar(30) NOT NULL, PRIMARY KEY (`siswa_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

Koneksi ke mysql (koneksi.php)
= = = =

"localhost"; "root"; "root"; "tugas ";

$koneksi = mysql_connect($host, $user, $pass) or die("Koneksi ke database gagal!"); mysql_select_db($name, $koneksi) or die("Tidak ada database yang dipilih!"); ?>

Tampilkan Data (tampil.html>

Beranda / Tambah Data

Data Siswa

'; }else{ $no = 1; while($data = mysql_fetch_assoc($query)){ echo ''; echo ''; //menampilkan nomor urut echo ''; echo ''; //menampilkan data nama lengkap dari database echo ''; //menampilkan data kelas dari database echo ''; //menampilkan data jurusan dari database echo ''; echo ''; $no++; }} ?>

No.	NIS	Nama Lengkap	Kelas	Jurusan	Opsi
Tidak ada data!
'.$no.'	'.$data['siswa_nis'].'	'.$data['siswa_nama'].'	'.$data['siswa_kelas'].'	'.$data['siswa_jurusan'].'	Edit / Hapus

Form Tambah (tambah.php)

Simple CRUD

Beranda / Tambah Data

Tambah Data Siswa

NIS	:
Nama Lengkap	:
Kelas	:	<select name="kelas" required> Pilih KelasX XIXII
Jurusan	:	<select name="jurusan" required>Pilih Jurusan Teknik Komputer dan Jaringan Akuntansi Kria Tekstil Kria Kayu

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI

Proses Tambah (tambah-proses.php) Kembali'; }else{ echo 'Gagal menambahkan data! '; echo 'Kembali'; } }else{ echo '<script>window.history.back()'; } ?>

or die(mysql_error());

Edit Data (edit-data.php)

Beranda / Tambah Data

Edit Data Siswa

edit.php?id=siswa_id $id = $_GET['id']; $show = mysql_query("SELECT * FROM siswa WHERE siswa_id='$id'"); if(mysql_num_rows($show) == 0){ echo '<script>window.history.back()'; }else{ $data = mysql_fetch_assoc($show);}?>

NIS	:
Nama Lengkap	:
Kelas	:	<select name="kelas" required> Pilih Kelas >X >XI >XII
Jurusan	:	<select name="jurusan" required> Pilih Jurusan >Teknik Komputer dan Jaringan >Akuntansi >Kria Tekstil >Kria Kayu

Materi Pembelajaran Mulok (Web Desain) – Walid Umar, S.SI

Proses Edit (edit-proses.php) Kembali'; }else{ echo 'Gagal menyimpan data! '; echo 'Kembali'; } }else{ //jika tidak terdeteksi tombol simpan di klik echo '<script>window.history.back()'; } ?>

Hapus Data (hapus.php) window.history.back()'; }else{ $del = mysql_query("DELETE FROM siswa WHERE siswa_id='$id'"); if($del){ echo 'Data siswa berhasil di hapus! '; //Pesan jika proses hapus berhasil echo 'Kembali'; //membuat Link untuk kembali ke halaman beranda }else{ echo 'Gagal menghapus data! '; //Pesan jika proses hapus gagal echo 'Kembali'; //membuat Link untuk kembali ke halaman beranda }}}else{ echo '<script>window.history.back()'; } ?>