Listbox PHP value dari MYSQL

<?php
$koneksi_db = mysql_connect("localhost", "root", ""); //membuka koneksi ke database
mysql_select_db("db_hclab", $koneksi_db);  //proses masuk ke database
$sql = "
SELECT
  `tbl_getdata`.`instrument_id`
FROM
  `tbl_getdata`
GROUP BY
  `tbl_getdata`.`instrument_id`
";
$query = mysql_query($sql);
?>
    <select class="form-control" name="id_kategori">
        <option value=""></option>
<?php
    while($hasil = mysql_fetch_array($query)){
?>
      <option value="<?php echo $hasil["instrument_id"];?>"><?php echo $hasil["instrument_id"];?></option>
<?php
}
?>
</select>

MYSQL - Mengelompokkan Berdasarkan Tanggal Pada format DATETIME

SELECT
  date(`tbl_getdata`.`created_dt`)
FROM
  `tbl_getdata`
GROUP BY
  date(`tbl_getdata`.`created_dt`);

Instal PHPMyadmin pada Ubuntu

Buka terminal kemudian ketik
sudo apt-get install phpmyadmin


buka url ini pada browser anda
http://ip.server.anda/phpmyadmin


jika terdapat masalah ekstendi mcrypt pada konfigurasi PHP maka ketik:

sudo apt-get install php5-mcrypt
sudo ln -s /etc/php5/conf.d/mcrypt.ini /etc/php5/mods-available
sudo php5enmod mcrypt

restart Apache
sudo service apache2 restart

Instal Mysql pada Ubuntu

pada terminal ubuntu ketik

sudo apt-get install mysql-server mysql-client

anda akan diminta memasukkan password untuk user root pada mysql

New password for the MySQL "root" user: <-- password root anda
Repeat password for the MySQL "root" user: <-- password root anda harus sama dengan yg pertama untuk memastikan password yg anda masukkan benar

agar mysql dapat suport pada php5

apt-cache search php5

sudo apt-get install php5-mysql php5-curl php5-gd php5-intl php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl

kemudian restart apache

sudo /etc/init.d/apache2 restart

Sort dalam Mysql

mysql> SELECT id, title FROM tbl_images order by id desc;
+----+-------+
| id | title |
+----+-------+
|  2 |       |
|  1 |       |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT id, title FROM tbl_images order by id asc;
+----+-------+
| id | title |
+----+-------+
|  1 |       |
|  2 |       |
+----+-------+
2 rows in set (0.00 sec)

Contoh-Contoh TRIGGER dalam Mysql

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }


DELIMITER //
CREATE TRIGGER tbl_beli_subt_upd AFTER UPDATE ON tbl_beli
FOR EACH ROW
BEGIN

IF new.harga_beli <=> old.harga_beli and new.jumlah <=> old.jumlah THEN
UPDATE tbl_beli SET sub_total = (new.harga_beli * new.jumlah) WHERE id_beli = old.id_beli;
END IF;

IF new.harga_beli = old.harga_beli and new.jumlah <=> old.jumlah THEN
UPDATE tbl_beli SET sub_total = (old.harga_beli * new.jumlah) WHERE id_beli = old.id_beli;
END IF;

IF new.harga_beli <=> old.harga_beli and new.jumlah = old.jumlah THEN
UPDATE tbl_beli SET sub_total = (new.harga_beli * old.jumlah) WHERE id_beli = old.id_beli;
END IF;

END;
// 
DELIMITER;


DELIMITER //
CREATE TRIGGER upd_sbt AFTER INSERT ON tbl_beli
FOR EACH ROW
BEGIN
set @id_beli = new.id_beli;
set @harga_beli = new.harga_beli;
set @jumlah = new.jumlah;
set @sub_total = @harga_beli * @jumlah;
UPDATE tbl_beli SET sub_total = @sub_total WHERE id_beli = @id_beli
END;
// 
DELIMITER;



drop trigger trig_transaksi;
DELIMITER //
CREATE TRIGGER trig_transaksi after INSERT ON tbl_transaksi FOR EACH ROW
BEGIN
IF new.ktg_transaksi = 'Penjualan' THEN 
UPDATE tbl_barang SET stok = stok - new.jmlh WHERE kode = new.kode_brg;
END IF;
IF new.ktg_transaksi = 'Pembelian' THEN 
UPDATE tbl_barang SET stok = stok + new.jmlh, hrgbli = new.hrgbli WHERE kode = new.kode_brg;
END IF;
END 
// 
DELIMITER ;


drop trigger trig_del_transaksi;
DELIMITER //
CREATE TRIGGER trig_del_transaksi after DELETE ON tbl_transaksi FOR EACH ROW
BEGIN
IF old.ktg_transaksi = 'Penjualan' THEN 
UPDATE tbl_barang SET stok = stok + old.jmlh WHERE kode = old.kode_brg;
END IF;
IF old.ktg_transaksi = 'Pembelian' THEN 
UPDATE tbl_barang SET stok = stok - old.jmlh WHERE kode = old.kode_brg;
END IF;
END 
// 
DELIMITER ;

drop trigger trig_upd_transaksi;
DELIMITER //
CREATE TRIGGER trig_upd_transaksi after UPDATE ON tbl_transaksi FOR EACH ROW
BEGIN
IF new.ktg_transaksi = 'Penjualan' THEN 
UPDATE tbl_barang SET stok = stok + old.jmlh - new.jmlh WHERE kode = new.kode_brg;
END IF;
IF new.ktg_transaksi = 'Pembelian' THEN 
UPDATE tbl_barang SET stok = stok - old.jmlh + new.jmlh, hrgbli = new.hrgbli WHERE kode = new.kode_brg;
END IF;
END 
// 
DELIMITER ;


drop trigger trig_kwi;
DELIMITER //
CREATE TRIGGER trig_kwi after INSERT ON tbl_kwi FOR EACH ROW
BEGIN
UPDATE tbl_kwi_det SET tgl_byr = new.tgl_byr WHERE kwitansi = new.kwitansi;
END 
// 
DELIMITER ;

drop trigger trig_kwi_lun;
DELIMITER //
CREATE TRIGGER trig_kwi_lun after UPDATE ON tbl_kwi_det FOR EACH ROW
BEGIN
UPDATE tbl_nota SET status_pembayaran = 'Lunas',  tgl_byr = new.tgl_byr WHERE nota = old.nota;
END 
// 
DELIMITER ;

DELIMITER //
CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
BEGIN
INSERT INTO tbl_gudang (id_barang) value (NEW.id_barang);
END 
// 
DELIMITER ;


DELIMITER //
CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
BEGIN

declare pencacah int;
set pencacah = 0;
select pencacah;
ulang : LOOP
set pencacah = pencacah + 1;
if pencacah > 10 then leave ulang; end if;
END LOOP ulang;
select pencacah;

END 
// 
DELIMITER ;




DELIMITER //
CREATE TRIGGER trig_transaksi BEFORE UPDATE ON tbl_beli FOR EACH ROW
BEGIN
declare x int;
declare n int;
set x = 0;
SET n = new.jumlah;
ulang : LOOP
set x = x + 1;
if x > n then leave ulang; end if;
INSERT INTO tbl_gudang (id_barang) value (NEW.id_barang);
END LOOP ulang;
END 
// 
DELIMITER ;








DELIMITER //
CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
BEGIN
declare x int;
declare n int;
set x = 0;
SET n = new.jumlah;
if new.status = 1 then
ulang : LOOP
set x = x + 1;
if x > n then leave ulang; end if;
INSERT INTO tbl_gudang (id_barang,tanggal_kedaluarsa,id_cabang,id_beli,harga_beli,laba,ppn,harga_jual) value (new.id_barang,new.tanggal_kedaluarsa,'GDG',new.id_beli,new.harga_beli,new.laba,new.ppn,new.harga_jual);
END LOOP ulang;

end if;
END 
// 
DELIMITER ;


====================================================================
drop trigger post_beli;
DELIMITER //
CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
BEGIN
declare x int;
declare n int;
set x = 0;
SET n = new.jumlah;
if new.status = 1 then
ulang : LOOP
set x = x + 1;
if x > n then leave ulang; end if;
INSERT INTO tbl_stok (id_barang,id_beli,harga_beli,laba,ppn,harga_jual,id_cabang) value (new.id_barang,new.id_beli,new.harga_beli,new.laba,new.ppn,new.harga_jual,new.id_cabang);
END LOOP ulang;

end if;
END 
// 
DELIMITER ;
====================================================================
drop trigger post_beli_nota;
DELIMITER //
CREATE TRIGGER post_beli_nota AFTER INSERT ON tbl_beli FOR EACH ROW
BEGIN
INSERT INTO UPDATE (status) value (new.id_barang,new.id_beli,new.harga_beli,new.laba,new.ppn,new.harga_jual,new.id_cabang);
END 
// 
DELIMITER ;
====================================================================
drop trigger post_jual;
DELIMITER //
CREATE TRIGGER post_jual AFTER INSERT ON tbl_jual FOR EACH ROW
BEGIN
declare n int;
SET n = new.jumlah;
UPDATE tbl_stok SET id_jual=new.id_jual
     WHERE id_stok IN (
         SELECT id_stok FROM (
             SELECT id_stok FROM tbl_stok 
             WHERE
             id_jual is NULL AND id_barang = new.id_barang
             ORDER BY id_stok ASC  
             LIMIT 0, n
         ) tmp
     );
END 
// 
DELIMITER ;
====================================================================
drop trigger post_jual_del;
DELIMITER //
CREATE TRIGGER post_jual_del AFTER DELETE ON tbl_jual FOR EACH ROW
BEGIN
UPDATE tbl_stok SET id_jual=NULL WHERE id_jual = old.id_jual;
END 
// 
DELIMITER ;
====================================================================
drop trigger post_jual_upd;
DELIMITER //
CREATE TRIGGER post_jual_upd AFTER UPDATE ON tbl_jual FOR EACH ROW
BEGIN
declare jumlah_lama int;
declare jumlah_baru int;
declare tambah int;
declare kurang int;
SET jumlah_lama = old.jumlah;
SET jumlah_baru = new.jumlah;
SET tambah = jumlah_baru - jumlah_lama;
SET kurang = jumlah_lama - jumlah_baru;
if jumlah_lama > jumlah_baru then
UPDATE tbl_stok SET id_jual=NULL
     WHERE id_stok IN (
         SELECT id_stok FROM (
             SELECT id_stok FROM tbl_stok 
             WHERE
             id_jual = old.id_jual AND id_barang = old.id_barang
             ORDER BY id_stok DESC  
             LIMIT 0, kurang
         ) tmp
     );
end if;
if jumlah_lama < jumlah_baru then
UPDATE tbl_stok SET id_jual=new.id_jual
     WHERE id_stok IN (
         SELECT id_stok FROM (
             SELECT id_stok FROM tbl_stok 
             WHERE
             id_jual is NULL AND id_barang = old.id_barang
             ORDER BY id_stok ASC  
             LIMIT 0, tambah
         ) tmp
     );

end if;
END 
// 
DELIMITER ;
====================================================================
drop trigger post_piutang_nota;
DELIMITER //
CREATE TRIGGER post_piutang_nota AFTER INSERT ON tbl_piutang_nota FOR EACH ROW
BEGIN
UPDATE tbl_piutang_bayar SET status = 1 WHERE id_piutang_nota = new.id_piutang_nota;
End
// 
DELIMITER ;
====================================================================
drop trigger post_hutang_nota;
DELIMITER //
CREATE TRIGGER post_hutang_nota AFTER INSERT ON tbl_hutang_nota FOR EACH ROW
BEGIN
UPDATE tbl_hutang_bayar SET status = 1 WHERE id_hutang_nota = new.id_hutang_nota;
End
// 
DELIMITER ;

Undo dalam Mysql

KOde mulai

BEGIN;
INSERT INTO tbl_piutang_bayar (id_jual_nota, id_piutang_nota) VALUES ('P001-00000001', 'J001-00000001'), ('P001-00000001', 'J001-00000003'), ('P001-00000001', 'J001-00000004');

untuk Undo

ROLLBACK;

untuk tidak undo

COMMIT; 

Variasi Perintah INSERT dalam Mysql

Bentuk Dasar Perintah INSERT

INSERT INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN');

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

INSERT INTO tbl_barang (kode_barang, nama_barang) VALUES('BRG001', 'AYAM BAKAR');

#2. INSERT ... SELECT ...

    INSERT INTO tbl_jual(nama_barang, kode_barang) SELECT kode_barang, nama_barang FROM tbl_barang;

#3. INSERT IGNORE

    INSERT IGNORE INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN');

#4. INSERT DELAYED

    INSERT DELAYED INTO tbl_barang (nama_barang, kode_barang) VALUES ('BRG001', 'AYAM BAKAR');

#5. INSERT LOW PRIORITY | HIGH PRIORITY

    INSERT INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR');

#6. INSERT ... ON DUPLICATE KEY UPDATE

    INSERT INTO tbl_jual (nama_barang, kode_barang, kategori) VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN') ON DUPLICATE KEY UPDATE kategori='MAKANAN';

#7. REPLACE INTO ...

    REPLACE INTO tbl_barang VALUES('BRG001', 'AYAM BAKAR', 'MAKANAN');

Select dalam Select Mysql

select sum(keranjang.subtotal) as sub_total from (select id_beli_nota, harga_beli, jumlah, (harga_beli * jumlah) as subtotal from tbl_beli where id_beli_nota = 'BLI-000000001') keranjang group by keranjang.id_beli_nota

Update dengan limit Mysql

UPDATE table_name SET name='test'
     WHERE id IN (
         SELECT id FROM (
             SELECT id FROM table_name 
             ORDER BY id ASC  
             LIMIT 0, 10
         ) tmp
     );

Menghilangkan Data Duplikat

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]  FROM table

Mengambil id terakhir pada field AUTO INCREMENT Mysql

SELECT LAST_INSERT_ID();

Membuat User Baru Pada Mysql Server

mysql> use mysql;
mysql> insert into user (host,user,password,select_priv) values('localhost','kopkar', password('123123'),'Y');
mysql> flush privileges;

Mengubah NULL menjadi 0 pada Mysql

IFNULL(field,0)

Format Tanggal pada Mysql

DATE_FORMAT(date,format)


Format   | Description
%a  | Abbreviated weekday name
%b  | Abbreviated month name
%c  | Month, numeric
%D  | Day of month with English suffix
%d  | Day of month, numeric (00-31)
%e  | Day of month, numeric (0-31)
%f  | Microseconds
%H  | Hour (00-23)
%h  | Hour (01-12)
%I  | Hour (01-12)
%i  | Minutes, numeric (00-59)
%j  | Day of year (001-366)
%k  | Hour (0-23)
%l  | Hour (1-12)
%M  | Month name
%m  | Month, numeric (00-12)
%p  | AM or PM
%r  | Time, 12-hour (hh:mm:ss AM or PM)
%S  | Seconds (00-59)
%s  | Seconds (00-59)
%T  | Time, 24-hour (hh:mm:ss)
%U  | Week (00-53) where Sunday is the first day of week
%u  | Week (00-53) where Monday is the first day of week
%V  | Week (01-53) where Sunday is the first day of week, used with %X
%v  | Week (01-53) where Monday is the first day of week, used with %x
%W  | Weekday name
%w  | Day of the week (0=Sunday, 6=Saturday)
%X  | Year of the week where Sunday is the first day of week, four digits, used with %V
%x  | Year of the week where Monday is the first day of week, four digits, used with %v
%Y  | Year, four digits
%y  | Year, two digits

Date and Time Functions

mysql> SELECT something FROM tbl_name

    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

=============================================================

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');

        -> 0, 0

=============================================================

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);

        -> NULL

mysql> SELECT DAYNAME('2006-05-00');

        -> NULL

=============================================================

mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);

        -> '2008-02-02'

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);

        -> '2008-02-02'

=============================================================

mysql> SELECT ADDDATE('2008-01-02', 31);

        -> '2008-02-02'

=============================================================

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

        -> '2008-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

        -> '03:00:01.999997'

=============================================================

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

        -> '2004-01-01 22:00:00'

=============================================================

mysql> SELECT CURDATE();

        -> '2008-06-13'

mysql> SELECT CURDATE() + 0;

        -> 20080613

=============================================================

Laporan Tahunan di Mysql - Pivot Table

Jumpa lagi di catatan fey. Kali ini saya akan menambahkan sebuat catatan baru  mengenai Pivot Tabel di MySql, biasanya pivot table sangat mudah di gunakan di Microsoft Excel. nah kali ini kita akan mencobanya di MySql.