Berikut cara mengkonversi nilai ke Rupiah dari Mysql
Selamat Mencoba …:D
CMIIW…:D
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
| /*** Konversi Nilai Rupiah ***/DELIMITER $$DROP FUNCTION IF EXISTS `tom`.`rupiah`$$CREATE DEFINER=`root`@`localhost` FUNCTION `rupiah`(m decimal(18,2)) RETURNS varchar(254) CHARSET utf8BEGIN /*------ WORD LIST (TELLING WORDS FROM 1 TO 100) ------*/set @ie=concat("satu",SPACE(19),"dua",SPACE(20),"tiga",SPACE(19),"empat",SPACE(18),"lima",SPACE(19),"enam",SPACE(19),"tujuh",SPACE(18),"delapan",SPACE(16),"sembilan",SPACE(15));set @o=concat("sepuluh",SPACE(16),"sebelas",SPACE(16),"duabelas",SPACE(15),"tigabelas",SPACE(14),"empatbelas",SPACE(13),"limabelas",SPACE(14),"enambelas",SPACE(14),"tujuhbelas",SPACE(13),"delapanbelas",SPACE(11),"sembilanbelas",SPACE(10));set @san=concat("duapuluh",SPACE(15),"duapuluh satu",SPACE(10),"duapuluh dua",SPACE(11),"duapuluh tiga",SPACE(10),"duapuluh empat",SPACE(9),"duapuluh lima",SPACE(10),"duapuluh enam",SPACE(10),"duapuluh tujuh",SPACE(9),"duapuluh delapan",SPACE(7),"duapuluh sembilan",SPACE(6));set @se=concat("tigapuluh",SPACE(14),"tigapuluh satu",SPACE(9),"tigapuluh dua",SPACE(10),"tigapuluh tiga",SPACE(9),"tigapuluh empat",SPACE(8),"tigapuluh lima",SPACE(9),"tigapuluh enam",SPACE(9),"tigapuluh tujuh",SPACE(8),"tigapuluh delapan",SPACE(6),"tigapuluh sembilan",SPACE(5));set @u=concat("empatpuluh",SPACE(13),"empatpuluh satu",SPACE(8),"empatpuluh dua",SPACE(9),"empatpuluh tiga",SPACE(8),"empatpuluh empat",SPACE(7),"empatpuluh lima",SPACE(8),"empatpuluh enam",SPACE(8),"empatpuluh tujuh",SPACE(7),"empatpuluh delapan",SPACE(5),"empatpuluh sembilan",SPACE(4));set @liuk=concat("limapuluh",SPACE(14),"limapuluh satu",SPACE(9),"limapuluh dua",SPACE(10),"limapuluh tiga",SPACE(9),"limapuluh empat",SPACE(8),"limapuluh lima",SPACE(9),"limapuluh enam",SPACE(9),"limapuluh tujuh",SPACE(8),"limapuluh delapan",SPACE(6),"limapuluh sembilan",SPACE(5));set @chi=concat("enampuluh",SPACE(14),"enampuluh satu",SPACE(9),"enampuluh dua",SPACE(10),"enampuluh tiga",SPACE(9),"enampuluh empat",SPACE(8),"enampuluh lima",SPACE(9),"enampuluh enam",SPACE(9),"enampuluh tujuh",SPACE(8),"enampuluh delapan",SPACE(6),"enampuluh sembilan",SPACE(5));set @pat=concat("tujuhpuluh",SPACE(13),"tujuhpuluh satu",SPACE(8),"tujuhpuluh dua",SPACE(9),"tujuhpuluh tiga",SPACE(8),"tujuhpuluh empat",SPACE(7),"tujuhpuluh lima",SPACE(8),"tujuhpuluh enam",SPACE(8),"tujuhpuluh tujuh",SPACE(7),"tujuhpuluh delapan",SPACE(5),"tujuhpuluh sembilan",SPACE(4));set @qiu=concat("delapanpuluh",SPACE(11),"delapanpuluh satu",SPACE(6),"delapanpuluh dua",SPACE(7),"delapanpuluh tiga",SPACE(6),"delapanpuluh empat",SPACE(5),"delapanpuluh lima",SPACE(6),"delapanpuluh enam",SPACE(6),"delapanpuluh tujuh",SPACE(5),"delapanpuluh delapan",SPACE(3),"delapanpuluh sembilan",SPACE(2));set @sip=concat("sembilanpuluh",SPACE(10),"sembilanpuluh satu",SPACE(5),"sembilanpuluh dua",SPACE(6),"sembilanpuluh tiga",SPACE(5),"sembilanpuluh empat",SPACE(4),"sembilanpuluh lima",SPACE(5),"sembilanpuluh enam",SPACE(5),"sembilanpuluh tujuh",SPACE(4),"sembilanpuluh delapan",SPACE(2),"sembilanpuluh sembilan",SPACE(1),"seratus",SPACE(16));set @tom=trim(concat(@ie,@o,@san,@se,@u,@liuk,@chi,@pat,@qiu,@sip)); /*------------------ WORD STRUCTURE ---------------------*/set @a=cast(m as unsigned);set @b=cast(@a as char);set @x=trim(@b);set @len=length(@x);set @a=LENGTH(@x);set @aa=LENGTH(@x)-1;set @aaa=LENGTH(@x)-2;set @aaaa=LENGTH(@x)-3;set @aaaaa=LENGTH(@x)-4;set @aaaaaa=LENGTH(@x)-5;set @aaaaaaa=LENGTH(@x)-6;set @aaaaaaaa=LENGTH(@x)-7;set @aaaaaaaaa=LENGTH(@x)-8;set @aaaaaaaaaa=LENGTH(@x)-9;set @aaaaaaaaaaa=LENGTH(@x)-10;set @aaaaaaaaaaaa=LENGTH(@x)-11;set @aaaaaaaaaaaaa=LENGTH(@x)-12;set @aaaaaaaaaaaaaa=LENGTH(@x)-13;set @csat=trim(substr(@x,@a,1));set @cpul=trim(substr(@x,@aa,2));set @crat=trim(substr(@x,@aaa,1));set @crib=trim(substr(@x,@aaaa,1));set @cpulrib=trim(substr(@x,@aaaaa,2));set @cratrib=trim(substr(@x,@aaaaaa,1));set @cjut=trim(substr(@x,@aaaaaaa,1));set @cpuljut=trim(substr(@x,@aaaaaaaa,2));set @cratjut=trim(substr(@x,@aaaaaaaaa,1));set @cmil=trim(substr(@x,@aaaaaaaaaa,1));set @cpulmil=trim(substr(@x,@aaaaaaaaaaa,2));set @cratmil=trim(substr(@x,@aaaaaaaaaaaa,1));set @ctrilyun=trim(substr(@x,@aaaaaaaaaaaaa,1));set @sat=cast(@csat as unsigned);set @pul=cast(@cpul as unsigned);set @rat=cast(@crat as unsigned);set @rib=cast(@crib as unsigned);set @pulrib=cast(@cpulrib as unsigned);set @ratrib=cast(@cratrib as unsigned);set @jut=cast(@cjut as unsigned);set @puljut=cast(@cpuljut as unsigned);set @ratjut=cast(@cratjut as unsigned);set @mil=cast(@cmil as unsigned);set @pulmil=cast(@cpulmil as unsigned);set @ratmil=cast(@cratmil as unsigned);set @trilyun=cast(@ctrilyun as unsigned); /*--------------------- PARSER --------------------------*/if @sat>0 thenset @tsat=trim(substr(@tom,(@sat-1)*23+1,23));elseset @tsat="";end if;if @pul>0 thenset @tpul=trim(substr(@tom,(@pul-1)*23+1,23));set @tsat="";elseset @tpul="";end if;if @rat>1 and @len>2 thenset @trat=concat(trim(substr(@tom,((@rat)-1)*23+1,23)),"ratus");elseif @rat=1 and @len>2 thenset @trat="seratus";elseset @trat="";end if;if @rib>1 and @len>3 thenset @trib=concat(trim(substr(@tom,(@rib-1)*23+1,23)),"ribu");elseif @rib=1 and @len>3 thenset @trib="seribu";elseset @trib="";end if;if @pulrib>0 and @len>4 thenset @tpulrib=concat(trim(substr(@tom,(@pulrib-1)*23+1,23))," ribu");set @trib="";elseset @tpulrib="";end if;if @ratrib>1 and @pulrib>0 and @len>5 thenset @tratrib=concat(trim(substr(@tom,(@ratrib-1)*23+1,23)),"ratus");elseif @ratrib=1 and @pulrib>0 and @len>5 thenset @tratrib="seratus";elseif @ratrib>1 and @ratrib<>1 and @pulrib=0 and @rib=0 and @len>5 thenset @tratrib=concat(trim(substr(@tom,(@ratrib-1)*23+1,23)),"ratus ribu");elseif @ratrib=1 and @pulrib=0 and @rib=0 and @len>5 thenset @tratrib="seratus ribu";elseset @tratrib="";end if;if @jut>0 and @len>6 thenset @tjut=concat(trim(substr(@tom,(@jut-1)*23+1,23))," juta");elseset @tjut="";end if;if @puljut>0 and @len>7 thenset @tpuljut=concat(trim(substr(@tom,(@puljut-1)*23+1,23))," juta");set @tjut="";elseset @tpuljut="";end if;if @ratjut>1 and @puljut>0 and @len>8 thenset @tratjut=concat(trim(substr(@tom,(@ratjut-1)*23+1,23)),"ratus");elseif @ratjut=1 and @puljut>0 and @len>8 thenset @tratjut="seratus";elseif @ratjut>1 and @puljut=0 and @jut=0 and @len> 8 thenset @tratjut=concat(trim(substr(@tom,(@ratjut-1)*23+1,23)),"ratus juta");elseif @ratjut=1 and @puljut=0 and @jut=0 and @len> 8 thenset @tratjut="seratus juta";elseset @tratjut="";end if;if @mil > 0 and @len>9 thenset @tmil=concat(trim(substr(@tom,((@mil)-1)*23+1,23))," milyar");elseset @tmil="";end if;if @pulmil >0 and @len>10 thenset @tpulmil=concat(trim(substr(@tom,((@pulmil)-1)*23+1,23))," milyar");set @tmil="";elseset @tpulmil="";end if;if @ratmil>1 and @pulmil>0 and @len>11 thenset @tratmil=concat(trim(substr(@tom,((@ratmil)-1)*23+1,23)),"ratus");elseif @ratmil=1 and @pulmil>0 and @len>11 thenset @tratmil="seratus";elseif @ratmil>1 and @pulmil=0 and @mil=0 and @len>11 thenset @tratmil=concat(trim(substr(@tom,((@ratmil)-1)*23+1,23)),"ratus milyar");elseif @ratmil=1 and @pulmil=0 and @mil=0 and @len>11 thenset @tratmil="seratus milyar";elseset @tratmil="";end if;if @tril>0 and @len> 12 thenset @ttril=concat(trim(substr(@tom,((@trilyun)-1)*23+1,23))," trilyun");elseset @ttril="";end if;set @fraction=format(m,2);set @cfraction=concat(@fraction);set @lrfraction=right(@cfraction,2);set @lfraction=left(@lrfraction,1);set @rfraction=right(@lrfraction,1);set @lf=cast(@lfraction as unsigned);set @rf=cast(@rfraction as unsigned);if @lf>0 thenset @tlf=trim(substr(@tom,(@lf-1)*23+1,23));elseset @tlf="nol";end if;if @rf>0 thenset @trf=trim(substr(@tom,(@rf-1)*23+1,23));elseset @trf="nol";end if;set @comma=(select case when @lrfraction="00" then "" else "koma" end);if @lrfraction="00" thenset @tlf="";elseset @tlf=concat(space(1),@tlf,space(1));end if;if @lrfraction="00" thenset @trf="";elseset @trf=concat(@trf,space(1));end if;set @myword=trim(concat(@ttril,space(1),@tratmil,@tpulmil,@tmil,space(1),@tratjut,@tpuljut,@tjut,space(1),@tratrib,@tpulrib,@trib,space(1),@trat,@tpul,@tsat,space(1),@comma,@tlf,@trf,"rupiah"));return ucase(@myword); END$$DELIMITER ;/* Caranya , ketikkan: *//* select rupiah(95107391054.12); */ |
CMIIW…:D
0
Contoh menghitung Sebuah Kata dalam Kalimat
Selamat Mencoba 
CMIIW….
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| /*** Menghitung Kata ***/DELIMITER $$DROP FUNCTION IF EXISTS `ztom`.`wordcount`$$CREATE FUNCTION `ztom`.`wordcount`(find char(30),word text(300))RETURNS integer LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER COMMENT 'WordCount' BEGINset @lenstr=length(trim(word));set @lenfind=length(trim(find));set @lenclearfind=length(replace(lcase(word),find,''));set @formula=(select((@lenstr-@lenclearfind)/@lenfind));return @formula; END$$DELIMITER ;/*
Untuk dapat menggunakan fungsi wordcount ini, Anda harus menuliskan
kata yang hendak dicari menggunakan huruf kecil (lowercase). Cara
penggunaannya, ketikkan:select wordcount(‘kata yang dicari’,’kalimat yang digunakan’) */ |
CMIIW….
Sebenarnya banyak cara untuk menghitung umur, disini salah satu contoh menghitung umur di database mysql
Selamat Mencoba 
CMIIW….
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| /*** Umur ***/DELIMITER $$DROP FUNCTION IF EXISTS `MYSQL`.`age`$$CREATE FUNCTION `MYSQL`.`age`(tgllahir date,tglskrg date)RETURNS integer BEGIN RETURN FROM_DAYS(TO_DAYS(tglskrg) - TO_DAYS(tgllahir)); END$$DELIMITER ;/* select age(‘tgl lahir’,’tgl sekarang’); */ |
CMIIW….
Contoh Select Query MySql di PHP
Posted: December 4, 2010 in Mysql, Script LanjutanTags: mysql php, query mysql, sample query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| $host = "localhost";$user = "..."; # masukkan nama user database yang sebenarnya di sini$pass = "..."; # masukkan password database yang sebenarnya di sini$db = "test"; # masukkan nama database yang sebenarnya di sini# pertama, konek. nama variabel $conn di sini singkatan dari# "connection" (tentu saja anda bebas memakai nama lain)$conn = mysql_connect($host, $user, $pass) or die("Gagal konek!");# kedua, pilih DB. argumen kedua $conn umumnya tidak perlu disebutkan# lagi kecuali jika ada lebih dari satu koneksi yang anda lakukanmysql_select_db($db, $conn) or die("Gagal memilih database $db: ".mysql_error());# ketiga, lakukan query. nama variabel $res di sini singkatan dari # "result" (tentu saja anda bebas memakai nama lain). argumen kedua # $conn umumnya tidak perlu disebutkan lagi kecuali jika ada lebih # dari satu koneksi yang aktif$res = mysql_query("SELECT * FROM orang", $conn) or die("Gagal query: ".mysql_error($conn));# keempat, ambil baris-baris hasil$rows = array();while ($row = mysql_fetch_assoc($res)) $rows[] = $row;# terakhir, tampilkan (atau proses hasil, dsb)print_r($rows); |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| $host = ":/tmp/mysql413.sock";$user = "root"; # masukkan nama user database yang sebenarnya di sini$pass = ""; # masukkan password database yang sebenarnya di sini$db = "test"; # masukkan nama database yang sebenarnya di sini# pertama, konek. nama variabel $conn di sini singkatan dari# "connection" (tentu saja anda bebas memakai nama lain)$conn = mysql_connect($host, $user, $pass) or die("Gagal konek!");# kedua, pilih DB. argumen kedua $conn umumnya tidak perlu disebutkan# lagi kecuali jika ada lebih dari satu koneksi yang anda lakukanmysql_select_db($db, $conn) or die("Gagal memilih database $db: ".mysql_error());# ketiga, lakukan query. nama variabel $res di sini singkatan dari # "result" (tentu saja anda bebas memakai nama lain). argumen kedua # $conn umumnya tidak perlu disebutkan lagi kecuali jika ada lebih # dari satu koneksi yang aktif$res = mysql_query("SELECT * FROM orang", $conn) or die("Gagal query: ".mysql_error($conn));# keempat, ambil baris-baris hasil$rows = array();while ($row = mysql_fetch_assoc($res)) $rows[] = $row;# terakhir, tampilkan (atau proses hasil, dsb)echo "<table cellpadding=5 border=1>";echo "<tr bgcolor=#cccccc> <th>ID</th> <th>Nama depan</th> <th>Nama belakang</th> <th>Tanggal lahir</th></tr>";$i = 0;foreach ($rows as $row) { $bgcolor = $i++ % 2 ? "#f0f0f0" : "#ffffff"; echo "<tr bgcolor=$bgcolor> <td>$row[id]</td> <td>$row[nama_depan]</td> <td>$row[nama_belakang]</td> <td>$row[tanggal_lahir]</td> </tr>";}echo "</table>"; |
0 Comments