Dynamic Mailing List from MySQL query

Question

I have recently implemented a "rewards points" system on my Wordpress (woocommerce) site.

I have constructed a MySQL query to show me the names, email address and current points balance for each of my customers:

SELECT  
            b1.meta_value AS first_name, 
            b2.meta_value as last_name, 
            b3.meta_value as points_balance,
            a.user_email,
            p.points_balance

FROM    wp_zaydb6_users a
                INNER JOIN wp_usermeta b1 ON b1.user_id = a.ID AND b1.meta_key = 'first_name' 
                INNER JOIN wp_usermeta b2 ON b2.user_id = a.ID AND b2.meta_key = 'last_name'
                INNER JOIN wp_usermeta b3 ON b3.user_id = a.ID AND b3.meta_key = 'wc_points_balance'

WHERE  b3.meta_value > 0

This returns something like this:

John    Smith   511 j.smith@gmail.com
Michael Barrymore   71  MICHAEL@BARRYMORE.COM.AU
Bob Monkhouse   326 Bob.monkhouse@bobmonk.at
Jane    Doe 176 Jane.Doe@janedoe.ch
Ricky   Martin  511 r.martin@gmail.com

My next step is to construct a script (bash but preferably PHP) which will loop through each record, pulling out each "First_name", printing the points_balance and emailing it to the relevant user. This all needs to be wrapped up an HTML formatted email rather than plain text.

I've not constructed dynamic mailing lists so this is all new to me.

What's the best way forward?

Answer 1

I have an old system that has to do something like this, and I do it in a very fast, but hacky way.

$row_message = fetch_html_template('renew_error'); //returns the html template text, that has special markings like [name], [username].
$row_message['text'] = str_replace('[name]', $variableThatHasUsersName, $row_message['text']);

its a very dirty and quick way to fix it. If you want to exted it, you can just use str_replace more times for as many variables as you wish.