Feeds:
Posts
Comments

Archive for June, 2009

Dear All, again MySQL again…hehehehe … simple trick from me that will save your time of work when you using looping in code … yeah code … in Delphi, VB, what ever you create the aplication.

We will trying to create crosstab or whatever you will said … in MySQL …

First, saya punya data sebagai berikut :

Tabel karyawan:
 NIP   | Nama
 ------------------------
 001   | Udin tea
 002   | Budi Kahandap
 003   | Andi Kararangge
 004   | Cecep Gorbacep
 005   | Ono Ontohod 

 Tabel master_absen:
 tipe_absen | deskripsi
 ----------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI 

 Tabel absen:
 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL

Actually we want to create the result of query like this :

 NIP | NAMA            | AL | CT | SK
 ------------------------------------
 001 | Udin tea        | 2  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0
 003 | Andi Kararangge | 0  | 0  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0

Tapi … ada tapinya juga neh …. kalau di master_absen dan di data absen ditambah datanya menjadi :

 tipe_absen | deskripsi
 --------------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI
 IZ         | IZIN PULANG   <---- tambahan data

kemudian data absen jadi :

 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL
 003  | 20/07/2009 | IZ    <--- tambahan data
 004  | 21/07/2009 | IZ    <--- tambahan data

hasil yang diinginkan jadi :

 NIP | NAMA            | AL | CT | IZ | SK
 -----------------------------------------
 001 | Udin tea        | 2  | 0  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0  | 0
 003 | Andi Kararangge | 0  | 0  | 1  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0  | 0

Nah ada tambahan kolom IZ tuh ….

Sekarang, How to make the simple single query statements ? yeah … query statements …

This below is step … step by step we will describe it …

Kalau kita menggunakan sql query dengan link left join seperti biasa :

select k.nip, k.nama, a.tipe_absen, a.tgl
from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, a.tgl
order by k.nip

we got result like this :

Query 1

Nah itu baru memunculkan semua data yang ada.

Sekarang kita coba di otak atik sql nya dengan menggunakan power of group_concat dan select concat statement dan menjadi sql kita, untuk mendapatkan hasil berapa banyak si-dia absen dan tidak menghiraukan tanggal berapa saja, dan saya tidak akan menjelaskan untuk group_concat and select concat functions disini, bukan gag mau … tapi cape ngetiknya :mrgreen:, seperti dibawah ini :

select concat(
'select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, disini kita sudah menggunakan fitur statement dari MySQL, dan juga return statement ke variable @Absence_SQL dan kita akan lakukan execute Absences untuk mendapatkan resultset seperti dibawah ini :

Query 2

Tapi … eit … just a moment … we got false data …, jika dilihat … record no 2 dan 3, kan ada 002 untuk Budi Kahandap itu punya status CT dan SK …. gak mungkin untuk memunculkan data seperti ini dan diserahkan kepada yang berwajib … hehehehe :mrgreen:

So, we change again the sql structure to meet our requirements … like shown as below :

select concat(
'select T.nip, T.nama',
group_concat(concat(', sum(T.', m.tipe_absen,') As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''), '\n',
'from (select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen) As T
group by T.nip') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, so … after we concatenate the sql statements again .. what we got ???

You may see what we want … as shown below :

Query 3

Ow, … we got right what we want …. records no 2 has become one line … :mrgreen:

ck… ck… ck… just a moment … but how we added the data has been described in beginning of this post ?

Just insert the data at master_absen and data absen … and then … just running again the last sql statements, and you will get the results like this :

Query 4

Look at the columns … somethin’ differents 😎 between this result of query than before  ??? You decide :mrgreen:

Dikau friendz bisa mengubah – ubah query diatas sesuai dengan keinginan ….

Sampai jumpa di lain Query Tricky. Oh ya… don’t forget to donate :mrgreen: to make this blog live and give another tricky … :mrgreen:

Salam DBA,
😎
Man’z

Read Full Post »

Dear all, met me again …

Now, we will posting how to resolve when your MySQL Server make you headache … alias bikin sakit kepala dan mau pecah … karena MySQL menolak semua access dari all host and IP.

Actually we don’t know what this happen persist, teuing kunaon tah … langsung begitu gag mau terima koneksi dari host manapun …. dan muncul salah satu pesan seperti ini :

ERROR 1130 (HY000): Host ‘localhost’ is not allowed to connect to this MySQL server.

atawa:

ERROR 1130 (HY000): Host ‘manz’ is not allowed to connect to this MySQL server.

atawa:

ERROR 1130 (HY000): Host ‘192.168.1.200’ is not allowed to connect to this MySQL server.

But … don’t worry … be happy 😀 i already found the technique to resolve this problem … :mrgreen:

We don’t know if other site already mention …

Teknik ini saya pakai karena server saya menggunakan windows …

Langkah – langkah nya :

1. Matikan service dari MySQL :

net stop MySQL

2. Jalankan service dengan background dan skipping checking user and privilege table:

start /b mysqld –skip-grant-tables –user=root

3. Reset user table di mysql dengan memanggil mysql command:

mysql -e “insert into mysql.user(host, user, grant_priv, super_priv, ssl_cipher, x509_issuer, x509_subject) values(‘root’,’%’,’Y’,’Y’,”,”,”);”

4. Kemudian matikan MySQL yang jalan di background process tersebut:

mysqladmin -uroot shutdown

5. Jalankan service MySQL secara normal:

net start MySQL

After that , ta..da…. dikau bisa masuk ke MySQL lagi, kalo anda tidak reset password, anda bisa pake password yang lama.

Silahkan mencicipi … :mrgreen:

Salam DBA,
😎
Man’z

Read Full Post »