KOLEKSI SOFTWARE & MOVIE

Berikut cara mengkonversi nilai ke Rupiah dari Mysql

/*** Konversi Nilai Rupiah ***/

DELIMITER $$

DROP FUNCTION IF EXISTS `tom`.`rupiah`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `rupiah`(

m decimal(18,2)) RETURNS varchar(254) CHARSET utf8

BEGIN

   /*------ 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 then

set @tsat=trim(substr(@tom,(@sat-1)*23+1,23));

else

set @tsat="";

end if;

if @pul>0 then

set @tpul=trim(substr(@tom,(@pul-1)*23+1,23));

set @tsat="";

else

set @tpul="";

end if;

if @rat>1 and @len>2 then

set @trat=concat(trim(substr(@tom,((@rat)-1)*23+1,23)),"ratus");

elseif @rat=1 and @len>2 then

set @trat="seratus";

else

set @trat="";

end if;

if @rib>1 and @len>3 then

set @trib=concat(trim(substr(@tom,(@rib-1)*23+1,23)),"ribu");

elseif @rib=1 and @len>3 then

set @trib="seribu";

else

set @trib="";

end if;

if @pulrib>0 and @len>4 then

set @tpulrib=concat(trim(substr(@tom,(@pulrib-1)*23+1,23))," ribu");

set @trib="";

else

set @tpulrib="";

end if;

if @ratrib>1 and @pulrib>0 and @len>5 then

set @tratrib=concat(trim(substr(@tom,(@ratrib-1)*23+1,23)),"ratus");

elseif @ratrib=1 and @pulrib>0 and @len>5 then

set @tratrib="seratus";

elseif @ratrib>1 and @ratrib<>1 and @pulrib=0 and @rib=0 and @len>5 then

set @tratrib=concat(trim(substr(@tom,(@ratrib-1)*23+1,23)),"ratus ribu");

elseif @ratrib=1 and @pulrib=0 and @rib=0 and @len>5 then

set @tratrib="seratus ribu";

else

set @tratrib="";

end if;

if @jut>0 and @len>6 then

set @tjut=concat(trim(substr(@tom,(@jut-1)*23+1,23))," juta");

else

set @tjut="";

end if;

if @puljut>0 and @len>7 then

set @tpuljut=concat(trim(substr(@tom,(@puljut-1)*23+1,23))," juta");

set @tjut="";

else

set @tpuljut="";

end if;

if @ratjut>1 and @puljut>0 and @len>8  then

set @tratjut=concat(trim(substr(@tom,(@ratjut-1)*23+1,23)),"ratus");

elseif @ratjut=1 and @puljut>0 and @len>8 then

set @tratjut="seratus";

elseif @ratjut>1 and @puljut=0 and @jut=0 and @len> 8 then

set @tratjut=concat(trim(substr(@tom,(@ratjut-1)*23+1,23)),"ratus juta");

elseif @ratjut=1 and @puljut=0 and @jut=0 and @len> 8 then

set @tratjut="seratus juta";

else

set @tratjut="";

end if;

if @mil > 0 and @len>9 then

set @tmil=concat(trim(substr(@tom,((@mil)-1)*23+1,23))," milyar");

else

set @tmil="";

end if;

if @pulmil >0 and @len>10 then

set @tpulmil=concat(trim(substr(@tom,((@pulmil)-1)*23+1,23))," milyar");

set @tmil="";

else

set @tpulmil="";

end if;

if @ratmil>1 and @pulmil>0 and @len>11 then

set @tratmil=concat(trim(substr(@tom,((@ratmil)-1)*23+1,23)),"ratus");

elseif @ratmil=1 and @pulmil>0 and @len>11 then

set @tratmil="seratus";

elseif @ratmil>1 and @pulmil=0 and @mil=0 and @len>11 then

set @tratmil=concat(trim(substr(@tom,((@ratmil)-1)*23+1,23)),"ratus milyar");

elseif @ratmil=1 and @pulmil=0 and @mil=0 and @len>11 then

set @tratmil="seratus milyar";

else

set @tratmil="";

end if;

if @tril>0 and @len> 12 then

set @ttril=concat(trim(substr(@tom,((@trilyun)-1)*23+1,23))," trilyun");

else

set @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 then

set @tlf=trim(substr(@tom,(@lf-1)*23+1,23));

else

set @tlf="nol";

end if;

if @rf>0 then

set @trf=trim(substr(@tom,(@rf-1)*23+1,23));

else

set @trf="nol";

end if;

set @comma=(select case when @lrfraction="00" then "" else "koma" end);

if @lrfraction="00" then

set @tlf="";

else

set @tlf=concat(space(1),@tlf,space(1));

end if;

if @lrfraction="00" then

set @trf="";

else

set @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);  */

Selamat Mencoba …:D
CMIIW…:D

Menghitung Kata dengan Mysql

Posted: December 4, 2010 in Mysql
Tags: count text, mysql

Contoh menghitung Sebuah Kata dalam Kalimat

/*** 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'

    BEGIN

set @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’)    */

Selamat Mencoba

CMIIW….

Menghitung Umur dengan Mysql

Posted: December 4, 2010 in Mysql

Sebenarnya banyak cara untuk menghitung umur, disini salah satu contoh menghitung umur di database mysql

/*** 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’); */

Selamat Mencoba

CMIIW….

Contoh Select Query MySql di PHP

Posted: December 4, 2010 in Mysql, Script Lanjutan
Tags: mysql php, query mysql, sample query

contoh simple query mysql di PHP bisa menggunakan contoh dibawah ini

$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 lakukan

mysql_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);

dan bisa juga menggunakan contoh seperti ini

$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 lakukan

mysql_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>";