Hi,
I wish to join below queries to generate configurable report:
To Find course: SELECT DISTINCT `data` FROM `mdl_user_info_data` WHERE `fieldid` ='1'
To Get user's first login: SELECT * FROM `mdl_user` WHERE `id` ='$userid'
To Get user's last login: SELECT * FROM `mdl_user` WHERE `id` ='$userid'
To Exclude some specific users: SELECT userid FROM `mdl_user_info_data` WHERE mdl_user_info_data.userid IN (SELECT userid FROM mdl_quiz_attempts WHERE userid != '839' AND userid != '2' AND userid != '1702' AND userid != '1703' AND userid != '1704' AND userid != '1705' ) AND fieldid ='5'
To Count students: SELECT userid FROM `mdl_user_info_data` WHERE mdl_user_info_data.userid IN (SELECT userid FROM mdl_quiz_attempts) AND fieldid ='5'
To Count quiz attempt: SELECT COUNT(`userid`) AS total_student FROM `mdl_quiz_attempts` WHERE `userid` ='$userid'
To Get Date and time: SELECT * FROM `mdl_quiz_attempts` WHERE `userid` ='$userid' ORDER BY `mdl_quiz_attempts`.`id` ASC
To Check custom_profile_field: SELECT * FROM `mdl_user_info_data` WHERE `userid`='$userid' and fieldid ='1'
To Get username: SELECT * FROM `mdl_user` WHERE `id` ='$userid'
To Get custom_profile_field: SELECT * FROM `mdl_user_info_data` WHERE `userid`='$userid' and fieldid ='5'
To Get total of custom_profile_field: SELECT DISTINCT `data` FROM `mdl_user_info_data` WHERE mdl_user_info_data.userid IN (SELECT userid FROM mdl_quiz_attempts) AND fieldid ='1'
To Get total students: SELECT * FROM `mdl_user_info_data` WHERE `userid`='$userid' and fieldid ='5'
To get Quiz Name: SELECT * FROM `mdl_quiz` WHERE `id` ='$quiz_id'
Above queries are to get data in column like this:
S.NO
User Name
Course Name (custom_profile_field)
Examination Centre (custom_profile_field)
No. Of Quiz (How many quizzes student attempted )
Quiz Attempt (Names of Quiz Attempted)
Start Time (Start time of quiz)
End Time (End time of Quiz)
Marks (Grades received by student)
First Login (First login of student)
last Login (Last login of student)
Please help me in this.
Thanks in Advance.