Need help to extract a user's email from mdl_user table.

Need help to extract a user's email from mdl_user table.

by Frankie Kam -
Number of replies: 7
Picture of Plugin developers

Hi.

I'm new to SELECT queries in SQL in the Moodle environment.

How do I write a code that will retrieve a Moodle user's email address and name from the mdl_user table, given the user's id?
So far all I have, which works, is the below code that will send an email message to myself. I hardcoded my own email address
in blue text. What I need to do is to email a message to any user, given his or her user id. 

require_once('config.php');
$CFG->smtphosts = '';
echo '<pre>' . "\n";

$mail =& get_mailer();
$mail->From = $USER->email;
$mail->AddAddress('kamboonseng@gmail.com', 'Someone');
$mail->Subject = "A new message for you from ".$USER->firstname." ".$USER->lastname." ".$course->shortname;
$mail->IsHTML(false);
$mail->Body = "\nMessage:\n".$TheMainMessage;

if ($mail->Send()) {
echo 'Mail sent';
echo '</pre>';
} else {
echo('ERROR: '. $mail->ErrorInfo);
echo '</pre>';
}

Any help would be greatly appreciated.

Frankie Kam
Malaysia 

Average of ratings: -
In reply to Frankie Kam

Re: Need help to extract a user's email from mdl_user table.

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Assuming Moodle 2.x (as you didn't specify):

$user = $DB->get_record('user', array('id' => $userid));
email_to_user($user, $USER, 'Subject', 'The text of the message', 'The <b>HTML</b> version of the <i>message</i>'); // See lib/moodlelib.php for the full list of options

(Note this will send the email from the current user ($USER) to the specified $userid)

Davo

Average of ratings: -
In reply to Davo Smith

Re: Need help to extract a user's email from mdl_user table.

by Frankie Kam -
Picture of Plugin developers

Hi Davo!

MANY thanks for your code for M2.x Davo! I was actually asking for M1.9. But since my last post on this forum I have solved the problem here, your code will be most useful when I turn my sights onto M2.0.

I was actually working on how to make David Mudrak's Stamp Collection module for Moodle 1.9 send E-mail notifications to students once a stamp was awarded. 

Regards
Frankie Kam 

Average of ratings: -
In reply to Frankie Kam

Re: Need help to extract a user's email from mdl_user table.

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I had a quick look at the solution you found and wanted to make a couple of points.

Firstly, instead of the hand-coded SQL query, why not just use the built-in Moodle database functions:

$user = get_record('user', 'id', $newstamp->userid);

This not only writes the SQL for you, it also returns it as a single object, rather than an array with one item in it (and for future reference, a good trick for getting the first item out of an array, is to use: '$user = reset($users);', rather than using a foreach loop).

You have also written a lot of code to send the email, but it still does less than simply writing:

$success = email_to_user($user, $USER, $subject, $message);
if ($success == 'emailstop') {
echo "User email disabled";
} else if (!$success) {
echo "Emali failed";
} else {
echo "Email successful";
}

Using the standard 'email_to_user' function avoids emailing deleted or suspended users, respects the global 'divertallemails' and 'noemailsever' settings, tracks email bounces & avoids sending to addresses that have too many, cleans the subject / message parameters, respects the user preferences about text/html emails (although I note that you only send plain text in your version anyway) and probably a few other bits and bobs I haven't mentioned.

Average of ratings: -
In reply to Davo Smith

Re: Need help to extract a user's email from mdl_user table.

by Frankie Kam -
Picture of Plugin developers

Hi Davo!

Yes, Master. Many thanks for the findings. My first attempt and something like this and once something works, I don't spend much time refining it. Thanks for bringing to my attention the code refinement which I bet wil simplify things and perhaps even make the code run faster as I think what you, Master, have given is more efficient. Thank you once again, I have much to learn from a Moodle PHP Jedi Master such as yourself.

Frankie Kam

Average of ratings: -
In reply to Davo Smith

Re: Need help to extract a user's email from mdl_user table.

by Frankie Kam -
Picture of Plugin developers

Hi Davo!

Thanks to your Moodle code, I have successfully reduced the code length from 68 lines to a mere 17 lines with a noticeable increase in processing speed!

Many thanks.

I did improve on your if-else ladder. There was a bug in your original code where the message "E-mail is disabled" would appear whether or not the user had disabled her email. So I corrected the if-else ladder to be as shown in the code below:

$success = email_to_user($user, $USER, $subject, $message);
 if ($success == "1") {
    echo "E-mail successful sent to ".$user->firstname." ".$user->lastname."!";
} else if ($success == "emailstop") {
             echo "E-mail of ".$user->firstname." ".$user->lastname." has been DISABLED!"
             .'<br>E-mail NOT sent';
         } else if (!$success){
                      echo "Error: E-mail unsuccessful.";
                  }

Your code efficiency has made all the difference, and to think that I took 6 hours of coding from scratch when I could have done it in less than 20 minutes! Sigh. But I learnt a lot today. MANY THANKS ONCE AGAIN.

Frankie Kam

Average of ratings: -
In reply to Frankie Kam

Re: Need help to extract a user's email from mdl_user table.

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I'll admit my code was written straight into the forum without any testing, the other way to fix the bug would be:

if ($success === 'emailstop') {

Which checks for $success being exactly the same as 'emailstop' (or "emailstop" the type of quotes makes no difference in this case), including being the same type. Whereas ' $success == 'emailstop' ' only checks that they are the same after the two sides have been converted into the same type - in this case they were both converted into boolean values ('emailstop' => true), which is what caused the problem (the table, just before 'example #1' on this page http://php.net/manual/en/language.operators.comparison.php explains what conversions are applied in different cases).

I suspect my version of the code is actually slightly slower than yours, as there is an extra function call (email_to_user) plus a larger number of 'if' statements inside that function. On the other hand, the difference is likely to be so tiny as to be insiginificant compared to code readability, maintainability and correctness (in terms of checking more reasons for not sending the emails). smile

Average of ratings: -
In reply to Davo Smith

Re: Need help to extract a user's email from mdl_user table.

by Frankie Kam -
Picture of Plugin developers

Hi Davo

Thanks for the treatise on the operators, very useful. And yes, your code being readable and elegant, more than makes up for any miniscule speed advantage my non-function code has. If it indeed is faster, that is. Thanks once again, you've been a great help.

Frankie Kam
Melaka, Malaysia

Average of ratings: -