时间戳

首页 > 解决方案 > 使用 ODBC 参数化案例语句

php - 使用 ODBC 参数化案例语句

问题描述

我正在使用与此类似的查询从 MS SQL DB 中选择数据：

$result = odbc_prepare(
   $connection, 
   "SELECT 
      t1.id, thumbnail, description, brand, vendor, 
      case 
         when ".implode(' AND ', $exactSearchTermDesc)." then 1 
         else 0 
      end as exactdescriptionmatch, 
      case 
         when ".implode(' AND ', $exactSearchTermMarkDesc)."". $exactThesaurusMarkDesc ." 
         then 1 else 0 
      end as exactcontentmatch, 
      case 
         when ".implode(' AND ', $searchTermDesc)."" . $thesaurusDesc ." then 1 
         else 0 
      end as descriptionmatch, 
      case 
         when ".implode(' AND ', $searchTermMarkDesc)."". $thesaurusMarkDesc ." then 1 
         else 0 
      end as contentmatch 
   FROM Martin.dbo.item_search"
);
odbc_execute($result);

如何参数化 case 语句中的变量？

例如，我尝试用这个参数化第一个变量无济于事......

$exactSearchTermDesc = implode(' AND ', $exactSearchTermDesc);
$result = odbc_prepare(
   $connection, 
   "SELECT 
      t1.id, thumbnail, description, brand, vendor, 
      case when ? then 1 else 0 end as exactdescriptionmatch, 
      case when ".implode(' AND ', $exactSearchTermMarkDesc)."". $exactThesaurusMarkDesc ." then 1 else 0 end as exactcontentmatch, 
      case when ".implode(' AND ', $searchTermDesc)."" . $thesaurusDesc ." then 1 else 0 end as descriptionmatch, 
      case when ".implode(' AND ', $searchTermMarkDesc)."". $thesaurusMarkDesc ." then 1 else 0 end as contentmatch 
   FROM Martin.dbo.item_search"
);
odbc_execute($result array($exactSearchTermDesc));

变量如下。$terms 是用户输入。

$searchTerms = explode(' ', $terms);
            $exactSearchTermDesc = array();
            $exactSearchTermMarkDesc = array();
            $searchTermDesc = array();
            $searchTermMarkDesc = array();
            foreach ($searchTerms as $term) {
                $term = trim($term);
                if (!empty($term)) {
                    $exactSearchTermDesc[] = "description LIKE '$term %'";
                    $exactSearchTermMarkDesc[] = "contains(marketingDescription, '$term')";
                    $searchTermDesc[] = "description LIKE '%$term%'";
                    $searchTermMarkDesc[] = "marketingDescription LIKE '%$term%'";
                    $searchTermVendor[] = "vendor LIKE '%$term%'";
                }
            }

$exactThesaurusDesc = " Or " . implode(' AND ', $exactThesaurusDesc); 
                $exactThesaurusMarkDesc = " Or " . implode(' AND ', $exactThesaurusMarkDesc);
                $thesaurusDesc = " Or " . implode(' AND ', $thesaurusDesc);
                $thesaurusMarkDesc = " Or " . implode(' AND ', $thesaurusMarkDesc);
                $thesaurusVendor = " Or " . implode(' AND ', $thesaurusVendor);

标签： phpsqlsql-serverodbc

解决方案

考虑重构您的 SQL 流程。与其在应用层（即 PHP）拼凑可能影响可读性和可维护性的动态 SQL 组件，不如考虑多个自连接到具有以下定义的临时术语表：

CREATE TABLE tmp_terms (
  id IDENTITY(1,1) NOT NULL,
  term VARCHAR(255),
  thesauraus_indicator BIT
)

具体来说，跨MIN返回 0 或 1 的连接匹配运行聚合查询。使用这种方法，可以维护相同的 SQL 查询，但 PHP 填充的基础术语将是动态组件。

CASE下面运行之前逻辑的非词库部分。此外，由于CONTAINS不能使用另一列而只能使用文字值，因此JOIN表达式使用LIKE前导通配符和尾随通配符。

SELECT 
   i.id, i.thumbnail, i.description, i.brand, i.vendor, 
   MIN(case 
           when exact_desc.term IS NOT NULL
           then 1 
           else 0 
       end) AS exact_description_match, 
   MIN(case 
           when market_match.term IS NOT NULL
           then 1 
           else 0 
       end) AS market_match, 
   MIN(case 
           when desc_match.term IS NOT NULL
           then 1 
          else 0 
       end) AS desc_match, 
   MIN(case 
           when vendor_match.term IS NOT NULL
           then 1 
           else 0 
       end) AS vendor_match 

FROM Martin.dbo.item_search i
LEFT JOIN tmp_terms exact_desc
   ON i.description LIKE CONCAT(exact_desc.term, ' %')
   AND exact_desc.thesaurus_indicator = 0
LEFT JOIN tmp_terms market_match
   ON i.marketingDescription LIKE CONCAT('%', market_match.term, '%')
   AND market_match.thesaurus_indicator = 0
LEFT JOIN tmp_terms desc_match
   ON i.description LIKE CONCAT('%', desc_match.term, '%')
   AND desc_match.thesaurus_indicator = 0
LEFT JOIN tmp_terms vendor_match
   ON i.vendor LIKE CONCAT('%', vendor_match.term, '%')
   AND vendor_match.thesaurus_indicator = 0

GROUP BY i.id, i.thumbnail, i.description, i.brand, i.vendor

要集成词库匹配，请UNION在外部查询聚合之前在 CTE 或子查询中使用。

WITH sub AS
   (SELECT 
       i.id, i.thumbnail, i.description, i.brand, i.vendor, 
       exact_desc.term AS exact_desc_term, market_match.term AS market_match_term, 
       desc_match.term AS desc_match_term, vendor_match.term AS vendor_match_term

    FROM Martin.dbo.item_search i
    LEFT JOIN tmp_terms exact_desc
       ON i.description LIKE CONCAT(exact_desc.term, ' %')
       AND exact_desc.thesaurus_indicator = 0
    LEFT JOIN tmp_terms market_match
       ON i.marketingDescription LIKE CONCAT('%', market_match.term, '%')
       AND market_match.thesaurus_indicator = 0
    LEFT JOIN tmp_terms desc_match
       ON i.description LIKE CONCAT('%', desc_match.term, '%')
       AND desc_match.thesaurus_indicator = 0
    LEFT JOIN tmp_terms vendor_match
       ON i.vendor LIKE CONCAT('%', vendor_match.term, '%')
       AND vendor_match.thesaurus_indicator = 0

    UNION

    SELECT 
       i.id, i.thumbnail, i.description, i.brand, i.vendor, 
       th_exact_desc.term, th_market_match.term, 
       th_desc_match.term, th_vendor_match.term

    LEFT JOIN tmp_terms th_exact_desc
       ON i.description LIKE CONCAT(th_exact_desc.term, ' %')
       AND th_exact_match.thesaurus_indicator = 1
    LEFT JOIN tmp_terms th_market_match
       ON i.marketingDescription LIKE CONCAT('%', th_market_match.term, '%')
       AND th_market_match.thesaurus_indicator = 1
    LEFT JOIN tmp_terms th_desc_match
       ON i.description LIKE CONCAT('%', th_desc_match.term, '%')
       AND th_desc_match.thesaurus_indicator = 1
    LEFT JOIN tmp_terms th_vendor_match
       ON i.vendor LIKE CONCAT('%', th_vendor_match.term, '%')
       AND th_vendor_match.thesaurus_indicator = 1
   )

SELECT sub.id, sub.thumbnail, sub.description, sub.brand, sub.vendor, 
       MIN(case 
               when sub.exact_desc_term IS NOT NULL
               then 1 
               else 0 
           end) AS exact_description_match, 
       MIN(case 
               when sub.market_match_term IS NOT NULL
               then 1 
               else 0 
           end) AS market_match, 
       MIN(case 
               when sub.desc_match_term IS NOT NULL
               then 1 
              else 0 
           end) AS desc_match, 
       MIN(case 
               when sub.vendor_match_term IS NOT NULL
               then 1 
               else 0 
           end) AS vendor_match 
FROM sub
GROUP BY sub.id, sub.thumbnail, sub.description, sub.brand, sub.vendor

注意：以上查询可能需要根据最终最终使用需求和测试进行调整。性能可能会因搜索词的数量而异。但最终，这个概念是与集合中的数据交互，而不是CASE WHEN...与搜索词增长的长连接逻辑。

最后，在 PHP 中，只需使用参数化清理并使用新值填充临时表，然后运行上述任何最终查询：

odbc_exec($connection, "DELETE FROM tmp_terms");

$stmt = odbc_prepare($connection,
                     "INSERT INTO tmp_terms (term, thesaurus_indicator) 
                      VALUES (?, ?)");

// NON-THESAURUS TERMS
foreach($search_terms as $t) {
   odbc_execute($stmt, array($t, 0));
}

// THESAURUS TERMS
foreach($th_search_terms as $t) {
   odbc_execute($stmt, array($t, 1));
}

// RUN FINAL SELECT QUERY
$result = odbc_exec($connection, "my_final_above_query");

while(odbc_fetch_row($result)){
         for($i=1; $i<=odbc_num_fields($result); $i++){
             // ... odbc_result($result,$i);
    }
}

推荐阅读