Insert MySQL Table from *.csv file

Setup local infile

Sebelum dapat melakukan load data secara local ke dalam database, maka perlu memastikan bahwa load infile dalam kondisi aktif

Periksa status local infile

mysql> show global variables like 'local_infile';

Akan memberikan informasi seperti di bawah ini:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Untuk mengaktifkan gunakan perintah berikut:

mysql> set global local_infile=true;

Setelah itu silahkan connect ke database dengan parameter local infile

mysql --local_infile=1 -u root -ppassword DB_name

Dan lakukan load data local, seperti:

mysql> load data local infile 'path/file_name.extention' into table table_name;

INSERT

Misalkan kita memiliki table berikut ini:

mysql> desc temp_user_info_data;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | bigint(10) | NO   | PRI | NULL    | auto_increment |
| userid     | bigint(10) | NO   | MUL | 0       |                |
| fieldid    | bigint(10) | NO   |     | 0       |                |
| data       | longtext   | NO   |     | NULL    |                |
| dataformat | tinyint(2) | NO   |     | 0       |                |
+------------+------------+------+-----+---------+----------------+

Catatan: untuk melihat info lebih detail tentang table dan bagaimana table tersebut di buat, Anda dapat menggunakan perintah:

mysql> desc namatable

atau

mysql> show create table namatable;

Kemudian file data kita diletakkan di /root dengan nama file data *.csv: contoh.csv seperti berikut:

5791,23,11
19766,23,11

Lalu lakukan load data:

mysql> load data local infile '/root/contoh.csv' into table temp_user_info_data fields terminated by ',' (userid,fieldid,data);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Jika data Anda memiliki satu baris pertama yang berisi nama kolom, maka berikan ignore 1 lines seperti berikut:

mysql> load data local infile '/root/contoh.csv' into table temp_user_info_data fields terminated by ',' ignore 1 lines (userid,fieldid,data);

Switch php version

Berikut adalah untuk switch dari satu versi php ke versi lainnya

sudo update-alternatives --config php

Setelah itu akan muncul pilihan sebagai berikut (tergantung php version yang sudah/pernah di install):

There are 5 choices for the alternative php (providing /usr/bin/php).

  Selection    Path             Priority   Status
------------------------------------------------------------
  0            /usr/bin/php8.1   81        auto mode
  1            /usr/bin/php7.1   71        manual mode
  2            /usr/bin/php7.2   72        manual mode
* 3            /usr/bin/php7.4   74        manual mode
  4            /usr/bin/php8.0   80        manual mode
  5            /usr/bin/php8.1   81        manual mode

Press <enter> to keep the current choice[*], or type selection number: 

Reference: https://php.tutorials24x7.com/blog/how-to-switch-php-version-on-ubuntu-20-04-lts

Fix Error #551 in phpMyAdmin 18.04

Error message containts :

Warning in ./libraries/plugin_interface.lib.php#551
count(): Parameter must be an array or an object that implements Countable

Solution:

You can also fix this by editing the library itself.

  1. Make a backup first
    sudo cp /usr/share/phpmyadmin/libraries/plugininterface.lib.php /usr/share/phpmyadmin/libraries/plugininterface.lib.php.bak
  2. Edit the library
    sudo nano /usr/share/phpmyadmin/libraries/plugin_interface.lib.php
  3. Search for “if (! is_null($options) && count($options) > 0) {”
    or if not found then search for “if ($options != null && count($options) > 0) {”

Then replace it with “if ($options != null) {”

To search in Nano editor press CTRL and W

  1. Save the file (CTRL and O)
  2. Refresh the phpMyAdmin page and try agin.

Backup Stored Procedures and Routines

We need to specify --routines to take backup of stored procedures with data and tables.

The following command will take backup of entire database including stored procedures. For example, your database name is “mydb”.

mysqldump -u root -p –routines mydb > mydb.sql

To take backup of only Stored Procedures and Triggers (Exclude table and data ) use the following command.

mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt mydb > mydb.sql

Reference:
https://tecadmin.net

Membuat seluruh elemen dalam Form tidak dapat diakses

Dengan memberikan attribut disabled pada elemen Fieldset

<form id="form1">
    <fieldset disabled>
        <input/>
        <input/>
        <input/>
    </fieldset>
</form>
<br>
<button id="btn_edit">Edit On</button><br>
<button id="btn_lock">Edit Off</button>

Jika menggunakan jQuery :

$("#btn_edit").click(function(){
        $("#form1 :fieldset").prop('disabled',false);
    });

$("#btn_lock").click(function(){
        $("#form1 :fieldset").prop('disabled',true);
    });

 

Upgrade PHP 5.5.x to PHP 5.6 on Ubuntu 14.04

Kebutuhan ini muncul ketika bermaksud menggunakan PHPUnit 5.6 yang mensyaratkan PHP 5.6 padahal saat ini masih menggunakan PHP 5.5.9. Tetap diperhatikan jika Anda memiliki modul-modul tambahan lainnya bisa terjadi akan hilang begitu Anda upgrade biasanya karena link/file-file modul *.so nya tidak ada.

sudo apt-get install software-properties-common
sudo add-apt-repository ppa:ondrej/php5-5.6
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install php5

Pada saat proses di atas saya lakukan, eeh phalcon.so tidak berada lagi pada tempatnya sebagaimana ketika menggunakan PHP 5.5.9. So be carefull ya teman2.

 

Salam,

 

 

Mencopot MySQL sepenuhnya

Uninstall MySQL dengan apt-get remove terkadang tidak lah cukup, karena tidak menghapus jejak-jejak sepenuhnya.

Berikut perintah-perintah yang lebih ‘gahar’, tapi mohon hati-hati ya, jangan sampai data Anda hilang kalau belum di backup :

sudo -i
service mysql stop
killall -KILL mysql mysqld_safe mysqld
apt-get --yes purge mysql-server mysql-client
apt-get --yes autoremove --purge
apt-get autoclean
deluser --remove-home mysql
delgroup mysql
rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld
updatedb
exit

 

MySQL Create Database with UTF8MB4 Character Set Syntax

Create database

I usually forgot the MySQL’s command line to create database with UTF8 character set syntax, so here it is:

CREATE DATABASE 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;

or

CREATE SCHEMA 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Create user

Create user and give previledge to it

create user 'username'@localhost identified by 'password';
grant all privileges on *.* to username@localhost;

or

GRANT [type of permission] ON [database name].[table “” not found /]
TO ‘[username]’@'localhost’;

Change user’s password

Replace the password with the password that you want to use.

MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

Reference from :

  • http://www.euperia.com/development/mysql/mysql-create-database-with-utf8-character-set-syntax/1064
  • https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Pemanfaatan checkbox dengan jQuery

Berikut ini adalah beberapa teknik di jQuery untuk bekerja dengan checkbox  :

Pemeriksaan apakah checkbox ditandai (checked)

// Mengembalikan nilai true jika ditandai, dan false selain itu.
$('#checkboxA').is(':checked');

Menandai checkbox

Berdasarkan spesifikasi HTML5, maka atribut haruslah di isi dengan checked=”checked”; bukan sekedar checked atau checked=”true”, dsb.

Read more

1 2 3