原文
http://blog.roodo.com/rocksay...1149103.html在表单中，可让使用者查询指定的门市代号。门市代号全为数字，以空白或逗点分隔各门市代号，并可使用连字符号 (-) 表示从哪个门市到哪个门市。例如输入 000-010 表示查询从 000到010 的门市。又如输入 000,005, 010-020 ，则表示查询 000 、005 以及从 010 到 020 的门市。解析输入字串，转成 SQL 查询语法的一部份。
复制程式
function parse_shop_string($shops) {
if(emptyempty($shops))
return "";
/*
分离 单店 与 区间 表示。
s1 = 单店
s2 = 区间
ex: shops = "000,003-005, 010 020, 230-240 301"
s1 = Array ( [0] => 000 [1] => 010 [2] => 020 [3] => 301 )
s2 = Array ( [0] => BW_003_005_END [1] => BW_230_240_END [2] => )
query_string = "(_TABLE.shop = 願' or _TABLE.shop = 顢' or _TABLE.shop = 顬' or _TABLE.shop = 馅' or _TABLE.shop between 顛' and 顝' or _TABLE.shop between 餾' and 饈')";
*/
$s = preg_replace("/[a-z']/i", "", $s);
/*移除非预期字元，即英文字母及单引号(single quote)*/
$s1 = preg_split("/(\s|,)+/",preg_replace("/(\d+)-(\d+)/", "", $shops));
$s2 = preg_split("/\s+/",preg_replace("/(\d+(\s|,)+)|(\d+$)|(,+)/" ,"",
preg_replace("/(\d+)-(\d+)/", "BW_\$1_\$2_END ", $shops)));
$query_string_s1 = "";
$shop = current($s1);
do {
if(!emptyempty($shop)) {
if(emptyempty($query_string_s1))
$query_string_s1 = "_TABLE.shop = '". $shop ."'";
else
$query_string_s1 = $query_string_s1 . " or _TABLE.shop = '{$shop}'";
}
} while($shop = next($s1));
$query_string_s2 = "";
$shop = current($s2);
do {
if(!emptyempty($shop)) {
if(emptyempty($query_string_s2))
$query_string_s2 = "_TABLE.shop ".
preg_replace("/BW_(\d+)_(\d+)_END/","between '\$1' and '\$2' ",$shop);
else
$query_string_s2 = $query_string_s2 . " or _TABLE.shop ".
preg_replace("/BW_(\d+)_(\d+)_END/","between '\$1' and '\$2' ",$shop);
}
} while($shop = next($s2));
if(emptyempty($query_string_s1))
$query_string = $query_string_s2;
elseif(emptyempty($query_string_s2))
$query_string = $query_string_s1;
else
$query_string = $query_string_s1 ." or " . $query_string_s2;
return ("(". $query_string .")");
}
$grade_date = "2006-02-20";
$args['shop_query_string'] = parse_shop_string($_POST['shops']);
$products_stock_query_string = " and ". preg_replace("/_TABLE.shop /", "product_stock.shop ", $args['shop_query_string']);
$sql_query_string = "select product_stock.* from product_stock where grade_date='{$grade_date}' {$products_stock_query_string}";
function parse_shop_string($shops) {
if(empty($shops))
return "";
/*
分离 单店 与 区间 表示。
s1 = 单店
s2 = 区间
ex: shops = "000,003-005, 010 020, 230-240 301"
s1 = Array ( [0] => 000 [1] => 010 [2] => 020 [3] => 301 )
s2 = Array ( [0] => BW_003_005_END [1] => BW_230_240_END [2] => )
query_string = "(_TABLE.shop = 願' or _TABLE.shop = 顢' or _TABLE.shop = 顬' or _TABLE.shop = 馅' or _TABLE.shop between 顛' and 顝' or _TABLE.shop between 餾' and 饈')";
*/
$s = preg_replace("/[a-z']/i", "", $s);
/*移除非预期字元，即英文字母及单引号(single quote)*/
$s1 = preg_split("/(\s|,)+/",preg_replace("/(\d+)-(\d+)/", "", $shops));
$s2 = preg_split("/\s+/",preg_replace("/(\d+(\s|,)+)|(\d+$)|(,+)/" ,"",
preg_replace("/(\d+)-(\d+)/", "BW_\$1_\$2_END ", $shops)));
$query_string_s1 = "";
$shop = current($s1);
do {
if(!empty($shop)) {
if(empty($query_string_s1))
$query_string_s1 = "_TABLE.shop = '". $shop ."'";
else
$query_string_s1 = $query_string_s1 . " or _TABLE.shop = '{$shop}'";
}
} while($shop = next($s1));
$query_string_s2 = "";
$shop = current($s2);
do {
if(!empty($shop)) {
if(empty($query_string_s2))
$query_string_s2 = "_TABLE.shop ".
preg_replace("/BW_(\d+)_(\d+)_END/","between '\$1' and '\$2' ",$shop);
else
$query_string_s2 = $query_string_s2 . " or _TABLE.shop ".
preg_replace("/BW_(\d+)_(\d+)_END/","between '\$1' and '\$2' ",$shop);
}
} while($shop = next($s2));
if(empty($query_string_s1))
$query_string = $query_string_s2;
elseif(empty($query_string_s2))
$query_string = $query_string_s1;
else
$query_string = $query_string_s1 ." or " . $query_string_s2;
return ("(". $query_string .")");
}
$grade_date = "2006-02-20";
$args['shop_query_string'] = parse_shop_string($_POST['shops']);
$products_stock_query_string = " and ". preg_replace("/_TABLE.shop /", "product_stock.shop ", $args['shop_query_string']);
$sql_query_string = "select product_stock.* from product_stock where grade_date='{$grade_date}' {$products_stock_query_string}";
