I would like to build a page that queries the mdl_user table to display a list of deleted users. I'm going to use it as kind of an archive.
Which function would be best suited to show only deleted users and, if possible, group them by year?
Sam
To get the deleted users:
How do you know which students are in which year on your Moodle site? Do you use one of the fields on the user profile page? Perhaps you assume all students in a given course are in a particular year?
Anyway, if you can tell us what Moodle information shows you what year a student is in, maybe someone can help you with SQL. The PHP function to use would probably be one of the following:
cheers
Gordon
$deleted_users = get_records('user', 'deleted', '1');
if (empty($deleted_users)) {
if (empty($deleted_users)) {
print "no deleted users found";
} else {print_object($deleted_users);
}How do you know which students are in which year on your Moodle site? Do you use one of the fields on the user profile page? Perhaps you assume all students in a given course are in a particular year?
Anyway, if you can tell us what Moodle information shows you what year a student is in, maybe someone can help you with SQL. The PHP function to use would probably be one of the following:
- get_records
- get_records_select
- get_records_sql
cheers
Gordon
How do you know which students are in which year on your Moodle site?
I was hoping to go off of the timestamp that moodle adds to the email field when a user gets deleted.
I know where to modify that part of the delete function so that the stamp shows elsewhere...
Would it be better to create another field for the stamp, or can I pick and choose what information I want out of a specific field?
i.e. let's say that a deleted user has an email field of s_wynens@whatever.com:135434846
can I use anything after the : in a SQL command to sort, or should I send 135434846 to a totally separate field and access it that way?
Sam
P.S. let me know if that makes sense or not.
I was hoping to go off of the timestamp that moodle adds to the email field when a user gets deleted.
I know where to modify that part of the delete function so that the stamp shows elsewhere...
Would it be better to create another field for the stamp, or can I pick and choose what information I want out of a specific field?
i.e. let's say that a deleted user has an email field of s_wynens@whatever.com:135434846
can I use anything after the : in a SQL command to sort, or should I send 135434846 to a totally separate field and access it that way?
Sam
P.S. let me know if that makes sense or not.
Interesting idea. Then perhaps something like the following PHP would work:
// set up the start dates
$startyear = 2004;
$startmonth = 9;
$startday = 1;
// set up the start end dates
$endyear = 2005;
$endmonth = 8;
$endday = 31;
// if this code is in a function, you will need to uncomment the next line
// in order to access to the $CFG variable
// global $CFG;
$sql = "
SELECT
u.*,
deletedate = SUBSTRING(u.email,LOCATE(':',u.email)+1)
FROM
{$CFG->prefix}user u
WHERE
u.deleted = 1
AND deletedate REGEXP '^[0-9]+$'
AND deletedate >= UNIX_TIMESTAMP('$startyear-$startmonth-$startday')
AND deletedate <= UNIX_TIMESTAMP('$endyear-$endmonth-$endday')
ORDER BY
deletedate
";
$deleted_users = get_records_sql($sql);
if (empty($deleted_users)) {
$start = "$startyear-$startmonth-$startday";
$end = "$endyear-$endmonth-$endday";
print "no deleted users found in $start to $end";
} else {
print_object($deleted_users);
}
The above code is completely untested and is intended only as a guide.
Hope it helps!
Gordon
Thanks for that. I'll start playing around with it and post a final product once I get it looking/working how I want.
Thanks again.
Sam
Thanks again.
Sam
Here's a better effort that I have tested to some extent. On Moodle 1.6, it seems that the username field (not the email field) of deleted user is set to: email address + "." (not ":") + date deleted (as a unix time stamp)
// set up the start and end dates (hour, minute, second, month, day, year)
$startdate = mktime(0, 0, 0, 9, 1, 2004); // = 1st Sept 2004
$enddate = mktime(0, 0, 0, 31, 8, 2005); // = 31st Aug 2005
// SQL code to extract deleted date from username field of deleted user
$deleteddate = "IF(deleted=0,0,RIGHT(username,LOCATE('.',REVERSE(username))-1))";
// set conditions for the select query
$select = "deleted=1 AND $deleteddate>0";
if ($startdate) $select .= " AND $deleteddate>=$startdate";
if ($enddate) $select .= " AND $deleteddate<=$enddate";
$deleted_users = get_records_select('user', $select);
if (empty($deleted_users)) {
// set up the start and end dates (hour, minute, second, month, day, year)
$startdate = mktime(0, 0, 0, 9, 1, 2004); // = 1st Sept 2004
$enddate = mktime(0, 0, 0, 31, 8, 2005); // = 31st Aug 2005
// SQL code to extract deleted date from username field of deleted user
$deleteddate = "IF(deleted=0,0,RIGHT(username,LOCATE('.',REVERSE(username))-1))";
// set conditions for the select query
$select = "deleted=1 AND $deleteddate>0";
if ($startdate) $select .= " AND $deleteddate>=$startdate";
if ($enddate) $select .= " AND $deleteddate<=$enddate";
$deleted_users = get_records_select('user', $select);
if (empty($deleted_users)) {
print "no deleted users found in $startdate to $enddate";
} else {print_object($deleted_users);
}