I've modded the mod again.
Now it also has the ability to
1. Do textline searches where the search is a comma-separated list and the search finds ANY of the terms list.
I use this typically to search for zip codes. Like: search for zip IN "64133,64134,64123". The result is users with ANY of those zips. Or you could search for city IN "Jonestone, Smithville" to find users in EITHER of those two towns.
2. Do textline searches for a list of items using the LIKE-style matching. So you could search for zip codes LIKE-IN "6413,6414,6417" which would match. 64130, 64131,64132, 64140,64141, 64142, 64170,64171, 64172, etc.
3. Do the "NOT" of either of these searches (ie, find all user who do not live in zip 64132,64133,64134).
* Be sure to save a backup copy of send_core.php, the file you will be working on--so you can restore that original file if something goes wrong.
* This is for PHPList 2.10.2--it might work for other versions that are not too different from 2.10.2.
* Line numbers are only approximate.
* This mod only works with "stacked attribute" functionality, so to use it go to your config.php file and change this setting as follows:
- Code: Select all
define("STACKED_ATTRIBUTE_SELECTION",1);
* I've tested this a little but not extensively.
* If you don't want to make all these mods, here is a download with a complete "send_core.php" you should be able to drop in as a replacement for the one in ver 2.10.2. It contains all the mods on this thread plus the "calculate" bug fix and a few other misc. bug fixes. But do keep a backup of your original send_core.php:
http://mobikefed.dreamhosters.com/phplist/admin/send_core2.zipHere is the mod:
Near line 799, you will find 'case "date": . . . . break;'.
After the 'break;' but before the
- Code: Select all
default:
if (isset($where_clause)) {
insert this code:
- Code: Select all
case "textline":
$textline_value = $crit_data["values"];
if (!$textline_value) {
break;
}
if (isset($where_clause)) {
$where_clause .= " $mainoperator ";
$select_clause .= " left join $tables[user_attribute] as table$tc on table0.userid = table$tc.userid ";
} else {
$select_clause = " table$tc.userid from $tables[user_attribute] as table$tc ";
}
$where_clause .= ' ( table'.$tc.'.attributeid = '.$crit_data["attribute"].' and table'.$tc.'.value != "" and table'.$tc.'.value ';
$textlineoperator = '';
switch ($crit_data["operator"]) {
case "is":
$where_clause .= ' = "'.$textline_value . '" )';
$textlineoperator = '=';
$textline_value='"'.$textline_value.'"';
$textline_not=false; //true if a "not" or "!=" type select
break;
case "isnot":
$where_clause .= ' != "'.$textline_value . '" )';$textlineoperator = '!=';break;
$textline_not=true; //true if a "not" or "!=" type select
case "like":
$where_clause .= ' LIKE "'.$textline_value . '" )';
$textlineoperator = 'LIKE';
$textline_value="\"%".$textline_value."%\"";
$textline_not=false; //true if a "not" or "!=" type select
break;
case "notlike":
$where_clause .= ' NOT LIKE "'.$textline_value . '" )';
$textlineoperator = 'NOT LIKE';
$textline_value="\"%".$textline_value."%\"";
$textline_not=true; //true if a "not" or "!=" type select
break;
//bhugh, 5/2007, added to get "in" & "not in" options working with textline
case "in":
$textlineoperator = 'IN';
//escape the textline to make it SQL safe, then turn it into a comma-separated
//list with each value enclosed in "", like "first","second","third","etc"
$textline_value= mysql_real_escape_string($textline_value);
$textline_value_array=explode(",",$textline_value);
$textline_value='"' . implode ('","', $textline_value_array) . '"';
$textline_value="(".$textline_value.")";
$where_clause .= ' IN ('.$textline_value . ') )';
$textline_not=false; //true if a "not" or "!=" type select
break;
case "not in":
$textlineoperator = 'NOT IN';
//escape the textline to make it SQL safe, then turn it into a comma-separated
//list with each value enclosed in "", like "first","second","third","etc"
$textline_value= mysql_real_escape_string($textline_value);
$textline_value_array=explode(",",$textline_value);
$textline_value='"' . implode ('","', $textline_value_array) . '"';
$where_clause .= ' NOT IN ('.$textline_value . ') )';
$textline_value="(".$textline_value.")";
$textline_not=true; //true if a "not" or "!=" type select
break;
case "likein":
$textlineoperator = 'LIKE';
//escape the textline to make it SQL safe, then turn it into a comma-separated
//list with each value enclosed in "", like "first","second","third","etc"
$textline_value= mysql_real_escape_string($textline_value);
$textline_value_array=explode(",",$textline_value);
//OK, this is a kludge
$textline_value='"%' . implode ('%") or value LIKE ("%', $textline_value_array) . '%"';
$where_clause .= ' IN ('.$textline_value . ') )';
$textline_value="(".$textline_value.")";
$textline_not=false; //true if a "not" or "!=" type select
break;
case "not likein":
$textlineoperator = 'NOT LIKE';
//escape the textline to make it SQL safe, then turn it into a comma-separated
//list with each value enclosed in "", like "first","second","third","etc"
$textline_value= mysql_real_escape_string($textline_value);
$textline_value_array=explode(",",$textline_value);
//OK, this is a kludge
$textline_value='"%' . implode ('%") and value NOT LIKE ("%', $textline_value_array) . '%"';
$where_clause .= ' IN ('.$textline_value . ') )';
$textline_value="(".$textline_value.")";
$textline_not=true; //true if a "not" or "!=" type select
break;
}
# $where_clause .= " )";
//bhugh, in case of "NOT" or "!=" type query, must do right join so as to be sure
//to include all users who don't even have the attribute marked/listed in the attribute table
//ie, if the attribute is zipcode, some users have zip 12345, some 12346, some have
//NO ZIP CODE ENTERED in the attribute table. So query zip=12345 is easy. But
//for query zip!=12345 you have to right join with the entire user table (and also
//use a slightly different "where" clause so as
//to include all users who don't even have a zipcode entered at all.
if ($textline_not) {
$subqueries[$i]['query'] = sprintf('select id from %s as table%d right join %s as tableb%d on table%d.userid=tableb%d.id and table%d.attributeid = %d where ( value %s %s )',
$GLOBALS['tables']['user_attribute'],$tc,
$GLOBALS['tables']['user'], $tc,
$tc, $tc, $tc,
$crit_data['attribute'],
$textlineoperator,
$textline_value);
} else {
$subqueries[$i]['query'] = sprintf('select userid from %s where attributeid = %d and value != "" and ( value %s %s )',$GLOBALS['tables']['user_attribute'],
$crit_data['attribute'],
$textlineoperator,
$textline_value);
}
break;
Near line 1413 find this code:
- Code: Select all
$attreq = Sql_Query(sprintf('select * from %s where type in ("select","radio","date","checkboxgroup","checkbox") %s',$tables["attribute"],$already_used));
Replace with this:
- Code: Select all
$attreq = Sql_Query(sprintf('select * from %s where type in ("select","radio","date","checkboxgroup","checkbox","textline") %s',$tables["attribute"],$already_used));
Around line 1458 find this code:
- Code: Select all
case "date":
$att_js .= sprintf('value_divs[%d] = "criteria_values_text";'."\n",$att["id"]);
$att_js .= sprintf('value_default[%d] = "%s";'."\n",$att["id"],$GLOBALS['I18N']->get('dd-mm-yyyy'));
$att_js .= sprintf('values[%d] = new Array(%d);'."\n",$att["id"],1);
$att_js .= sprintf('values[%d][%d] = new Option("%s","%d",false,false);'."\n",$att["id"],$c,
"Date" ,"dd-mm-yyyy"); # just to avoid javascript errors, not actually used
$att_js .= sprintf('operators[%d] = new Array(4);'."\n",$att["id"]);
$att_js .= sprintf('operators[%d][0] = new Option("%s","is",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('is'));
$att_js .= sprintf('operators[%d][1] = new Option("%s","isnot",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isnot'));
$att_js .= sprintf('operators[%d][2] = new Option("%s","isbefore",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isbefore'));
$att_js .= sprintf('operators[%d][3] = new Option("%s","isafter",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isafter'));
Replace that entire code with this:
- Code: Select all
case "textline":
$att_js .= sprintf('value_divs[%d] = "criteria_values_text";'."\n",$att["id"]);
$att_js .= sprintf('value_default[%d] = "%s";'."\n",$att["id"],'');
$att_js .= sprintf('values[%d] = new Array(%d);'."\n",$att["id"],1);
//bhugh, rem-ed out because it somehow conflicts with the actual value the person enters for this criterion
//$att_js .= sprintf('values[%d][%d] = new Option("%s","%d",false,false);'."\n",$att["id"],"0",
// $att["id"],"dd-mm-yyyy"); # just to avoid javascript errors, not actually used
$att_js .= sprintf('operators[%d] = new Array(4);'."\n",$att["id"]);
//bhugh, 5/2007, added to get "in" & "not in" options working with textline
$att_js .= sprintf('operators[%d][0] = new Option("%s","in",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('in (comma-separated list)'));
$att_js .= sprintf('operators[%d][1] = new Option("%s","not in",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('not in (comma-separated list)'));
$att_js .= sprintf('operators[%d][2] = new Option("%s","is",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('is'));
$att_js .= sprintf('operators[%d][3] = new Option("%s","isnot",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isnot'));
$att_js .= sprintf('operators[%d][4] = new Option("%s","like",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('like'));
$att_js .= sprintf('operators[%d][5] = new Option("%s","notlike",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('notlike'));
//bhugh, 5/2007, added to get "like-in" & "not like-in" options working with textline
$att_js .= sprintf('operators[%d][6] = new Option("%s","likein",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('like-in (comma-separated list)'));
$att_js .= sprintf('operators[%d][7] = new Option("%s","not likein",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('not like-in (comma-separated list)'));
break;
case "date":
$att_js .= sprintf('value_divs[%d] = "criteria_values_text";'."\n",$att["id"]);
$att_js .= sprintf('value_default[%d] = "%s";'."\n",$att["id"],$GLOBALS['I18N']->get('dd-mm-yyyy'));
$att_js .= sprintf('values[%d] = new Array(%d);'."\n",$att["id"],1);
//bhugh, rem-ed out because it somehow conflicts with the actual value the person enters for this criterion
//$att_js .= sprintf('values[%d][%d] = new Option("%s","%d",false,false);'."\n",$att["id"],$c,
// "Date" ,"dd-mm-yyyy"); # just to avoid javascript errors, not actually used
$att_js .= sprintf('operators[%d] = new Array(4);'."\n",$att["id"]);
$att_js .= sprintf('operators[%d][0] = new Option("%s","is",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('is'));
$att_js .= sprintf('operators[%d][1] = new Option("%s","isnot",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isnot'));
$att_js .= sprintf('operators[%d][2] = new Option("%s","isbefore",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isbefore'));
$att_js .= sprintf('operators[%d][3] = new Option("%s","isafter",false,true);'."\n",$att["id"],$GLOBALS['I18N']->get('isafter'));
[Note: The above also fixes a bug in the "Date" attribute code that prevented it from working on my installation. However it may cause javascript errors--though I haven't encountered them.]
Around line 1557 find this code:
- Code: Select all
$attreq = Sql_Query(sprintf('select * from %s where type in ("select","radio","date","checkboxgroup","checkbox") %s',$tables["attribute"],$already_used));
Replace with this:
- Code: Select all
$attreq = Sql_Query(sprintf('select * from %s where type in ("select","radio","date","checkboxgroup","checkbox","textline") %s',$tables["attribute"],$already_used));
