Search code examples
sqlphpmailer

How to send PHPMailer that retrieve email template from database and insert variable into template

I'm trying to retrieve a email template from my database to my PHPMailer body. The variables$title and $name are to put inside my email template so the variables are displayed together with the template. Is there any suggestions of doing it?

Here is my database of the template: db Below is the PHP code:

if(isset($_POST['issue']))
    {
        ...
        $username = $_POST['studentname'];
        $bookid = $_POST['bookdetails'];

        //perform multi query to retrieve data
        $sql= "SELECT option_value FROM settings WHERE option_name = 'email_temp_issue';";
        $sql .="SELECT title FROM books WHERE id = '$bookid';";
        $sql .="SELECT fullName from students WHERE email = '$username'";
        $sql_run = mysqli_multi_query($connection,$sql);

        if(mysqli_num_rows($sql_run)>0)
        {
            foreach ($sql_run as $row)
            {
                $email_template = $row['option_value']; //retrieve email template 
                $title = $row['title']; //variable to put in email template
                $name = $row['fullName']; //variable to put in email template 
            }
        }//NOT SURE IF I RETRIEVE IT RIGHT..

        $mail->Body = "$email_template"; //load template to email body



        if(!$mail->Send()) {
            echo "<script>alert('Error while sending Email.')</script>";
            var_dump($mail);
        }
        else {
            echo "<script>alert('Email sent successfully')</script>";
        }
}
Solution

  • First of all your script is vulnerable to SQL injection attack, so you should mitigate that as a matter of urgency.

    To send to a list from a database, base your code on the mailing list example provided with PHPMailer.

    PHPMailer doesn't care how you create your message body, so you can do simple string substitutions using your examples:

    $mail->Body = str_replace(['{name}', '{title}'], [$name, $title], $email_template);

    or something more complex using a template engine like Smarty or Twig if it's appropriate.