Ayo Berbagi Pengetahuan .... !!!: Mysql

Showing posts with label Mysql. Show all posts

Thursday, July 18, 2019

LOAD MANY TEXT FILE TO MYSQL TABLE

#01.5 the script executed from out of mysql with run file coba.bat
All file: bat file & txt file stored in folder c:\xampp\mysql\bin

echo on
setlocal enabledelayedexpansion
FOR %%f IN ("*.txt") DO (
mysql -e "LOAD DATA local INFILE '"%%f"' INTO TABLE tabelname FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'" -u root --password=password DBname
DONE
)

Thursday, December 26, 2013

connetion ODBC to MYSQL Linux error: Connection Failed: [HY000] ][MySQL][ODBC 3.51 Driver] Can't connect to MySQL server on ..

error message on ODBC:
Connection Failed: [HY000][MySQL][ODBC 3.51 Driver] Can't connect to MySQL server on '192.168.100.3'(10061)

environment:
Server: Linux + MySQL
Client: Windows 7 + VB6 + ODBC connection

Solution:
follow below steps:
(1) go to your MySQL Server:
      edit on file : /etc/MySQL/my.cnf
      changes: bind-address = 127.0.0.1
      become to : bind-address = 0.0.0.0

(2) go to MySQL database
      Privileges:
      add user root
    host : any host or IP your PC
      password
      Global privileges: grant all table

# restart the server after finish


the issue due to authentification issue, only access from localhost then changes to access from others host.

Saturday, July 21, 2012

melihat view di mysql

Berikut script untuk melihat view dan lainnya pada database MYSQL:
select TABLE_NAME from information_schema.VIEWS where TABLE_SCHEMA = 'mysql';

jika ingin melihat script lengkapnya, pakai berikut:

SHOW CREATE VIEW your_view_name

dan pilih print view di button bawah

Monday, June 11, 2012

script to create date range in MySQL

below quer to create date range in mysql.

usually used for create working calender for employee attendance

DROP PROCEDURE IF EXISTS datespopulate;

DELIMITER |

CREATE PROCEDURE datespopulate(dateStart DATE, dateEnd DATE)

BEGIN

  WHILE dateStart <= dateEnd DO

    INSERT INTO datetable (d) VALUES (dateStart);

    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

  END WHILE;

END;

|

DELIMITER ;

CALL datespopulate('2010-11-01','2015-01-01');

Note I named my table "datetable" and the column is named "d", but feel free to change this. Works fine on my end, let me know if you run in to an issue.

source from : http://stackoverflow.com/questions/4736690/create-mysql-date-range

timezone mysql & PHP not correct

To see what timezone your MySQL session is using, just execute this:
SELECT @@global.time_zone, @@session.time_zone;

to changes timezone, run below script: use super previlage

mysql> SET GLOBAL time_zone = 'America/New_York';

or

mysql> SET GLOBAL time_zone = 'Asia/Krasnoyarsk';

to check your date correct, use this:

select date(now());

or

select curdate();

for php, please go to php.ini then edit below:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone = "America/Los_Angeles"

; http://php.net/date.default-latitude
;date.default_latitude = 31.7667

changes : America/Los_Angeles --> Asia/Seoul

Friday, April 06, 2012

backup Mysql through command line

below script to backup mysql database use mysqldump:

c:\xampp\mysql\bin>mysqldump -u root -ppasssword databasename >filename.sql

the filenamesql will store at c:\xampp\mysql\bin folder

Monday, July 25, 2011

beda MyISAM dan INNODB di MySQL

MyISAM:
Format tabel MyISAM merupakan pengembangan dan penyempurnaan dari format tabel ISAM, dan merupakan format tabel default pada MySQL. Pada MyISAM file indeks disimpan dengan nama akhiran .MYI dan file data disimpan dengan nama akhiran .MYD

InnoDB:

Tipe tabel InnoDB merupakan tipe tabel MySQL yang mendukung proses transaksi. Tipe ini memiliki beberapa keunggulan, antara lain:
Format Tabel InnoDB mendukung proses transaksi dengan adanya fasilitas rollback dan commit, dan juga kemampuan untuk memulihkan tabel bila terjadi kerusakan pada tabel tersebut. Mampu melakukan penguncian (locking) pada tingkatan record dan juga mampu membaca pada perintah SELECT yang tidak dikunci (mirip dengan kemampuan Oracle).

Saturday, August 21, 2010

sql query from command line

below example for auto update query at mysql from command line;

please write below script and save as coba.bat
then set as autorun use scheduler task from windows

mysql -u root -p4321 -D fos -e "update jns_cuti set lama_jcuti = 1 where date_jcuti = CURDATE()"

Friday, January 01, 2010

auto generate Code

Berikut contoh salah satu untuk membuat Kode Otomatis (auto generate Code) dari system

$sql_isi ="SELECT * FROM permohonan_cuti";
$qr_isi =mysql_query($sql_isi, $koneksi) or die ("Gagal query".mysql_error());
if (mysql_num_rows($qr_isi) <=0) {
$kd_pcuti="PC001";
}
else {
$sql_kdpcuti="SELECT MAX(RIGHT(kd_pcuti,3)) + 1 AS kd_pcuti_baru FROM permohonan_cuti";
$qr_kdpcuti=@mysql_query($sql_kdpcuti) or die ("Gagal query".mysql_error());
$hs_kdpcuti=mysql_fetch_array($qr_kdpcuti) or die ("Hasil pemakai tidak ada");
$kd_pcuti_baru=$hs_kdpcuti[kd_pcuti_baru];

if ($kd_pcuti_baru < 10 ) {
$kd_pcuti="PC00".$kd_pcuti_baru;
}
elseif ($kd_pcuti_baru < 100) {
$kd_pcuti="PC0".$kd_pcuti_baru;
}
elseif ($kd_pcuti_baru < 1000) {
$kd_pcuti="PC".$kd_pcuti_baru;
}
}

Sunday, June 07, 2009

Create value 0 for data if no value or NULL

select kategori.nama_kategori , count(berita.nama_berita) as hitung
from kategori left join berita on
kategori.nama_kategori = berita.nama_berita
and berita.tgl_berita >= '2009-06-01'
group by kategori.nama_kategori

ACT	1
Email	0
Hardware	0
Internet	0
Network	0
Others	0
Software	0
VMI	1

Saturday, May 09, 2009

import data to MySQL 5.0.45

please do below steps:

save the excel data to csv format and no need additional character between each column.
open the csv file use notepad to make sure that each column separate by , (koma).
at the AppServ, open the phpMyAdmin and select the database
then open the table at the menu and select structure mode
then select import menu then browse the csv file
then select format file csv then select
fields terminated by : , (koma)
fields enclosed by : , (koma)

Thursday, February 05, 2009

group hari di mysql

berikut contoh script untuk menggroupkan issue berdasarkan hari di MYSQL, sejenis pivot tabel atau cross-tab :

select a.com_desc,
sum(CASE WHEN a.nama_berita = 'ACT' THEN cnt ELSE 0 END ) ACT,
sum(CASE WHEN a.nama_berita = 'VMI' THEN cnt ELSE 0 END ) VMI,
sum(CASE WHEN a.nama_berita = 'Network' THEN cnt ELSE 0 END ) Network,
sum(CASE WHEN a.nama_berita = 'Email' THEN cnt ELSE 0 END ) Email,
sum(CASE WHEN a.nama_berita = 'Others' THEN cnt ELSE 0 END ) Others,
sum(CASE WHEN a.nama_berita = 'Hardware' THEN cnt ELSE 0 END ) Hardware,
sum(CASE WHEN a.nama_berita = 'Software' THEN cnt ELSE 0 END ) Software,
sum(CASE WHEN a.nama_berita = 'Internet' THEN cnt ELSE 0 END ) Internet
from
( SELECT t_day.com_desc , t_day.com_code , berita.nama_berita, count( berita.nama_berita ) cnt
FROM t_day LEFT JOIN berita ON com_desc = hari
and berita.tgl_berita >'2000-12-31'
GROUP BY t_day.com_desc , berita.nama_berita
ORDER BY t_day.com_code
)a
group by a.com_desc
order by a.com_code desc

Sunday, February 01, 2009

sejenis decode di mysql untuk buat tabel pivot

decode hanya dikenal di Oracle. So untuk buat tabel pivot di mysql database pakai cara berikut:
yaitu CASE WHEN, demikian juga bila di MsSQL Server. Mereka juga mengenalnya sebagai CROSS-TAB(ulation) . Kalo pakai script berikut bisa disisipkan ke function jpgraph untuk membuat grafik batang bertingkat.

SELECT date_format( tgl_berita, '%m' ) AS tanggal,

sum(CASE WHEN a.nama_berita = 'ACT' THEN cnt ELSE 0 END ) ACT,
sum(CASE WHEN a.nama_berita = 'VMI' THEN cnt ELSE 0 END ) VMI

FROM (SELECT tgl_berita, nama_berita, count( nama_berita ) cnt
FROM berita GROUP BY nama_berita, tgl_berita)a WHERE tgl_berita > '2007-12-31'
GROUP BY date_format( tgl_berita, '%m' ).

Atau pake class nya ADODB PHP, mereka sudah nyediakan functionnya di
http://phplens.com/adodb/pivot.tables.html

include "adodb/pivottable.inc.php";
    $sql = PivotTableSQL(
       $gDB,                                      #  adodb connection
        'products  p ,categories c ,suppliers s',   #  tables
        'SupplierName',                             #  rows (multiple fields allowed)
        'CategoryName',                            #  column to pivot on
        'p.CategoryID  = c.CategoryID and s.SupplierID= p.SupplierID' #  joins/where
    );

group by tahun dan bulan di mysql

Ayo Berbagi Pengetahuan .... !!!