I am getting students added to course using a query but i noticed 2 of them do not appear( even if they are active and listed as participants) checking inside tables i noticed they do not appear in mdl_user_info_data
. How can i prevent this? or whats the reason they dont were added to this table
This is my query:
SELECT u.id,u.username,u.firstname,u.lastname,u.email, b.data
FROM mdl_user u,mdl_role_assignments r, mdl_user_info_data b
WHERE u.id=r.userid
AND u.id=b.userid
AND r.roleid=5
AND r.contextid = 'somecontextId'
ORDER BY u.email ASC;
CodePudding user response:
The table mdl_user_info_data holds the values for custom user profile fields (that are defined in mdl_user_info_field).
If a user's profile has not been edited since a particular custom user profile field has been created, then there will not be an associated mdl_user_info_data record for them.
Note that if there is more than one custom user field defined, there can be more than one mdl_user_info_data field - so your query could return more than one record per user.
You probably want to rewrite your query to LEFT JOIN with mdl_user_info_data. You probably also want to LEFT JOIN with mdl_user_info_field to identify which of the custom user profile fields it relates to.
Also note that your query makes a number of assumptions that may not always be true - if your query is running inside Moodle code, then you should use {user_info_data} instead of the 'mdl_' prefix, as that prefix can be changed. Hard-coding roleid 5 for 'student' can also fail on some sites (although it is usually the case).