I need two sql querries (i'm using phpmyadmin on moodle 1.9, apache on windows 2003)
I found one SQL querry for #1 in these forums and it works 90%. #2 I don't know how to compose in sql.
1. a. Find total students enrolled in each course - my SQL solves this already
b. Find total students enrolled in Moodle -
c. AND exclude certain courses (like last semester)
d. AND find total unique students enrolled (ie, don't count a student 5x if they are enrolled in 5 courses)
2. In each moodle course, list each student's grade (for weekly progress reports/final grade and also to warn those with with D's and F's) - export by course and student on a weekly schedule to excel and email that to the main office to import into a filemaker program. I'm thinking once I find an sql querry that works I might be able to install the module: Admin Report: Custom SQL queries which says it schedules reports but I don't know how to do the SQL step and the export to excel step (another admin will have to figure out the import step into filemaker on their end)
I solved 1b-d by exporting to excel and creating a number of formulas to countif students are duplicates and only add up those students once for all courses. ie, if they are in music, english, and science I only want to count them as 1 enrolled student for this semester's count. It would be nice to do 1a-d in one sql querry rather than exporting and using formulas to figure it out.
Here's what I used to solve 1a. I put it into phpmyadmin and export to excel (1b-d)
SELECT c.fullname, u.firstname, u.lastname, u.email, u.id FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id Where cx.contextlevel = '50';
Thank you for your help. 2 is more important than 1 as I have a workaround.
I'm bumping this post because it did not start in this forum. It was in the wrong forum so I had it moved to the grading forum now in hopes to find someone versed in mysql querries. I got my original querry from Matthew Burford at this link in 2008
http://moodle.org/mod/forum/discuss.php?d=104990#p462749
2 is more pressing for me right now. If I could get all their grades from each course each week that would really help. Thanks. JT
http://moodle.org/mod/forum/discuss.php?d=104990#p462749
2 is more pressing for me right now. If I could get all their grades from each course each week that would really help. Thanks. JT
I'm just reviewing the documentation on grade publishing and grade exporting. Does anyone know whether it can export ALL grades from ALL courses at once this way instead of my figuring out the mysql code to do this?
Thanks JT
Thanks JT
Hi again,
I'm narrowing this down. I found many querries and two almost work for me. I'm need this one to work but with the GROUP the student is in too. This first one does almost everything I need except for blanks like category name's for totals and attendance.
Someone who know's mysql coding please help. I used this is phpmyadmin with 90% success:
SELECT mdl_course.fullname, mdl_course.idnumber AS course_id, mdl_user.firstname, mdl_user.lastname, mdl_user.username, mdl_grade_items.itemname AS gradebookitem, mdl_grade_grades.rawgrade AS raw_grade, mdl_grade_grades.finalgrade AS final_grade, FROM_UNIXTIME( mdl_grade_grades.timecreated ) AS date_created, FROM_UNIXTIME( mdl_grade_grades.timemodified ) AS date_modified, SUBSTRING( mdl_grade_grades.feedback, 1, 15 )
FROM mdl_grade_grades
JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
ORDER BY mdl_course.fullname, mdl_course.idnumber, mdl_user.username, mdl_grade_items.itemname, mdl_grade_grades.timemodified
I'm starting to understand the syntax of Select and Order by but Join vs Inner Join is still confusing me. I want to do something like this to also show sections.
INNER JOIN mdl_course_sections ON mdl_course_modules.section = mdl_course_sections.id
I'm narrowing this down. I found many querries and two almost work for me. I'm need this one to work but with the GROUP the student is in too. This first one does almost everything I need except for blanks like category name's for totals and attendance.
Someone who know's mysql coding please help. I used this is phpmyadmin with 90% success:
SELECT mdl_course.fullname, mdl_course.idnumber AS course_id, mdl_user.firstname, mdl_user.lastname, mdl_user.username, mdl_grade_items.itemname AS gradebookitem, mdl_grade_grades.rawgrade AS raw_grade, mdl_grade_grades.finalgrade AS final_grade, FROM_UNIXTIME( mdl_grade_grades.timecreated ) AS date_created, FROM_UNIXTIME( mdl_grade_grades.timemodified ) AS date_modified, SUBSTRING( mdl_grade_grades.feedback, 1, 15 )
FROM mdl_grade_grades
JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
ORDER BY mdl_course.fullname, mdl_course.idnumber, mdl_user.username, mdl_grade_items.itemname, mdl_grade_grades.timemodified
I'm starting to understand the syntax of Select and Order by but Join vs Inner Join is still confusing me. I want to do something like this to also show sections.
INNER JOIN mdl_course_sections ON mdl_course_modules.section = mdl_course_sections.id
Hi Jamie,
There is actually a Global Gradebook (Export) patch in the tracker: MDL-17420 and I've made the Site Wide User Report and Multi Course Grader Report (available in the Modules and Plugins Database). You might want to try these out (on a test system) and/or have a go at modifying them for your needs.
Also, you're better off using the Gradebook API (PHP Code) than trying to get your results through SQL. One thing that will definitely be missing from your SQL query results is empty grades. Other things like scales won't work and not sure what you're expecting to do with the date_created, date_modified fields.
Cheers,
Barry
There is actually a Global Gradebook (Export) patch in the tracker: MDL-17420 and I've made the Site Wide User Report and Multi Course Grader Report (available in the Modules and Plugins Database). You might want to try these out (on a test system) and/or have a go at modifying them for your needs.
Also, you're better off using the Gradebook API (PHP Code) than trying to get your results through SQL. One thing that will definitely be missing from your SQL query results is empty grades. Other things like scales won't work and not sure what you're expecting to do with the date_created, date_modified fields.
Cheers,
Barry
Thanks for replying Barry! you're one of the main people I've been reading to figure out this mysql. I know about the global grades and that's what I first tried to install using Jonathan Newman's post on both 17420 and 17346 on a test server but was having some trouble. I was under the impression that maybe it did not work in 1.9 only 1.94 and above. I don't want to have to upgrade my server mid-year to 1.97
the dates in the sql mean nothing to me - I copied this I thought from one of yours or someone's post at moodle and it almost worked so I am just tweaking it. Until I can upgrade my server, is there a way I can tweak this mysql to include which group they are in and the category it is from since the name is blank for both course total and category total. Thanks Jamie. I'll try to instal the global grades again.
the dates in the sql mean nothing to me - I copied this I thought from one of yours or someone's post at moodle and it almost worked so I am just tweaking it. Until I can upgrade my server, is there a way I can tweak this mysql to include which group they are in and the category it is from since the name is blank for both course total and category total. Thanks Jamie. I'll try to instal the global grades again.
Cool Barry, I downloaded your version which is so much easier to install than the 3 difference files I was having trouble with. I'm getting this error below. It may be related to another error I had from the past
require_js: yui_element - file not found.
I now see it's a little different than the global users report. I tried to also install the site wide users report and was successful in setting up the tables but I don't see this tab it talks about on documentation.
Click on the "Site Wide User Report" subtab
Hi Jamie,
Re: require_js: yui_element - file not found.
When/where do you get this error? If you get that when viewing my reports (not sure how you would), you should be able to safely comment out the following lines of the index.php files (in both reports):
Do you use tabs in your gradebook? If not, it should be under the 'Choose an action menu'. The names of the reports are under the View.
If you can give the steps to reproduce your error, please log an issue in the tracker and I'll get to it when I can.
Cheers,
Barry
Re: require_js: yui_element - file not found.
When/where do you get this error? If you get that when viewing my reports (not sure how you would), you should be able to safely comment out the following lines of the index.php files (in both reports):
require_js(array('yui_yahoo', 'yui_dom', 'yui_event', 'yui_container', 'yui_connection', 'yui_dragdrop', 'yui_element'));Do you use tabs in your gradebook? If not, it should be under the 'Choose an action menu'. The names of the reports are under the View.
If you can give the steps to reproduce your error, please log an issue in the tracker and I'll get to it when I can.
Cheers,
Barry
Thanks Barry,
I will put this in at the tracker too and am wondering if global grades is what I really need still because I can use cron to automate it and send it to my next admin wheras multi-user and site wide are more of a convenience for teachers/students? I still like them, but for now, if I can't upgrade yet, do you know the sql for allowing me to see categories and especially what group they are in?
Thanks,
james
ps, I got the sitewide to show up in 'choose an action' (wrong folder
) and commented out /require etc but they both show blank pages now when clicked in a new course with one fake user.
I will put this in at the tracker too and am wondering if global grades is what I really need still because I can use cron to automate it and send it to my next admin wheras multi-user and site wide are more of a convenience for teachers/students? I still like them, but for now, if I can't upgrade yet, do you know the sql for allowing me to see categories and especially what group they are in?
Thanks,
james
ps, I got the sitewide to show up in 'choose an action' (wrong folder
HI Barry,
Here's the tracker number:
After another night of reading about joins and randomly trying new querries for hours, I finally re-read your post saying I'd be 'better off using the grading API'. I'm not sure how to use that. I know vba but very little HTML, PHP, mysql. If I could get all the data to excel I could work with it using vba to get whatever I need. I've been working on this since November installing certificates, modules, and every other patch I can find. I need this for reporting ALL students grades back to another admin who runs filemaker on citrix. Thanks for your help.
Jamie
Here's the tracker number:
| CONTRIB-1809 |
After another night of reading about joins and randomly trying new querries for hours, I finally re-read your post saying I'd be 'better off using the grading API'. I'm not sure how to use that. I know vba but very little HTML, PHP, mysql. If I could get all the data to excel I could work with it using vba to get whatever I need. I've been working on this since November installing certificates, modules, and every other patch I can find. I need this for reporting ALL students grades back to another admin who runs filemaker on citrix. Thanks for your help.
Jamie
Hi Jamie,
I can't reproduce your error from the information you gave in the tracker issue. If anyone else gets the same error and we can track down why/how it happens then we could take it from there. I also tried commenting out those lines myself and all seemed to work well (but I was in a hurry so maybe I missed something somewhere). I'll be pretty busy until end of March so sorry I can't help anymore, but I would advise using a test server with different Moodle versions (maybe fresh installs) etc to see if you can get it to work.
Cheers,
Barry