Report Analysis

SELECT COUNT(s.report_id) AS total, t.type_name, group_concat(s.content_id) as content_ids
FROM rpt_content_main s
LEFT JOIN rpt_content_type t ON t.type_id = s.report_type_id
GROUP BY s.report_type_id

Get total question consumed

SELECT * FROM 
(
Select
 d.student_id as d_student_id,
  d.gold as d_gold,
  d.total_question_consume as   d_total_question_consume,
  d.total_question_correct as d_total_question_correct
from osem_main.usr_student_data_main d 
/*WHERE d.student_id not in (4,5,22,47,49,55,79,84,85,2001,2002,2007,2021) */
) as md
join
(
SELECT c.student_id as c_student_id, count(*) as c_total_consume, sum(c.final_result) as c_total_correct 
from osem_content.qst_student_consumption c 
/*where c.student_id not in (4,5,22,47,49,55,79,84,85,2001,2002,2007,2021)*/
group by c.student_id
) as cd
ON md.d_student_id = cd.c_student_id
;

update osem_main.usr_student_main set is_reviewer=1 where login_id
in
('syazwi','awieer','quzaimer2016','hanania',
'alisa613','kindaichi',
'nazshahsalim','ainanie19','imran3087','kkkkkkkk','nazuhan87',
'osmanawang','azimazahar3','mustafakamal','aliffhafiy',
'imran3086','dinsiputsedut','ismahadi',
'abubakar','hahahaha','aqib21',
'nazshah','reez26','aqib20','langbuana','imran3088','imran3089',
'aqib21','amalina','reezh26','lalala', 'ajilala','aqib1234', 'ejoy12',
'azimazahar3','sabrina88',
 'hanania',
 'dinsiputsedut',
 'imran3086',
  'ainanie19',
 'juhari',
 'osmanawang',
 'imranhamzah','jijiji','susususu',
'Otobot','juhari','joejoejoe','radzeen','jjjjjj','alifdanishazmi');

select group_concat(student_id) from osem_main.usr_student_main m where m.is_reviewer=1;

MySQL to get total question reviewed per chapter

SELECT s.chapter_id, 
CASE
 WHEN s.eduyear_id=1 THEN 'Darjah 1'
 WHEN s.eduyear_id=2 THEN 'Darjah 2'
 WHEN s.eduyear_id=3 THEN 'Darjah 3'
 WHEN s.eduyear_id=4 THEN 'Darjah 4'
 WHEN s.eduyear_id=5 THEN 'Darjah 5'
 WHEN s.eduyear_id=6 THEN 'Darjah 6'
 WHEN s.eduyear_id=7 THEN 'Tingkatan 1'
 WHEN s.eduyear_id=8 THEN 'Tingkatan 2'
 WHEN s.eduyear_id=9 THEN 'Tingkatan 3'
 WHEN s.eduyear_id=10 THEN 'Tingkatan 4'
 WHEN s.eduyear_id=11 THEN 'Tingkatan 5'
 ELSE 'lain-lain' END as Tingkatan, 
 
s.subject_name, t.topic_no, t.chapter_name, COUNT(s.question_id) AS total_question, 

SUM(CASE WHEN s.review_by <> '' AND s.chapter_id=t.chapter_id THEN 1 ELSE 0 END) 
AS total_question_reviewed,

SUM(CASE WHEN s.review_by <> '' AND s.published_status = 1 AND s.chapter_id=t.chapter_id THEN 1 ELSE 0 END) 
AS total_question_reviewed_and_published,

SUM(CASE WHEN s.review_by <> '' AND s.published_status = 0 AND s.chapter_id=t.chapter_id AND s.review_status LIKE 'f4%' THEN 1 ELSE 0 END) 
AS total_question_need_to_review,

SUM(CASE WHEN s.review_by <> '' AND s.published_status = 0 AND s.chapter_id=t.chapter_id AND s.review_status LIKE 'up%' THEN 1 ELSE 0 END) 
AS total_question_rejected


FROM qst_master_main s
LEFT JOIN edu_subject_chapter t ON t.chapter_id=s.chapter_id
WHERE s.eduyear_id < 10
GROUP BY s.chapter_id
ORDER BY s.eduyear_id,t.subject_code_id, t.topic_no

Data Entry Review Summary

select 'main_total', A.total FROM (
select s.review_by, count(s.question_id) as total from
pencil_content.qst_master_main s 
where s.review_by <> '') as A

UNION ALL

select s.review_by, count(s.question_id) as  total from
pencil_content.qst_master_main s 
where s.review_by <> '' group by s.review_by order by total DESC;

Dummy data for pfm_student_subject

(student_id,

subject_code_id,

dt_update,

short_name,

overall_accuracy,

overall_coverage,

last_three_months_coverage,

last_three_months_accuracy)



SELECT * FROM (

SELECT

85,

e.subject_code_id,

(NOW() - INTERVAL ROUND((RAND() * (90-0))+0) DAY) as dt_update,

ec.short_name,

ROUND(AVG((RAND() * (100-0))+0)) as overall_accuracy,

ROUND(AVG((RAND() * (100-0))+0)) as overall_coverage,

ROUND(AVG((RAND() * (100-0))+0)) as last_three_months_coverage,

ROUND(AVG((RAND() * (100-0))+0)) as last_three_months_accuracy

FROM edu_student_subject e

LEFT JOIN edu_subject_code ec ON ec.subject_code_id = e.subject_code_id

WHERE e.student_id=85

GROUP BY e.subject_code_id) as tbl1



ON DUPLICATE KEY UPDATE

dt_update = tbl1.dt_update,

short_name = tbl1.short_name,

overall_accuracy = tbl1.overall_accuracy,

overall_coverage = tbl1.overall_coverage,

last_three_months_coverage = tbl1.last_three_months_coverage,

last_three_months_accuracy = tbl1.last_three_months_accuracy;

How to get only column name in mysql specific table

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS WHERE

TABLE_SCHEMA = 'db_name'

AND TABLE_NAME = 'table_name';

After import sample data 1.9.1.0 can't login admin, no admin user in database?

Run this code in query:

LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;

SET @SALT = "rp";
SET @PASS = CONCAT(MD5(CONCAT( @SALT , "password") ), CONCAT(":", @SALT ));
SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL;

INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) 
VALUES ('Firstname','Lastname','email@example.com','myuser',@PASS,NOW(),0,0,1,@EXTRA,NOW());

INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) 
VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'myuser'),'Firstname');

UNLOCK TABLES;

In admin site:

username: myuser

password: password 

Save Mysql table -> field, type, comment to csv

Mysql statement to save table field, type, comment to csv

SELECT
 
    column_name,
    column_type,
    column_default,
    column_comment
 
FROM information_schema.columns
WHERE table_name='c_product_detail'
INTO OUTFILE 'C:/Users/Imran/Desktop/work/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
;