1. Ambil data kelas XI
SELECT
u.username,
u.firstname,
u.lastname,
u.email,
nis.data AS profile_field_nis,
pw.data AS profile_field_pwexam,
c.name AS cohort1
INTO OUTFILE '/tmp/user_xi.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM mdl_user u
JOIN mdl_cohort_members cm ON cm.userid = u.id
JOIN mdl_cohort c ON c.id = cm.cohortid
LEFT JOIN mdl_user_info_field fnis
ON fnis.shortname = 'nis'
LEFT JOIN mdl_user_info_data nis
ON nis.fieldid = fnis.id
AND nis.userid = u.id
LEFT JOIN mdl_user_info_field fpw
ON fpw.shortname = 'pwexam'
LEFT JOIN mdl_user_info_data pw
ON pw.fieldid = fpw.id
AND pw.userid = u.id
WHERE c.name IN (
'XI-A','XI-B','XI-C',
'XI-D','XI-E','XI-F','XI-G'
)
AND u.deleted = 0
AND u.suspended = 0
ORDER BY c.name, u.lastname;
2. Ambil data kelas X
SELECT
u.username,
u.firstname,
u.lastname,
u.email,
nis.data AS profile_field_nis,
pw.data AS profile_field_pwexam,
c.name AS cohort1
INTO OUTFILE '/tmp/user_x.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM mdl_user u
JOIN mdl_cohort_members cm ON cm.userid = u.id
JOIN mdl_cohort c ON c.id = cm.cohortid
LEFT JOIN mdl_user_info_field fnis
ON fnis.shortname = 'nis'
LEFT JOIN mdl_user_info_data nis
ON nis.fieldid = fnis.id
AND nis.userid = u.id
LEFT JOIN mdl_user_info_field fpw
ON fpw.shortname = 'pwexam'
LEFT JOIN mdl_user_info_data pw
ON pw.fieldid = fpw.id
AND pw.userid = u.id
WHERE c.name IN (
'X-A','X-B','X-C',
'X-D','X-E','X-F','X-G'
)
AND u.deleted = 0
AND u.suspended = 0
ORDER BY c.name, u.lastname;
3. Ambil file dari laptop dengan SCP
Kelas XI:
scp dhwan@192.168.200.20:/tmp/user_xi.csv ~/Downloads/
Kelas X:
scp dhwan@192.168.200.20:/tmp/user_x.csv ~/Downloads/
Kalau gagal karena secure_file_priv, cek:
SHOW VARIABLES LIKE 'secure_file_priv';
Lalu ganti path /tmp/ sesuai folder yang ditampilkan MariaDB, misalnya:
/var/lib/mysql-files/
