Oracle+PHP 简明手册

 

在简单的搜索框中添加类似 Google 的搜索操作符


作者：Nick Bollweg

让用户对最重要的信息进行即时访问。

本文相关下载：
示例代码与列表
Oracle 数据库 10g 特别版
Zend Core for Oracle

2006 年 5 月发表

平均每位用户执行的搜索次数巨大，并且仍在不断增加。 事实上，您的用户在应用程序中进行搜索所花费的时间比实际使用搜索结果的时间更多。 因此，您需要如何让用户更加方便的获得信息，从而提升数据对知识工人的价值。 充分掌握数据库和一些字符串处理将帮助用户找到他们需要的信息。 在本文中，我将介绍一种使用 PHP 和 Oracle，以模块化方式来扩展搜索功能的方法。

 

与我有关吗？

 

随着用户越来越习惯于衍生应用程序的优势，对其数据的“主人翁”感愈发强烈，在数据库中学习他们所需的新知识，许多数据模型也相应发展变化。 虽然这是一种典型的“发展型问题”，但仍然是一种问题。 本文中的示例应用程序提供了一个具有搜索功能的简单、灵活的报告工具，另外还提供了一个用户新近采用的界面：简单搜索框。

 

这个简单的应用程序在 PHP5 的基础上构建，大量使用面向对象的概念。数据模型为 Oracle 数据库 10g 特别版 (XE) 提供的 HR 模式。 通过 Zend Core for Oracle 可以很容易地连接 PHP 和 Oracle。

 

在进行一些修改后，用户几乎可以从 Web 服务器上的任何地方运行该示例应用程序。 您需要编辑 constants.inc 的前三个值，以便准确的反映本地环境的数据库用户、密码和连接字符串。示例代码中包含带有 admin.php（用于生成示例数据的脚本）的 Sales 实体和序列， 并且示例代码由几个类文件和一些包含大部分 "chrome" HTML 的高级文件构成。

 

踏上征程

 

示例应用程序是一种搜索工具，用于查看 HR 应用程序中的员工，其目标用户为常规的日常用户。 这些用户希望了解的重要信息包括：

  • 姓名
  • 电子邮件地址
  • 电话号码
  • 部门

精明的开发人员会发现所有这些数据都位于一个表格中，一个简单的查询操作就可以获取全部数据：

SELECT * FROM EMPLOYEES

这样就一切就绪了。 添加一些关于数据的表格标签，将数据上载到网页上，然后向用户显示如何使用 Control-F。是这样吗？

 

一分耕耘，一分收获

 

解决问题不会那么快的。 事实上，用户可能非常喜欢第一个应用程序，因此您必须对它进行扩展。 之后，用户又希望在查看员工的同时访问管理人员的信息。 因此，您添加了自连接 - HTML 表体积增加（但完善）。这时，用户将不再抱怨。

但在下一周，用户希望向其中添加销售信息，并添加一些下拉列表来执行过滤。 用户的需求不断增加，最后您逐步创建了一个几乎无法管理的应用程序、一些难看的、不提供正式文档的网页，致使用户的满意度下降。 这时，您开始感到苦恼。

 

也许回到最初会对您有所帮助。 用户希望查看基于各自标准的过滤列表。 他们只希望查看感兴趣的内容，而不是一次查看所有内容。 为解决这个问题，示例应用程序挑出上述流程的符号部分，将其浓缩为便于访问少量的一些 PHP 和 SQL 片断，这些片断可随应用程序一起扩展或扩充。

这个流程的基本单元是 操作符，它可以向 SQL 查询中一点点地添加标准。 在详细讨论操作符之前，我们应该查看需要修改的基础查询：

SELECT
  (a.EMPLOYEE_ID)                     "Employee ID", 
  (a.FIRST_NAME)                      "First Name", 
  (a.LAST_NAME)                       "Last Name", 
  (LOWER(a.EMAIL || '@widgetco.com')) "Email", 
  (a.PHONE_NUMBER)                    "Phone", 
  (a.DEPARTMENT_ID)                   "Department" 
FROM
  (EMPLOYEES) a 
ORDER BY 
  a.LAST_NAME asc

显然，与上述的“简单”查询相比，这个查询并没有增加许多价值。 然而，该查询中的一项重要改进是使用了别名。 它的作用有两方面： 节约（这是列表字符串驻留并显示在最终用户屏幕上的唯一位置）和解疑（随着查询的增多，为每个结果列赋予对用户有意义的名称将使情况变得简单）。 EmployeeSearcher 中的几行 PHP 定义了这个基础查询：

class EmployeeSearcher extends Searcher{

public function EmployeeSearcher( $q = "" , $db = null ){
    $this->Searcher( $q, $db );    
    $this->query->setMainTable( "EMPLOYEES" );
    $prefix = $this->query->getMainTableAlias();
    $baseColumns = array( '"Employee ID"'  =>  "$prefix.EMPLOYEE_ID", 
                          '"First Name"'   =>  "$prefix.FIRST_NAME",    
                          '"Last Name"'    =>  "$prefix.LAST_NAME", 
                          '"Email"'        =>  "LOWER($prefix.EMAIL ||
                                                                    '@widgetco.com')",
                          '"Phone"'        =>  "$prefix.PHONE_NUMBER", 
                          '"Department"'   =>  "$prefix.DEPARTMENT_ID");
    foreach ($baseColumns as   $column=>$alias){
      $this->query->addColumn( $alias, $column );
    }
  }
  
}

EmployeeSearcher 设定了舞台，下面将展示查询构造的真正含义。但首先，让我们详细了解一下操作符。

 

你好，操作符

 

每个操作符都可以为应用程序添加一些灵活性，并且可以改善最终输出结果。 这里有一个示例操作符，它的名称是 Name，允许根据员工的姓或名进行过滤。

class Name extends Operator{
  
  public function __construct() { 
    $this->setHint( 'Employee named', 'name' );
  }
  
  public function process( $item, $token ) {  
    $alias = $this->query->getMainTableAlias();
    $this->query->addWhere( 
      array( "lower({$alias}.first_name || {$alias}.last_name ) like",
              array( "%$item%" )
           )
    );
  }
  
}

在构造函数中，操作符通过设定提示来了解它希望成为哪种类型的操作符，以防用户查询。第一个值 hint 将用在应用程序的其他位置，以便为您忠实的用户提供一个信息表。 以下是用户访问此功能时必须键入的信息： 搜索操作符和一些文本。在此情况下，在搜索框中键入

 

name:Jo

 

将通知操作符删减员工列表，使其仅包含姓名中含有 "Jo" 的员工。 之后，系统将为用户提供 "Joshuas"、"Johnsons" 和所有其他以 "Jo" 开头的姓名列表，并允许用户发布一条关于公司中某个 John 的临时查询。

下面是另一个操作符，允许用户基于每个员工的经理进行过滤：

class Manager extends Operator{
  
  public function __construct() { 
    $this->setHint( 'Manager named',      'manager');
    $this->setHint( 'Manager not named',  '~manager');
    $this->hasNascent = true;
  }
  
  public function process( $item , $token ) {  
    $alias = $this->query->getMainTableAlias();
    $joinAlias = $this->ensureJoin();    
    
    $not = "";
    
    if( $token == '~manager' ){
      $not = "not";
    }
    
    $this->query->addWhere(
      array(
        "lower({$joinAlias}.first_name || {$joinAlias}.last_name ) $not like",
        array( "%$item%" )
           )
    );
    $this->hasActed = true;
  }
  
  protected function ensureJoin(){
    $alias = $this->query->getMainTableAlias();
    if( $this->hasActed ){
      $joinAlias = $this->query->getPrefix( 'employees' );
} else {
      $joinAlias = $this->query->addTable('employees');
      $this->query->addWhere( array( "$alias.manager_id = $joinAlias.employee_id" ) );  
      $this->query->addColumn( "$joinAlias.last_name", '"Manager Last Name"' );
    }
    return $joinAlias;
  }
}

事实上，这个操作符中提供了两个搜索操作符。 正如建议所提示，添加代字号后改变了查询，禁止任何匹配输入的经理在屏幕上显示。 此外，此操作符提供了 额外的列。 无论用户的操作是否激活了这些列，操作符都可以进行添加。 在本示例中，操作符在初期状态下可以确保将新表连接到查询，并向其中添加新的列。 同时，如果提供适当搜索操作符的用户激活了操作符，查询将再次改变，类似于上方的 Name 操作符。 在启用 Manager 操作符的情况下指定以下搜索字符串将生成以下查询：

                               
manager:king name:john

SELECT    (a.EMPLOYEE_ID) "Employee ID", 
          (a.FIRST_NAME) "First Name", 
          (a.LAST_NAME) "Last Name", 
          (LOWER(a.EMAIL || '@widgetco.com')) "Email", 
          (a.PHONE_NUMBER) "Phone", 
          (a.DEPARTMENT_ID) "Department", 
          (b.LAST_NAME) "Manager Last Name" 
FROM      (EMPLOYEES) a, 
          (EMPLOYEES) b 
WHERE     a.MANAGER_ID = b.EMPLOYEE_ID 
  AND     LOWER(b.FIRST_NAME || b.LAST_NAME ) LIKE :a_1 
  AND     LOWER(a.FIRST_NAME || a.LAST_NAME ) LIKE :b_2 
ORDER BY  a.LAST_NAME asc

上述内容对于一个小型查询来说稍显拥挤，但它实现了目的，而且大部分繁琐的连接语句只需写一次。 当然，他成功解决了“与 King 一起工作的那个 John”这一老难题。

此处需要注意的问题是对绑定变量的使用。 两个绑定变量 a_1 和 b_2 分别绑定到 "%king%" 和 "%john"。 这些是来自用户的信息，因此可信度受到质疑。 通过使用绑定变量，我们可以获得缓存优势的同时防止用户脱离查询的输入。

 

查询来自何处？

 

除了上述文本示例，编写数字操作符也是一项简单的工作。 下面是 Sales 操作符的核心部分：

public function process( $item , $token ) {  
/* ... */
switch( $token ){
      case( 'salesabove' ):
      $this->query->addWhere( 
        array( "nvl($joinAlias.totalsales,0) >= ", 
                array( "$item" ) ) );
break;
      case( 'salesbelow' ):
      $this->query->addWhere( 
        array( "nvl($joinAlias.totalsales,0) <= ", 
                array( "$item" ) ) );
    }
/* ... */
protected function ensureJoin(){
/* ... */
$this->query->addColumn( "nvl($joinAlias.totalsales,0)", '"Total Sales"' );
/* ... */
$table = "select salesperson, count(*) totalsales from sale group by salesperson";

此操作符添加了一个完整的子查询 — 与在引入一个新列的上述示例中添加新连接表的方式相同 — 并启用了两个对比操作符。 这为用户提供了查询夏威夷行程中各个员工总销售额的完美方式。 然而，在查询级别汇总这些信息是有一定难度的。 我们需要暂时抛弃面向用户的工作方式，将精力集中到字符串处理上。

这种方法（在示例代码中称为 Query.class.php）搜索最基本的信息：

public function buildSQL(){
  $this->results = null;
  $columns = array();
  foreach( $this->columns as $columnAlias => $columnExpression ) {
    if( is_numeric( $columnAlias ) ){
      $columns[] = $columnExpression;
} else {
      $columns[] = "($columnExpression) $columnAlias";
    }
  }
  $columns = implode( ", ", $columns );
    
  $tables = array( '('.$this->mainTable.') '.$this->mainTableAlias );
  foreach( $this->tables as $tableAlias => $tableExpression ) {

    $tables[] = "($tableExpression) $tableAlias";
  }
  $tables = implode( ", ", $tables );
    
  $wheres = implode( ' '.$this->whereGlue.' ', $this->wheres );
  if( $wheres ){
    $wheres = " WHERE $wheres ";
  }
    
  $order = "{$this->mainTableAlias}.last_name asc";
    
  $this->sql = "SELECT $columns FROM $tables $wheres ORDER BY $order";
  $stmt = oci_parse( $this->db, $this->sql );
  foreach( $this->bindings as $bindName => $bindValue ){
    oci_bind_by_name( $stmt, $bindName, $this->bindings[$bindName] );
  }    
        oci_execute($stmt);
        oci_fetch_all ( $stmt, $this->results );
        oci_free_statement( $stmt );
return true;
}

在启动 EmployeeSearcher 之后，此方法将在执行操作符期间改进查询获得的所有信息。 列、表和 WHERE 子句都从各自的数组扩展，并使用各自的 SQL 别名作为数组的主键。 然而，查询的中间部分是来自文中前面内容的、熟悉的 SELECT 查询。 通过将 SQL 语句的复杂性分散到各个组成部分，您在不牺牲灵活性的同时获得了一种组合方法。 所有这些内容与适合数据模型的操作符相结合，可向用户提供一系列的搜索功能。

 

最后一步

 

搜索程序（在本示例中为 EmployeeSearcher）用于控制所有查询功能，并主要负责处理结果的格式化。 另外，搜索程序还负责处理操作符的动态加载。 下面的代码段显示了在上述表格标签中格式化的结果集：

private function formatResults( $columns ){
  $keys = array_keys( $columns );
  $results = "";
  foreach( $keys as $key ){
    $results .= "<th>$key</th>";
  }
  foreach( $columns[$keys[0]] as $row => $foo ){
    $temp = "";
    foreach( $keys as $key ){
      $temp .= '<td class="'.$this->cssClean( $key ).
                         "\">{$columns[$key][$row]}</td>\n";
    }      
    $results .= "<tr>$temp</tr>\n";
  }
  return "<table>$results</table> <div class=\"recordCount\">".
         count( $columns[$keys[0]] ).
         " records.</div>";
}

上面代码段的重点是对 td 标签中类的设置。 每个类都被赋予了与列名相近的可复写名称。列名用于根据查询结果和 main.css 中定义的 clever CSS 格式化结果。SearchPage 用于控制搜索程序，并负责处理页面布置和数据库连接的细节：

public function EmployeeSearchPage( $request ){
  $this->Page( $request );
  $this->setTitle( "Search" );
  if( isset( $request["q"] ) ){
    $this->db = oci_connect( WCO_DB_USER, WCO_DB_PASSWORD, WCO_DB_HOST );
    if (!$this->db) {
          exit;
        }
    $this->setTitle( "Search Results" );
    include_once( "search/EmployeeSearcher.class.php" );
    $searcher = new EmployeeSearcher( $request["q"] , $this->db);
    $this->setBody( $this->getBody() . $searcher->getResults() );
    oci_close( $this->db );
  }
}

最后的代码段（将标签放置在页面上）是简单的 search.php：

$page = new EmployeeSearchPage( $_REQUEST );
if( @$_REQUEST['q'] ){
  $page->setBody( $page->getBody() );
}
include_once( "index.php" );

如何利用操作符？

 

如果从开始就使用通俗易懂的语言描述所有操作符，将操作符提示处理成令人满意的格式不需要很长时间。 搜索程序中的下列方法将处理 HTML：

public function getHints(){
    $this->initOperators();      
$results = "";
    foreach($this->operators as $token => $operator){
      $results .= "<li>
                              
$token:" . 
                            $operator->getHint( $token ) . "</li>\n";
    }
    return "<ul>$results</ul>";
  }

以下方法的结果由 hints.php 放置到页面上，通过 search.php 进行链接：

                               
public function HintsPage( $request ){
  $this->Page( $request );
  $this->setTitle( "Search Hints" );
  include_once( "search/EmployeeSearcher.class.php" );
  $searcher = new EmployeeSearcher();
  $this->setBody( $this->getBody() . 
                  "Use the following operators to enhance your search:" .
                  $searcher->getHints() );
  
}

再做一次

 

由于描述相对复杂的过滤时唯一需要的表单输入是一个短字符串，用户可以轻松地为常用视图收藏这些位置。

 

根据 HR 数据模型的基础知识和各种用户需求逐步建立的示例应用程序与上述工作的模式基本相同，但最后的结果不同：通过在设计期间而不是执行期间制定决策，构建的系统具有扩展性，并带有内置用户文档。 对于用户来说，他们可以更有效地搜索日益增长的数据。

 

本示例是以员工为中心的 HR 数据的浏览器；使用新操作符，利用一个不同的基础查询定义新的搜索程序可以提供基于国家或部门的视图。示例代码中包含一些额外的操作符。进行扩展的其他方法还包括可更改的操作符（如 Group By 和 Order By 子句）、PHP 和 CSS 中的新演示和更好的演示，以及 Ajax 增强特性。

 

通过在用户数据与数据库相遇的所有地点使用绑定变量，我们消除了大部分 SQL 注入攻击危险。这对处理潜在的敏感信息非常重要。

 

结论

 

作为数据的前端，与此系统类似的可扩展系统允许用户执行搜索，并且几乎不需要维护。模块化和设计完善的数据模型使这些成为可能。如果早一些考虑用户文档，应用程序就可以一种简便、精确的方式提供文档，并有助于实现代码的结构化和可读性。 祝您的搜索工作顺利完成！

Nick Bollweg [nick.bollweg@gmail.com] 是马里兰州巴尔的摩市从事数据库和 Web 开发的自由职业者。

将您的意见发送给我们