Using Moodle Create Users and Enroll Them in Courses via SQL

Creating Moodle users and register them on courses programatically

I see you add "php" to your tags, so I'll give you the php answer, but the query work in any mysql.

$servername = 'localhost';
$username = 'username';
$password = 'password';
$dbname = 'moodle';

$u_moodle = 'theusernameyouwant';
$hp_moodle = password_hash('thepasswordyouwant', PASSWORD_DEFAULT); ///IMPORTANT!
$name = 'first name';
$lname = 'last name';
$email = 'e@m.ail'; ///This have to be verified by you as we're inserting it directly
$course = '123'; //Id that you put in moodle admin, not the real id    

$conn = new mysqli($servername, $username, $password, $dbname);

$sql = "INSERT INTO 'mdl_user' (auth, confirmed, mnethostid, username, password, firstname, lastname, email)
    VALUES ('manual', 1, 1, '$u_moodle', '$hp_moodle', '$name', '$lname', '$email')";
// auth = 'manual', confirmed = 1, mnethostid = 1 Always. the others are your variables

if ($conn->query($sql) === TRUE) {
    echo "OKTC";
} else {
    ////Manage your errors
}

$sql = "SELECT * FROM $m_user WHERE email='$email'";
$result = $conn2->query($sql);
if($row = $result->fetch_assoc()) {
    $id = $row['id']; //Id of newly created user. we're using that for to register him on the course
}

////You have to use this if your idnumber for the course is the one you put into moodle (thats not the real id)
$sql = "SELECT id FROM 'mdl_course' WHERE idnumber=$course";
$result = $conn->query($sql);
if(!$result){
    ///Not existing course, manage your error
}
if($row = $result->fetch_assoc()) {
    $idcourse = $row["id"];
}

///I need now the "enrol" id, so I do this:
$sql = "SELECT id FROM 'mdl_enrol' WHERE courseid=$idcourse AND enrol='manual'";
$result = $conn->query($sql);
if(!$result){
    ///Not enrol associated (this shouldn't happen and means you have an error in your moodle database)
}
if($row = $result->fetch_assoc()) {
    $idenrol = $row["id"];
}

///Lastly I need the context
$sql = "SELECT id FROM 'mdl_context' WHERE contextlevel=50 AND instanceid=$idcourse"; ///contextlevel = 50 means course in moodle
$result = $conn->query($sql);
if(!$result){
    ///Again, weird error, shouldnt happen to you
}
if($row = $result->fetch_assoc()) {
    $idcontext = $row["id"];
}

///We were just getting variables from moodle. Here is were the enrolment begins:

$time = time();
$ntime = $time + 60*60*24*$duration; //How long will it last enroled $duration = days, this can be 0 for unlimited.
$sql = "INSERT INTO 'mdl_user_enrolments' (status, enrolid, userid, timestart, timeend, timecreated, timemodified)
VALUES (0, $idenrol, $id, '$time', '$ntime', '$time', '$time')";
if ($conn->query($sql) === TRUE) {
} else {
    ///Manage your sql error
}

$sql = "INSERT INTO 'mdl_role_assignments' (roleid, contextid, userid, timemodified)
VALUES (5, $idcontext, '$id', '$time')"; //Roleid = 5, means student.
if ($conn->query($sql) === TRUE) {
} else {
    //manage your errors
}

E finito. There you got a new user name enroled into course 123.

how to enroll a user in all courses on Moodle

You can use auto enrol plugin for the purpose of enrolment. When the user clicks into course they get automatically enrolled to the courses by use of this plugin. Link: https://moodle.org/plugins/enrol_auto

Moodle user enrollment API

Something like this

$context = context_course::instance($course->id);
// What role to enrol as?
$studentroleid = $DB->get_field('role', 'id', array('shortname' => 'student'));
// Loop through the students.
foreach ($users as $user) {
    if (!is_enrolled($context, $user->id)) {
        // Not already enrolled so try enrolling them.
        if (!enrol_try_internal_enrol($course->id, $user->id, $studentroleid, time())) {
            // There's a problem.
            throw new moodle_exception('unabletoenrolerrormessage', 'langsourcefile');
        }
    }
}

SQL query for Courses Enrolment on Moodle

The first query gives you a list of users who are enroled on the course, whatever role they have assigned to them (it is possible to be enroled on a course and have no role assigned at all).

The second query shows all the users who have role 5 assigned to them at the course level. It is possible (though unusual) to have a role assigned at the course level, without actually being enroled in the course itself.

However, both of the queries are flawed.

The first query could return duplicate results if the user was enroled in a course via more than one enrolment method (unusual, but possible). It also fails to take into account the following:

• The enrolment plugin may be disabled at site level
• The enrolment plugin may be disabled at the course level (check for 'e.status = 0' to only find active enrolment plugins)
• The enrolment may be time-limited - the user's enrolment may have expired (check for 'ue.timeend = 0 OR ue.timeend > NOW()' to find only unexpired enrolments)

The second query assumes that the student role is id 5 (and also that there are no other roles, based on the student role, that are in use). I would normally either use an extra query to check the id of the 'student' role in the table 'mdl_role' and then use that value, or change the last couple of lines to the following:

JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'.

The second query also fails to check the 'contextlevel' - it is possible to have a multiple contexts with the same instance id (as it is possible to have course id 5, course category id 5, user id 5, etc.) - so you need to check that the context found is a 'course' context (contextlevel = 50).

Neither query checks for suspended users or deleted users (although, in the case of deleted users, they should have been automatically unenroled from all courses at the point where they were deleted).

A fully complete solution (possibly overly complex for most situations) would combine both queries together to check the user was enroled and assigned the role of student and not suspended:

SELECT DISTINCT u.id AS userid, c.id AS courseid
FROM mdl_user u
JOIN mdl_user_enrolments ue ON ue.userid = u.id
JOIN mdl_enrol e ON e.id = ue.enrolid
JOIN mdl_role_assignments ra ON ra.userid = u.id
JOIN mdl_context ct ON ct.id = ra.contextid AND ct.contextlevel = 50
JOIN mdl_course c ON c.id = ct.instanceid AND e.courseid = c.id
JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'
WHERE e.status = 0 AND u.suspended = 0 AND u.deleted = 0
  AND (ue.timeend = 0 OR ue.timeend > UNIX_TIMESTAMP(NOW())) AND ue.status = 0

(Note I haven't double-checked that query extensively - it runs, but you would need to carefully cross-reference against actual enrolments to check I hadn't missed anything).

---

Related Topics