Erwin Heldy Blog

Database MySQL merupakan salah perangkat lunak untuk penyimpanan data yang sangat populer untuk saat ini. Salah satu alasan banyak orang memilih MySQL adalah keunggulan dan kelengkapan fitur-fitur di dalamnya. Salah satu fitur dari MySQL adalah dukungan terhadap berbagai fungsi agregat GROUP BY dan HAVING untuk pengelompokkan data. Pada tutorial ini dijelaskan mengenai fitur GROUP BY dan HAVING tersebut, disertai contohnya.

Sebelum mencoba fungsi GROUP BY dan HAVING, perlu diketahui bahwa keduanya sangat berkaitan dengan fungsi agregat. Fungsi agregat merupakan kelompok fungsi di MySQL yang memungkinkan untuk memiliki parameter berupa kelompok data. Sebagai contoh dari fungsi agregat adalah fungsi SUM() yang akan menjumlahkan seluruh nilai yang menjadi parameternya. Contoh lain adalah fungsi MAX() yang akan menghasilkan nilai terbesar dari keseluruhan nilai yang menjadi parameternya.

Berikut ini beberapa fungsi agregat yang ada di MySQL:

Name	Description
AVG()	Return the average value of the argument
BIT_AND()	Return bitwise and
BIT_OR()	Return bitwise or
BIT_XOR()	Return bitwise xor
COUNT(DISTINCT)	Return the count of a number of different values
COUNT()	Return a count of the number of rows returned
GROUP_CONCAT()	Return a concatenated string
MAX()	Return the maximum value
MIN()	Return the minimum value
STD()	Return the population standard deviation
STDDEV()	Return the population standard deviation
SUM()	Return the sum
VARIANCE()	Return the population standard variance

Untuk memudahkan pemahaman bagaimana fungsi GROUP BY dan HAVING digunakan, kita akan menggunakan contoh data di MySQL. Buatlah tabel nilai yang menyimpan data nilai mahasiswa, strukturnya sebagai berikut:

Column	Type	Comment
nim	varchar(10)	Nim mahasiswa
nama	varchar(30)	Nama mahasiswa
matkul	varchar(20)	Nama Matakuliah
nilai	int(3)	Nilai mahasiswa

Selanjutnya isi tabel tersebut dengan beberapa data. Bagi yang ga mau repot bikin, ini sudah disiapkan perintah untuk bikin tabel dan mengisi datanya dalam bentuk file .sql. Silahkan download nilai.sql.

Berikut ini beberapa contoh persoalan yang dapat diselesaikan dengan fungsi GROUP BY dan HAVING.

Contoh Kasus #1. Tampilkan rata-rata nilai dari seluruh mahasiswa.

Untuk menyelesaikannya tidak perlu menggunakan GROUP BY karena yang diminta adalah seluruh mahasiswa. Untuk mendapatkan rata-rata nilai, kita dapat menggunakan fungsi AVG(). Berikut ini query untuk kasus tersebut.

SELECT AVG(nilai) as rata_rata FROM nilai;

Berikut ini hasil dari query di atas.

+-----------+
| rata_rata |
+-----------+
| 79.6800   |
+-----------+

Contoh Kasus #2. Tampilkan rata-rata nilai untuk setiap mahasiswa

Karena yang diminta adalah rata-rata untuk setiap mahasiswa, maka kita harus menggunakan GROUP BY untuk mengelompokkan rata-rata berdasarkan field tertentu. Query dan hasilnya kurang lebih sebagai berikut:

SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim;

+------------+------+-----------+
| nim        | nama | rata_rata |
+------------+------+-----------+
| 0911500101 | ADI  | 82.0000   |
| 0911500102 | IDA  | 81.0000   |
| 0911500103 | EDI  | 80.6000   |
| 0911500104 | INA  | 70.4000   |
| 0911500105 | ANI  | 84.4000   |
+------------+------+-----------+

Contoh Kasus #3. Tampilkan nilai terbesar dan terkecil untuk setiap mahasiswa.

Query untuk menyelesaikan kasus ketiga di atas sebenarnya sama saja dengan yang sebelumnya. Perbedaannya hanya pada fungsi agregat yang digunakan untuk menampilkan nilai terbesar dan terkecil yaitu MAX() dan MIN(). Berikut ini query dan hasil query-nya.

SELECT nim, nama, MAX(nilai) as terbesar, MIN(nilai) as terkecil FROM nilai GROUP BY nim;

+------------+------+----------+----------+
| nim        | nama | terbesar | terkecil |
+------------+------+----------+----------+
| 0911500101 | ADI  | 90       | 65       |
| 0911500102 | IDA  | 90       | 70       |
| 0911500103 | EDI  | 88       | 60       |
| 0911500104 | INA  | 80       | 50       |
| 0911500105 | ANI  | 92       | 68       |
+------------+------+----------+----------+

Contoh Kasus #4. Tampilkan rata-rata nilai yang didapat mahasiswa untuk setiap matakuliah

Cukup jelas bahwa pada kasus ini, mirip dengan kasus kedua di atas, namun pengelompokkan data berdasarkan matakuliah, bukan berdasarkan mahasiswa. Querynya kurang lebih sebagai berikut:

SELECT matkul, AVG(nilai) as rata_rata FROM nilai GROUP BY matkul;

+-----------------+-----------+
| matkul          | rata_rata |
+-----------------+-----------+
| ALGORITMA       | 84.4000   |
| KALKULUS        | 62.6000   |
| PBO             | 82.0000   |
| PEMROGRAMAN WEB | 86.4000   |
| PTI             | 83.0000   |
+-----------------+-----------+

Contoh Kasus #5. Tampilkan rata-rata nilai untuk setiap mahasiswa, yang rata-rata nilai lebih besar dari 80

Perhatikan kembali kasus kedua di atas. Hasil query menunjukkan bahwa untuk setiap mahasiswa akan ditampilkan nilai rata-rata yang diperoleh. Nah pada kasus ke-5 ini yang ingin ditampilkan adalah hanya mahasiswa yang nilainya lebih dari 80. Artinya untuk mahasiswa atas nama “INA” yang mendapat nilai rata-rata 70.4 tidak boleh ditampilkan. Solusinya adalah dengan menambahkan kondisi pada query kasus kedua diatas. Nah, jika kondisi suatu query terkait dengan fungsi agregat, maka kita tidak bisa menggunakan kondisi WHERE. Penggunaan WHERE pada fungsi agregat akan menyebabkan error.

Cobalah query berikut ini.

SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim WHERE AVG(nilai)>80;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE AVG(nilai)>80' at line 1

Lalu bagaimana solusinya jika tidak bisa menggunakan WHERE ? MySQL (dan juga database yang lainnya) memiliki struktur kondisi khusus terkait fungsi agregat yaitu HAVING. Jika query diatas diperbaiki dan menggunakan HAVING, maka hasilnya sebagai berikut:

SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim HAVING AVG(nilai)>80;

+------------+------+-----------+
| nim        | nama | rata_rata |
+------------+------+-----------+
| 0911500101 | ADI  | 82.0000   |
| 0911500102 | IDA  | 81.0000   |
| 0911500103 | EDI  | 80.6000   |
| 0911500105 | ANI  | 84.4000   |
+------------+------+-----------+

Gampang kan?

Silahkan Anda berkreasi berdasarkan contoh-contoh di atas. Semoga tutorial ini bermanfaat untuk kita semua. Amin

 

Sumber

Data Manipulation Language (DML) merupakan bagian dari perintah SQL (Structured Query Language) yang terdiri dari berbagai perintah untuk memanipulasi data di dalam suatu database. Empat perintah utama yang termasuk dalam perintah DML adalah perintah SELECT, INSERT, UPDATE dan DELETE. MySQL sebagai salah satu software database terkemuka tentunya mendukung keempat perintah DML tersebut dengan sangat baik. Di dalam tutorial kali ini, akan dibahas mengenai perintah INSERT dimana ternyata cukup banyak variasi perintah INSERT di MySQL yang sering terlupakan (terabaikan), namun sebenarnya sangat berguna.

Selain bentuk dasar perintah INSERT, setidaknya ada 7 (tujuh) variasi bentuk perintah INSERT yang  suatu saat akan berguna untuk Anda. Berikut ini ketujuh perintah INSERT tersebut yang selanjutnya akan dijelaskan satu per satu.

1. INSERT INTO (field1, field2, …) VALUES (…)
2. INSERT … SELECT …
3. INSERT IGNORE …
4. INSERT DELAYED …
5. INSERT LOW PRIORITY | HIGH PRIORITY …
6. INSERT … ON DUPLICATE KEY UPDATE
7. REPLACE INTO …

Untuk mempermudah pemahaman terhadap tutorial ini, maka seluruh contoh yang disajikan akan mengacu pada struktur tabel sebagai berikut:

Nama tabel: tblmhs

mysql> desc tblmhs;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nim      | varchar(10) | NO   | PRI | NULL    |       |
| nama     | varchar(40) | NO   |     | NULL    |       |
| alamat   | text        | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Di sini tidak dibahas mengenai bagaimana membuat struktur tabel di atas.

Bentuk Dasar Perintah INSERT

Sebelum membahas variasi dari perintah INSERT, alangkah baiknya kita mengingat kembali mengenai bagaimana bentuk dasar dari perintah INSERT tersebut. Perintah INSERT pada dasarnya merupakan perintah yang dapat digunakan untuk memasukkan data (record) ke sebuah tabel di database. Bentuk perintah ini cukup sederhana. Berikut ini contoh sederhana perintah INSERT untuk memasukkan data seorang mahasiswa dengan nim ‘1011500100‘, nama ‘ACHMAD SOLICHIN‘ dan alamat di JAKARTA SELATAN.

INSERT INTO tblmhs VALUES('1011500100', 'ACHMAD SOLICHIN', 'JAKARTA SELATAN');

Hal yang penting dari contoh perintah INSERT di atas adalah urutan dari nilai data yang akan ditambahkan harus sama dengan urutan field (kolom) di tabel. Urutan field di dalam tabel tblmhs adalah nim, nama lalu alamat, sehingga waktu melakukan INSERT urutan seharusnya juga nim, nama dan alamat. Jika urutan tidak sesuai, maka data yang dimasukkan bisa jadi menempati kolom yang salah juga

#1. INSERT INTO tabel (field1, field2) VALUES …

Pada bentuk dasar perintah INSERT di atas, kita harus menyebutkan nilai dari seluruh kolom. Jadi jumlah nilai yang akan ditambahkan harus sesuai dengan jumlah kolom dalam tabel. Namun ada kalanya kita hanya ingin menambahkan sebagian dari kolom tabel, jadi tidak semua kolom kita masukkan. Jika demikian, variasi perintah INSERT yang pertama ini tentu tepat untuk digunakan. Kita menyebutkan kolom-kolom yang akan di-insert-kan di belakang nama tabel. Urutannya juga tidak harus sesuai dengan urutan di dalam struktur tabel. Sebagai contoh, kita akan memasukkan data nim dan nama seorang mahasiswa, tanpa memasukkan alamatnya. Urutan fieldnya sengaja dibalik, nama terlebih dahulu baru nim. Berikut perintahnya:

INSERT INTO tblmhs (nama, nim) VALUES('CHOTIMATUL MUSYAROFAH', '1012501983');

Terlihat pada contoh di atas bahwa urutan yang harus disesuaikan adalah urutan field yang berada di belakang nama tabel ‘tblmhs’ dan urutan data (values) yang akan di-insert-kan.

#2. INSERT … SELECT …

Ada kalanya kita ingin memindahkan atau meng-copy sejumlah data yang berada pada suatu tabel ke tabel yang lainnya. Singkatnya copy data antar tabel. Untuk menyelesaikan permasalahan tersebut, tentunya sangat kurang efektif jika kita harus melakukan insert satu per satu. Untuk melakukan copy record antar tabel, akan lebih efektif jika kita menggunakan bentuk perintah “INSERT … SELECT …” yang menggabungkan proses insert (menambahkan data) dan select (mengambil data). Sebagai contoh, kita ingin memindahkan data nim dan nama mahasiswa yang berada di tabel tblpendaftaran ke tabel tblmhs, berikut ini perintahnya:

INSERT INTO tblmhs(nim, nama) SELECT nim, nama FROM tblpendaftaran;

#3. INSERT IGNORE

Penulis pernah membuat suatu absensi pada suatu perusahaan dimana data absensi diimport dari suatu file teks hasil keluaran dari mesin finger. Setelah diimport, data absensi karyawan akan diperiksa dan dilengkapi oleh staf HRD. Proses import dari teks file dapat terjadi berulang-ulang sehingga diperlukan mekanisme agar pada saat proses import data yang mungkin sudah diedit oleh staf HRD tidak tertimpa. Jadi intinya, saat proses import, harus diperiksa apakah data absensi yang akan dimasukkan ke tabel sudah ada atau belum. Jika sudah ada, maka tidak perlu dilakukan insert, namun jika tidak ada maka lakukan proses import. Awalnya terpikir untuk melakukan pengecekan data ke database terlebih dahulu sebelum insert, namun dari isi efektivitas tentu hal ini tidak baik, apalagi jika datanya cukup banyak. Beruntung kemudian ketemu bentuk perintah INSERT IGNORE … di MySQL. Dengan perintah ini, permasalahan tersebut dapat terselesaikan hanya dengan satu perintah.

Dalam contoh berikut ini, akan melakukan insert data ke tabel tblmhs hanya jika data nim belum ada di tabel. Pemeriksaan data didasarkan pada field yang menjadi primary key. Jadi jika terjadi duplikasi primary key maka data akan diabaikan (ignored).

INSERT IGNORE INTO tblmhs VALUES('1012501983','BUNGA', 'TANGERANG');

#4. INSERT DELAYED

Bentuk perintah INSERT DELAYED … merupakan bentuk perintah yang mungkin akan kita perlukan saat membuat suatu aplikasi transaksional dengan beberapa pengguna dalam satu waktu. Dengan tambahan perintah “DELAYED” maka proses menambahkan data akan ditangguhkan hingga tidak ada user yang mengakses tabel. Contoh berikut ini menggambarkan bagaimana perintah tersebut:

INSERT DELAYED INTO tblmhs (nim, nama) VALUES ('1011500121', 'LINTANG', 'TANGERANG');

Penjelasan lebih lanjut mengenai perintah ini dapat dilihat di manual MySQL.

#5. INSERT LOW PRIORITY | HIGH PRIORITY

Tambahan perintah LOW PRIORITY atau HIGH PRIORITY dibelakang perintah INSERT dapat digunakan untuk mengatur prioritas dari perintah INSERT yang akan dijalankan. Perintah dengan prioritas lebih tinggi tentu akan didahulukan untuk dijalankan. Sama seperti bentuk perintah INSERT DELAYED, perintah ini juga berguna saat kita “bermain” dengan proses transaksional. Berikut ini contoh sederhananya:

INSERT INTO tblmhs VALUES('1111500111', 'INDAH', 'JAKARTA');

#6. INSERT … ON DUPLICATE KEY UPDATE

Variasi perintah INSERT ini mungkin suatu saat akan kita perlukan. Misalnya pada kasus dimana pada proses insert sejumlah data, jika datanya sudah ada, dalam arti terjadi duplikasi primary key, maka lakukan perintah update untuk kolom tertentu. Sebagai contoh, kita akan melakukan penambahan data ke tblmhs, jika datanya sudah ada, maka update field alamat dengan alamat yang baru. Berikut ini perintahnya:

INSERT INTO tblmhs (nim, nama, alamat) VALUES('1111500111', 'WULAN', 'MALANG') ON DUPLICATE KEY UPDATE alamat='JAKARTA';

Untuk penjelasan lebih rinci terkait perintah ini dapat dilihat di dokumentasi MySQL (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html).

#7. REPLACE INTO …

Penulis sangat senang dengan adanya bentuk perintah REPLACE di MySQL ini. Perintah ini sangatlah membantu. Contohnya saat penulis ingin melakukan penambahan data secara massal seperti pada kasus import absensi karyawan di atas. Jika pada proses import, kita menginginkan agar data selalu up to date, maka jika data sudah ada harus dilakukan proses update. Perintah REPLACE INTO berperan untuk melakukan dua tugas sekaligus, yaitu INSERT jika datanya belum ada dan UPDATE jika datanya sudah ada. Berikut ini contoh perintah ini:

REPLACE INTO tblmhs VALUES('1011500100', 'ACHMAD SOLICHIN', 'TANGERANG SELATAN');

Demikian ketujuh bentuk variasi perintah INSERT di dalam MySQL yang sering terlupakan namun seringkali kita butuhkan. Semoga postingan ini bermanfaat untuk kita semua. Mari terus semangat berbagi demi Indonesia!

 

Sumber

Kali ini saya akan memberikan contoh penggunaan database, session, dan enkripsi MD5 untuk menjadikannya sebagai halaman login yang cukup aman.

Kebutuhan:
- Webserver dengan dukungan PHP
- Myslq Database Server

Secara sederhana, cara kerjanya adalah sbb:
- Ketika user memasukkan username dan password-nya, maka yang pertama kali dilakukan adalah mengeceknya dalam database apakah username tersebut telah terdaftar.
- Jika telah terdaftar dan berhasil melakukan login, maka sang user akan diberi sebuah session yang diambil dari usernamenya
- Jika kesemuanya tidak sesuai maka sang user akan diredirect ke halaman login.php, sehingga terlihat seperti diam ditempat.

Pertama-tama kita buat databasenya

create database coba;
use coba;
CREATE TABLE IF NOT EXISTS `users` (
  `username` varchar(20) NOT NULL,
  `password` varchar(200) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

selanjutnya kita buat file-file php-nya

config.php

<?php 
//membuat koneksi ke database
mysql_connect("localhost", "root", "admin");

//localhost = nama server-nya
//root  = user mysql
//admin  = password user root
 
//memilih database
mysql_select_db("coba");
?>

login.php

<?php 
//memulai session
session_start(); 

//cek adanya session, jika session sudah ada maka diarahkan ke index.php
if (ISSET($_SESSION['username'])){
header("location: index.php");
}
?>


<form method="post" action="cek.php">
<pre>Username <input name="username" type="text">
Password <input name="password" type="password">
  <input name="submit" value="Login" type="submit">

  Bukan Member? Silahkan Daftar
  <a href="signup.php">Daftar</a>
  
</pre>
</form>  

cek.php

<?php 
//memulai session
session_start(); 

//koneksi ke database
include "config.php" ; 

//mengambil data dari form
$username    = $_POST['username']; 
$password    = $_POST['password']; 

//enkripsi password dengan md5
$password_md5   = md5($password);

//cek username dan password dari database
$perintah   = "select * from  users where username='$username'&&password='$password_md5'"; 
$perintah_di_query = mysql_query($perintah); 
$ketersediaan  = mysql_num_rows($perintah_di_query); 

//Cek adanya username dan password di database dilanjutkan dengan membuat session
if ($ketersediaan >= 1 ){ 
$_SESSION['username'] = $username; 
header("location: index.php"); 
}else{
header("location: login.php"); 
}
?>

index.php

<?php 
//memulai session
session_start(); 

//cek adanya session
if (ISSET($_SESSION['username'])){
echo "Anda Login Sebagai ";
echo $_SESSION['username']; 
echo "<br><a href='logout.php'>logout</a>"; 

//jika tidak ada session
}else{
header("location: login.php");
}
?> 
<html><body>
<br><br><h2>
Home Page
<br><br></h2>
</body></html>

logout.php

<?php  
//memulai session
session_start(); 

//cek adanya session, jika session ada maka akan di unset dan dilanjutkan dengan destroy session
if(ISSET($_SESSION['username'])) { 
UNSET($_SESSION['username']); 
} 
header("location: index.php"); 
session_destroy(); 
?>

signup.php

<form method="post" action="signup_process.php">
<pre><h2>Form Pendaftaran</h2>
Username <input name="username" type="text">
Password <input name="password" type="password">
  <input name="submit" value="Daftar" type="submit">
</pre>
</form>

signup_process.php

<?php 
include "config.php";

///mengambil data dari form
$username  = $_POST['username'];
$password  = $_POST['password'];
$password_md5 = md5($password);

//cek pengisian data
if($username=='' || $password==''){
echo "Data tidak lengkap<br--><a href=signup.php>Back</a>";

//jika data sudah lengkap, dilanjutkan input data ke database
}else{
$perintah   = "insert into users values ('$username', '$password_md5')"; 
$perintah_di_query = mysql_query($perintah); 

//Jika input data berhasil, dilanjutkan dengan pemberitahuan pendaftaran berhasil
if ($perintah_di_query) {
echo "Daftar berhasil, silakan <a href='index.php'>login</a>";

//jika input data gagal, dilanjutkan dengan pemberitahuan pendaftaran gagal
}else{
echo "Daftar gagal atau username telah terdaftar silakan <a href='signup.php'>Ulangi</a> atau <a href='login.php'>Login</a>";
}
}
?>

- Pengujian dilakukan dengan membuat sebuah user dan melakukan proses login, jika berhasil maka Home Page akan tampil.

Login sebagai super user

sudo su

Install paket-paket pendukung

apt-get install mysql-server mysql-client nginx php5-fpm

/etc/init.d/nginx start

Sampai tahap ini, nginx sudah terpasang.
Arahkan browser ke http://localhost maka akan muncuk default html nginx







Konfigurasi nginx

edit file /etc/nginx/sites-available/default kurang lebih seperti berikut

server {
        listen   80; ## listen for ipv4; this line is default and implied
        listen   [::]:80 default ipv6only=on; ## listen for ipv6

        root /usr/share/nginx/www;
        index index.php index.html index.htm;

        # Make site accessible from http://localhost/
        server_name example.com;

        location / {
                # First attempt to serve request as file, then
                # as directory, then fall back to index.html
                try_files $uri $uri/ /index.html;
        }

        location /doc {
                root /usr/share;
                autoindex on;
                allow 127.0.0.1;
                deny all;
        }

        location /images {
                root /usr/share;
                autoindex off;
        }

        #error_page 404 /404.html;

        # redirect server error pages to the static page /50x.html
        #
        error_page 500 502 503 504 /50x.html;
        location = /50x.html {
                root /usr/share/nginx/www;
        }

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #       proxy_pass http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        location ~ \.php$ {
                fastcgi_pass 127.0.0.1:9000;
                fastcgi_index index.php;
                include fastcgi_params;
        }

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        location ~ /\.ht {
                deny all;
        }
}

*kofigurasi data disesuaikan

save konfigurasi dan restart nginx

/etc/init.d/nginx restart

Untuk menguji php sudah berjalan, buat sebuah contoh file yaitu /usr/share/nginx/www/info.php yang berisi



<?php
phpinfo();
?>


Simpan konfigurasi, lalu arahkan browser ke http://localhost/info.php
Jika halaman seperti dibawah muncul, maka php telah berfungsi






















Install dukungan mysql pada php

apt-get install php5-mysql

restart php-fpm

/etc/init.d/php5-fpm restart

Arahkan browser ke http://localhost/info.php lalu scroll ke bagian modules. Jika terdapat halaman seperti dibawah, maka dukungan mysql pada php telah terpasang.