SQL tricks

MySQL backup

mysqldump -u transfer -ptransfer --single-transaction --quick --lock-tables=false marks_filmsv3 > marks_filmsv3.20160719.sql

Using pivot table view

http://stackoverflow.com/questions/1241178/mysql-rows-to-columns

Search for login in all DBs

/* Cleanup then Run this query after produced */

SELECT CONCAT("SELECT '",table_schema,"' as table_schema, login COLLATE utf8_unicode_ci AS login FROM `",table_schema,"`.users WHERE login = 'quinton.macmillan1218@gmail.com' UNION ") AS Delete_This_Row

FROM INFORMATION_SCHEMA.TABLES

WHERE table_name = 'user_details'

AND table_schema NOT LIKE '%schema' ;

Search for Loaded Modules in all DBs

SELECT CONCAT("SELECT '",table_schema,"' AS Site, name COLLATE utf8_unicode_ci AS name FROM `",table_schema,"`.loaded_modules WHERE name = 'FM_Logging' UNION ") AS This_Row

FROM INFORMATION_SCHEMA.tables

WHERE table_name = 'loaded_modules'

AND table_schema NOT LIKE '%schema';

Count of Users created in the past year

SELECT

COUNT(*)

FROM

(

SELECT user_id, MIN(enrolled_at)

FROM enrollments

GROUP BY 1

HAVING MIN(enrolled_at) >= DATE_SUB(NOW(), INTERVAL 1 YEAR)

) AS f

Create Enrollment Import

SELECT

u.login AS 'Username'

, e.curriculum_id AS 'Curriculum Id'

, o.name AS 'Organization'

, CASE (e.is_complete) WHEN 1 THEN 'T' ELSE 'F' END AS 'Is Complete'

, CASE (e.is_passed) WHEN 1 THEN 'T' ELSE 'F' END AS 'Is Passed'

, CASE (e.is_failed) WHEN 1 THEN 'T' ELSE 'F' END AS 'Is Failed'

, e.enrolled_at AS 'Enrolled At'

, e.completed_at AS 'Completed At'

, e.visits AS 'Visits'

, e.grade AS 'Grade'

, CONCAT('mjohnson 2016-07-19: fix ~~~~~~~~~~~~~~ ',COALESCE(e.notes,'')) AS 'Notes'

, e.is_active AS 'Is Active'

FROM `enrollments` AS e

LEFT JOIN users AS u

ON u.id = e.user_id

LEFT JOIN organizations AS o

ON o.id = u.organization_id

WHERE completed_at > '2016-07-13 22:39:15'

Correct Completion status for enrollments

UPDATE enrollments

SET is_failed = 0

, is_passed = 1

, is_complete = 1

, notes = CONCAT(COALESCE(notes,''),' ~~~~~~~~~~~~

mjohnson 2018-09-17: fix pass/fail')

WHERE curriculum_id = 137

AND is_failed = 1

AND grade >= 60

AND is_active = 1

Create Updates for all FiLMS DBs

SELECT CONCAT("UPDATE `",table_schema,"`.users SET password_hint = 'Contact support@icomproductions.ca' WHERE id = 2 AND password_hint LIKE 'Ask Mark%'; ")

FROM INFORMATION_SCHEMA.tables

WHERE table_name = 'users'

AND table_schema NOT LIKE '%schema'

ORDER BY table_schema, table_name;

Sync Enrollment Completions with Earned Cert Earned_at Date

/* Identify issues */

SELECT e.completed_at, ec.*

FROM `earned_certificates` AS ec

LEFT JOIN enrollment_earned_cert_assn AS eeca

ON eeca.earned_certificate_id = ec.id

LEFT JOIN enrollments AS e

ON e.id = eeca.enrollment_id

WHERE ec.certificate_id = 6

AND e.completed_at <> ec.earned_at

/* Sync ECs */

UPDATE IGNORE `earned_certificates` AS ec

LEFT JOIN enrollment_earned_cert_assn AS eeca

ON eeca.earned_certificate_id = ec.id

LEFT JOIN enrollments AS e

ON e.id = eeca.enrollment_id

SET ec.earned_at = e.completed_at

, ec.expires_at = DATE_ADD(e.completed_at, INTERVAL 3 YEAR)

WHERE ec.certificate_id = 6

AND e.completed_at <> ec.earned_at

/* Delete enrollments for Not Attempted Deactivated Curriculums */

DELETE IGNORE

FROM enrollments

WHERE curriculum_id IN

(

SELECT id

FROM curriculums

WHERE NAME LIKE '%deactivated%'

)

AND visits = 0

AND is_complete = 0

AND id NOT IN

(

SELECT enrollment_id

FROM results

)

/* Find Display Names with Company name */

SELECT NAME, SUBSTRING_INDEX(NAME,' at ', 1)

FROM users

WHERE role_id = 5

AND NAME LIKE '% at %'

LIMIT 25

/* Split and save Display Names before Company name */

UPDATE users

SET NAME = SUBSTRING_INDEX(NAME,' at ', 1)

WHERE role_id = 5

AND NAME LIKE '% at %'

/* Compre Faster Cert names to Courses */

SELECT cf.id AS 'Certificate ID'

, cf.NAME AS 'Certificate Name'

, c.id AS 'Course ID'

, c.CODE AS 'Course Code'

, c.NAME AS 'Course Name'

, cf.expiry_days AS 'Cert Expiry Days'

, CASE cf.is_auto_restart WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Auto Restart'

, cf.restart_offset AS 'Restart on Offset Days'

, CASE fr.is_complete WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Requires complete'

, CASE fr.is_passed WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Requires pass'

FROM fm_fastercertificates_mode_assn AS fma

LEFT JOIN fm_fastercertificates_modes AS fm

ON fm.id = fma.certificate_mode_id

LEFT JOIN certificates AS cf

ON cf.id = fma.certificate_id

LEFT JOIN fm_fastercertificates_requirements AS fr

ON fr.certificate_id = cf.id

LEFT JOIN curriculums AS cu

ON cu.id = fr.curriculum_id

LEFT JOIN structures AS s

ON s.id = cu.structure_id

LEFT JOIN courses AS c

ON c.id = s.course_id

WHERE fm.id = 2

ORDER BY cf.NAME

/* Sync Faster Cert names with Courses */

UPDATE fm_fastercertificates_mode_assn AS fma