[SOLVED] Need help w/REGEXP

[Grimloch]

I am trying to learn how to use REGEXP in a db query. In the code snippet below, I have a selection box(not in the code), where the user group(name) is selected. The value of this 'name selection' is numeric(group_id) from the user_groups table which has 3 fields.

 

group_id(auto-increment)

group_name(text)

group_description(text)

 

id=1 "List1" "no descr"

id=2 "List2" "no descr"

id=3 "List3" "no descr"

etc. etc. etc.

 

The 'users table' has a field called 'user_groups' where the 'group id'(numeric) from the 'user_groups' table, is recorded as a 'text' field with a dot(period) pre-pended to it. Say a user belongs to 2 groups #'s 1 and 3. His 'user_groups' field would contain ( .1.3 ). I need to compare the 'users table,user_groups' to the 'user_groups table,group_id' which is selected by the $sendto variable. I have tried for 3 days now to figure out how to make this work. If I select user group '3' "List3" to send to, then the value of $_POST['sendto'] is in fact a 3 and can be displayed. I can't seem to figure out how to extract 'user_email' from the db using this method. Any help or hints would greatly appreciated!

 

   
case ($delivery == 'grp'):

// New mail send routine for sending to a group(s)

$result = dbquery("SELECT * FROM ".DB_USERS." WHERE user_newsletter ='1' AND user_groups REGEXP('^[1-9]{1}$') = '".$_POST['sendto']."'");

if (dbrows($result)) {
	while ($data = dbarray($result)) {
                           $list = $data['user_email'];

echo $list;
}
}
die;

[Psycho]

You main problem is that your database design is flawed. You should have an intermediary table to associate users to groups. The table would only need two columns: user_id and group_id. You would have one record in that table for each association. So, if a user belongs to two groups there will be two records in that table.

 

However, if you really must keep your current flawed approach, you don't need regex. A simple LIKE comparison would work fine as long as you make sure there is a period at the end of the value in the user_groups field - ex: ( .1.3. ). Then you query would be thus:

$groupID = mysql_real_escape_string(trim($_POST['sendto']));

$query = "SELECT *
          FROM ".DB_USERS."
          WHERE user_newsletter = 1
            AND user_groups LIKE '%.{$groupID}.%'";

[Grimloch]

Thanks for your quick reply. I totally agree with you on the db design however, I didn't design it, so I have to work with what I have which is PHP-Fusion v7.00.5... I have a very successful 'newsletters infusion(mod)' to which I am trying to add 'sending to distinct groups'. Its an admin only mod and my next project will be to add sending attachments but that's way down the road. In the meantime I'll try your suggestion and see if I can make it work. Thanks !

 

[Grimloch]

You main problem is that your database design is flawed. You should have an intermediary table to associate users to groups. The table would only need two columns: user_id and group_id. You would have one record in that table for each association. So, if a user belongs to two groups there will be two records in that table.

 

However, if you really must keep your current flawed approach, you don't need regex. A simple LIKE comparison would work fine as long as you make sure there is a period at the end of the value in the user_groups field - ex: ( .1.3. ). Then you query would be thus:

$groupID = mysql_real_escape_string(trim($_POST['sendto']));

$query = "SELECT *
          FROM ".DB_USERS."
          WHERE user_newsletter = 1
            AND user_groups LIKE '%.{$groupID}.%'";

 

OK... I looked at using your suggestion but its not feasible because it would require changing the core db functions. Whenever possible when creating mods we shy away from changing core files or the db except for adding your own tables. If you change the functioning of core files then you no longer have a 'generic' system but a 'hybrid' one which won't work on others systems unless they also apply the core file changes. Most admins aren't willing to do this unless it's an official change from the CMS team. So I am still looking for help with this db/field data type delima.

[Psycho]

Well, whoever created a DB design to use a single field to store multiple associations doesn't understand database normalization. Since you are stuck with a jacked up design, I guess I'll show you a jacked up way to work with it.

 

$grpID = mysql_real_escape_string(trim($_POST['sendto']));
//Select any row where the group ID exists
$query = "SELECT *
          FROM ".DB_USERS."
          WHERE user_newsletter = 1
            AND user_groups REGEXP '\.{$grpID}\.|\.{$grpID}$'";

[Grimloch]

Well, whoever created a DB design to use a single field to store multiple associations doesn't understand database normalization. Since you are stuck with a jacked up design, I guess I'll show you a jacked up way to work with it.

 

$grpID = mysql_real_escape_string(trim($_POST['sendto']));
//Select any row where the group ID exists
$query = "SELECT *
          FROM ".DB_USERS."
          WHERE user_newsletter = 1
            AND user_groups REGEXP '\.{$grpID}\.|\.{$grpID}$'";

 

Well I tried your code with very strange results. First of all it does in fact now send emails but in a very strange way. Here is what I have: I am using a test site that I have setup(sub-domain) and I created several users with valid emails that I also created on the host.

These are assigned to user_group 3:

tester1@domain.com - John

tester2@domain.com - Paul

tester3@domain.com - Fernando

----------

Three other users with emails configured on another domain I own.

These are assigned to user_group 4:

tester1@otherdomain.com - Angela

tester2@otherdomain.com - Rita

tester3@otherdomain.com - Eraina

----------

I use Thunderbird email so I have all 6 new email addresses configured.

Here's what happens:

 

When I send an email to user_group 3, 'tester3 gets 1' - 'tester2 gets 2' - 'tester1 gets nothing.

 

When I send an email to user_group 4, 'tester3 gets 1' - 'tester2 gets 2' - 'tester1 gets nothing.

 

I cannot figure out why. To simplify matters all these users are ONLY asigned to that one group.

My first question?: the meaning of your code. I think this is what it means.

----------

\. = looking for a period

{$grpID} = looking for the value of $_POST['sendto'] which is group_id

\. = looking for another period

----------

| = OR

----------

\. = looking for a period

{$grpID} = looking for the value of $_POST['sendto'] which is group_id

----------

So it should match one entry (.2) or more than one (.2.3.5.4); is this correct?

 

I tried your code as written and also with a few variations; same result.

As written: REGEXP '\.{$grpID}\.|\.{$grpID}$'

1st variation: REGEXP('\.{$grpID}\.|\.{$grpID}$')

2nd variation: REGEXP('\.{$grpID}\.{1}|\.{$grpID}{1}$')

3rd variation: REGEXP('^\.{$grpID}\.|\.{$grpID}$')

4th variation: REGEXP('^\.{$grpID}\.{1}|\.{$grpID}{1}$')

5th variation: REGEXP'^\.{$grpID}\.{1}|\.{$grpID}{1}$'

 

Code lines:

$grpID = ($_POST['sendto']);
$result = dbquery("SELECT * FROM ".DB_USERS." WHERE user_newsletter =1 AND user_groups REGEXP'^\.{$grpID}\.{1}|\.{$grpID}{1}$'");

 

What I am seeing happening doesn't make sense to me. What if I add a period before the $sendto group_id?

 

[vineld]

You should definitely listen to what mjdamato is trying to tell you and consider migrating to another environment because what you are doing now is a VERY bad idea. The solution is not scalable by any means.

[Psycho]

I can't comment on your results. I tested the code I provided and it worked fine for me. The expression I provided is only used to get the results from the database, what you do with those results is out of my hands. Did you at least try running the query and just display the results to see if they are what you expect? Trying to run a query, performing additional functinality on top of those results, and then determining if the query was sufficient by analyzing the output is not legitimate as the post processign could be where the error is.

 

To answer your question the the regex expression does this:

 

1. Find the value (period)(groupID)(period):

\.{groupID}\.

 

OR |

 

2. Find the value (period)(groupID) at then end of the value:

\.{groupID}$

[Grimloch]

You Asked: Did you at least try running the query and just display the results to see if they are what you expect?

 

Yes, I did just now. And no matter how I configure REGEXP it will only return 2 users from the db even though that group_id is assigned to 3 users. I am really puzzled by this result. I have a custom page function where I can preview any code I want to see the results. I am attaching an image of the code-run results.

 

 

[attachment deleted by admin]

[Grimloch]

OK! Finally! Definite progress though not yet completely solved. Here is the dbquery as I have it now.

$result = dbquery("SELECT * FROM ".DB_USERS." WHERE
user_newsletter ='1' AND user_groups REGEXP'\.{$_POST['sendto']}'");

 

Users 1 thru 7 are now in group 4 as well as group 3 so their 'user_groups' field looks like this:

.3.4

 

Query above correctly selects the 7 users that are in group 4 to receive the email however heres the result in my email client.

 

User1 = 1 email

User2 = 2 emails

User3 = 3 emails

User4 = 4 emails

User5 = 5 emails

User6 = 6 emails

User7 = 7 emails

 

So the main problem is in my sending routine. The emails are being sent as a comma seperated 'To' list. Onward! Once I figure out how to send a single email to each 'selected address' I'm home free. I don't even care anymore about being able to send to multiple groups... Here is my sending routine; where am I going wrong? And thanks 'mjdamato' for the REGEXP solution.

 

    case ($delivery == 'grp'):
// New mail send routine for sending to a user_group

require_once INFUSIONS."newsletters/newsletters_include.php";
$error = "";
$subject = stripslash($_POST['subject']);
if ($_POST['format'] == "plain") {
	$content = stripslash($_POST['content']);
} else if ($_POST['format'] == "html") {
include INFUSIONS."newsletters/include/html_header_include.php";
	$content .= stripslashes($_POST['content'])."\n";
	$content .= "</body>\n</html>\n";
}
_load_PHPMailer();
$mail = new MyMailer;
$mail->Subject = $subject;
if ($_POST['format'] == 'plain') {
	$mail->Body = $content;
} else {
	$mail->AltBody = $locale['nl423'];
	$mail->Body = $content;
  }

$result = dbquery("SELECT * FROM ".DB_USERS." WHERE user_newsletter ='1' AND user_groups REGEXP'\.{$_POST['sendto']}'");
if (dbrows($result)) {
	$rows = dbrows($result);
while ($data = dbarray($result)) {
	$mail->AddAddress($data['user_email']);
			if (!$mail->Send()) {
				$error = $locale['nl417'];
			if ($rows > 1) {
				$mail->ClearAddresses();
			}
	}
}	
}
opentable($locale['nl412']);
echo "<div style='text-align:center'><br />\n";
if (!$error) {
	echo $locale['nl416']."<br /><br />\n";
echo "<table class='tbl-border' align='center' width='400' cellpadding='4' cellspacing='0'><tr>\n";
echo "<td align='center'>".$locale['nl426']."</td>\n</tr><tr>";

$result = dbquery("SELECT group_name FROM ".DB_USER_GROUPS." WHERE group_id = '".$_POST['sendto']."'");

if (dbrows($result) != 0) {
while ($data = dbarray($result)) {

echo "<td align='left'>".$locale['nl427']."".$data['group_name']."</td>\n";
}	
}

echo "</tr></table><br /><br />\n";
} else {
	echo $locale['nl417']."<br /><br />\n".$error."<br /><br />\n";
}
echo "<a href='".FUSION_SELF.$aidlink."'>".$locale['nl421']."</a><br /><br />\n";
echo "<a href='".ADMIN."index.php'>".$locale['nl422']."</a><br /><br />\n";
echo "</div>\n";
closetable();

break;
// End group sending code

[Psycho]

I have to ask, why did you find it necessary to change the code I gave you? Do you realize that the change you made is flawed? Did you even read my response in reply #7? Go ahead and create enough groups so you have at least one two-digit group code (i.e. '10'), then use that query for group code '1'. And guess what, the query will return records that are associated with group 10 as well as group 1.

[Grimloch]

  OOPPSsss........sorry about that; and you are absolutely right! I forgot to change it back after testing before I posted it here. I DO in fact have the code as you provided on my live website and it's working fine. As I said the problem now is in my sending routine. I've probably frustrated you enough now so if you choose not to continue with help I understand. I will eventually figure it out and get it to work properly before releasing it as an infusion(mod) to the CMS. Thanks for all your help, I really appreciate it.

 

[Psycho]

You should probably make a new post. You now have a database query result with all the records you need and your problem is now procedural in trying to ensure all the users in the result set get sent an email

[Grimloch]

OK. Thanks... I'll do that if I can't make any headway myself, but I'd like to figure it out. I just didn't know what to do with REGEXP as I'd never seen it used before in PHP/MySQL; I knew it existed in PERL. Thanks man for all your help.

 

[Grimloch]

OK. Thanks... I'll do that if I can't make any headway myself, but I'd like to figure it out. I just didn't know what to do with REGEXP as I'd never seen it used before in PHP/MySQL; I knew it existed in PERL. Thanks man for all your help.

 

 

Update !! A simple counter function in the dbQuery/Send routine fixed the problem of multiple emails. One email per user works flawlessly now. Thanks again guys and phpfreaks.com for all your help and support. This thread is now solved!