Custom profile field criteria for mass-mail?

Discussion forum for MOD Writers regarding MOD Development.
Locked
User avatar
Virtuality
QA Team
Posts: 109
Joined: Sat Dec 14, 2002 4:44 pm
Location: Stockholm, Sweden
Contact:

Custom profile field criteria for mass-mail?

Post by Virtuality » Thu Mar 05, 2009 11:06 am

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!
Jonathan “Virtuality” Gulbrandsen - phpBB 2.x & 3.x Swedish Translator
phpbb-se.com - phpBB på svenska! | Development board for the Swedish translation of phpBB3

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Custom profile field criteria for mass-mail?

Post by ric323 » Thu Mar 05, 2009 11:08 am

-- 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.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
Virtuality
QA Team
Posts: 109
Joined: Sat Dec 14, 2002 4:44 pm
Location: Stockholm, Sweden
Contact:

Re: Custom profile field criteria for mass-mail?

Post by Virtuality » Thu Mar 05, 2009 11:13 am

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!
Jonathan “Virtuality” Gulbrandsen - phpBB 2.x & 3.x Swedish Translator
phpbb-se.com - phpBB på svenska! | Development board for the Swedish translation of phpBB3

mtrs
Registered User
Posts: 2049
Joined: Sat Sep 22, 2007 2:39 pm

Re: Custom profile field criteria for mass-mail?

Post by mtrs » Thu Mar 05, 2009 8:25 pm

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) . '
I abandoned all of my mods.

User avatar
Virtuality
QA Team
Posts: 109
Joined: Sat Dec 14, 2002 4:44 pm
Location: Stockholm, Sweden
Contact:

Re: Custom profile field criteria for mass-mail?

Post by Virtuality » Fri Mar 06, 2009 10:18 am

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?
Jonathan “Virtuality” Gulbrandsen - phpBB 2.x & 3.x Swedish Translator
phpbb-se.com - phpBB på svenska! | Development board for the Swedish translation of phpBB3

mtrs
Registered User
Posts: 2049
Joined: Sat Sep 22, 2007 2:39 pm

Re: Custom profile field criteria for mass-mail?

Post by mtrs » Fri Mar 06, 2009 11:31 am

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';  
I abandoned all of my mods.

User avatar
Virtuality
QA Team
Posts: 109
Joined: Sat Dec 14, 2002 4:44 pm
Location: Stockholm, Sweden
Contact:

Re: Custom profile field criteria for mass-mail?

Post by Virtuality » Fri Mar 06, 2009 1:05 pm

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
Jonathan “Virtuality” Gulbrandsen - phpBB 2.x & 3.x Swedish Translator
phpbb-se.com - phpBB på svenska! | Development board for the Swedish translation of phpBB3

Locked

Return to “[3.0.x] MOD Writers Discussion”