Page 1 of 1

Custom profile field criteria for mass-mail?

Posted: Thu Mar 05, 2009 11:06 am
by Virtuality
Hello folks!

I'm not filling out the SRF for this one since it's not really a problem, I need a customization. :)

What I would want to do is to add a criteria checking the status of a custom profile field, in order to send out a mass e-mail to that member or not. I still want it to check the standard field as well.

I've found the relevant SQL-query in acp_email that controls this:

Code: Select all

						$sql = 'SELECT u.user_email, u.username, u.username_clean, u.user_lang, u.user_jabber, u.user_notify_type
							FROM ' . USERS_TABLE . ' u, ' . USER_GROUP_TABLE . ' ug
							WHERE ug.group_id = ' . $group_id . '
								AND ug.user_pending = 0
								AND u.user_id = ug.user_id
								AND u.user_allow_massemail = 1
								AND u.user_type IN (' . USER_NORMAL . ', ' . USER_FOUNDER . ')
							ORDER BY u.user_lang, u.user_notify_type';
But I have no idea how to modify it in order to look at a custom profile field. :oops:

I guess it should be something like

"AND pf.pf_news_from_us = 1"

But somehow the phpbb_profile_fields_data needs to be added to the query and also the user_id to be compared against that. That I'm not sure how to do.

Did I make myself understandable? :P Just ask if anything is unclear. :) And thanks for any help!

Re: Custom profile field criteria for mass-mail?

Posted: Thu Mar 05, 2009 11:08 am
by ric323
-- moved from Support to "MOD Writers Discussion".

Even if this is just for yourself, this IS the correct forum to ask about how to modify phpBB.

Re: Custom profile field criteria for mass-mail?

Posted: Thu Mar 05, 2009 11:13 am
by Virtuality
ric323 wrote:-- moved from Support to "MOD Writers Discussion".

Even if this is just for yourself, this IS the correct forum to ask about how to modify phpBB.
Oops, that was very sloppy of me. Sorry about that, and thanks for the move. :)

It was very clear where I should've posted this. :P
If you're seeking help in writing code to change the way your board works, please post in MOD Writers Discussion. This is not the place for getting help with MODs, or requesting MODs to be created!

Re: Custom profile field criteria for mass-mail?

Posted: Thu Mar 05, 2009 8:25 pm
by mtrs
Virtuality wrote:What I would want to do is to add a criteria checking the status of a custom profile field, in order to send out a mass e-mail to that member or not. I still want it to check the standard field as well.
Firstly, you can obtain those users to receive mail

Code: Select all

    $sql = 'SELECT user_id
        FROM ' . PROFILE_FIELDS_DATA_TABLE . "
        WHERE pf_news_from_us = 1";
    $result = $db->sql_query($sql);
        
    while ($row = $db->sql_fetchrow($result))
    {
        $user_id_list[] = $row['user_id']);
    }
    $db->sql_freeresult($result); 
(see get_profile_fields() in sessions.php)
Virtuality wrote:I guess it should be something like

"AND pf.pf_news_from_us = 1"

But somehow the phpbb_profile_fields_data needs to be added to the query and also the user_id to be compared against that. That I'm not sure how to do.
After that, user_id_list should be tested in the next query you posted.
Although I don't know the syntax well, a line similar to below should be added to your query.

AND ' . $db->sql_in_set('user_id', $user_id_list) . '

Re: Custom profile field criteria for mass-mail?

Posted: Fri Mar 06, 2009 10:18 am
by Virtuality
mtrs wrote:
Virtuality wrote:What I would want to do is to add a criteria checking the status of a custom profile field, in order to send out a mass e-mail to that member or not. I still want it to check the standard field as well.
Firstly, you can obtain those users to receive mail

Code: Select all

    $sql = 'SELECT user_id
        FROM ' . PROFILE_FIELDS_DATA_TABLE . "
        WHERE pf_news_from_us = 1";
    $result = $db->sql_query($sql);
        
    while ($row = $db->sql_fetchrow($result))
    {
        $user_id_list[] = $row['user_id']);
    }
    $db->sql_freeresult($result); 
(see get_profile_fields() in sessions.php)
Virtuality wrote:I guess it should be something like

"AND pf.pf_news_from_us = 1"

But somehow the phpbb_profile_fields_data needs to be added to the query and also the user_id to be compared against that. That I'm not sure how to do.
After that, user_id_list should be tested in the next query you posted.
Although I don't know the syntax well, a line similar to below should be added to your query.

AND ' . $db->sql_in_set('user_id', $user_id_list) . '
Thanks a lot! Seems to be exactly what I needed.

That first snippet of code seems to work just fine, I ran an echo and it produces the exact results I want.

The "AND ' . $db->sql_in_set('user_id', $user_id_list) . '" part though prints out as

"SELECT username, username_clean, user_email, user_jabber, user_notify_type, user_lang FROM phpbb_users WHERE user_allow_massemail = 1 AND user_id = '53' AND user_type IN (0, 3) ORDER BY user_lang, user_notify_type"

So how do you avoid the ' 's around the user_id? Since that won't give any results. Am I a total noob now? I can't remove the 's in the statement, so what am I missing here?

Re: Custom profile field criteria for mass-mail?

Posted: Fri Mar 06, 2009 11:31 am
by mtrs
I added AND u.user_id IN (' . $user_id_list . ') in your code, instead of $db->sql_in_set, you can also test that.

Code: Select all

                      $sql = 'SELECT u.user_email, u.username, u.username_clean, u.user_lang, u.user_jabber, u.user_notify_type
                         FROM ' . USERS_TABLE . ' u, ' . USER_GROUP_TABLE . ' ug
                         WHERE ug.group_id = ' . $group_id . '
                            AND ug.user_pending = 0
                            AND u.user_id = ug.user_id
                            AND u.user_allow_massemail = 1
                            AND u.user_type IN (' . USER_NORMAL . ', ' . USER_FOUNDER . ')
                            AND u.user_id IN (' . $user_id_list . ')
                         ORDER BY u.user_lang, u.user_notify_type';  

Re: Custom profile field criteria for mass-mail?

Posted: Fri Mar 06, 2009 1:05 pm
by Virtuality
No, that didn't work. :( It results in it just printing out "Array".

SELECT username, username_clean, user_email, user_jabber, user_notify_type, user_lang FROM phpbb_users WHERE user_allow_massemail = 1 AND user_id IN (Array) AND user_type IN (0, 3) ORDER BY user_lang, user_notify_type