分析资料输入栏位，并转成 SQL 查询字句

Home Home
引用 | 编辑月光
2007-05-22 09:28
楼主
原文
http://blog.roodo.com/rocksaying/archives/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}";