|
Adding Google-like Search Operators to Simple Search Boxes
by Nick Bollweg
Give your users instant access to your most important information.
Published May 2006
The range of search activities performed by the average user is large and growing. In fact, your users are probably spending more time searching in some applications than actually using the results they find there. So how do you make your information more easily available, increasing your data's value to knowledge workers? A good grasp of your database and some string manipulation can put users well on their way toward finding needed information. In this recipe, I'll offer a modular approach to extending your searching capabilities using PHP and Oracle.
What's It to Me?
Many data models grow and change as users become more accustomed to the benefits of derived applications, feel more ownership of their data, and learn new things they would like to remember in the database. This is the classic "Good Problem" to have, but is nevertheless a problem. This recipe's sample application provides a simple but flexible reporting tool with search capabilities along with an interface that users have newly embraced: the simple search box.
The sample application is built on PHP5 and uses object-oriented concepts heavily. The data model is the HR schema provided with Oracle Database 10g Express Edition (XE). PHP and Oracle connectivity is most easily achieved through Zend Core for Oracle .
The sample application will run from nearly anywhere on your Web server with few modifications. You will need to edit the first three values from constants.inc to accurately reflect your local environment's database user, password, and connection string. The Sale entity and sequence with admin.php, a script for generating sample data, is included in the sample code. The sample code consists of several class files and a few high-level files that contain most of the "chrome" HTML.
In the Beginning
The sample application is a search tool for looking at the employees in the HR application, with the target audience being regular, daily users. The important things these users would want to know include:
- Names, first and last
- Email address
- Telephone number
- Department
A savvy developer will note that all this data is in one table, and one little query will pull it all out:
SELECT * FROM EMPLOYEES
There you go, all set! Throw some table tags around this data, put it on a Web page, and show the user how to use Control-F. Right?
No Pain, No Gain
Not so fast. It turns out that the users love the first app so much that you have to expand it. Now users would also like access to manager information when looking at employees. So, you throw in a self-join—the HTML table is now larger (but complete), so there's nothing to complain about.
But the following week the users want to add sales information and some dropdowns to do filtering. This "scope creep" continues until you've been nickled-and-dimed into a nearly unmanageable application, some ugly, undocumented Web pages, and unhappy users. You feel the pain.
Perhaps returning to the beginning would help here. The users want to be able to look at a filtered list based on their criteria. They only want to see what's interesting, and may not want to see everything at once. To tackle this problem, the sample application has taken the symbolic parts of the process above and boiled them down into convenient morsels of PHP and SQL that expand or grow along with the application.
The atomic unit of this process is an operator, which incrementally adds criteria to an SQL query. Before we dive into operators, though, we should look at the base query that will be modifying:
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
This query apparently provides relatively little additional value over the "easy" query above. One improvement, however, is aggressive aliasing. It plays two roles: parsimony (this is the only place where the column table strings reside and show up on the end user's screen) and disambiguation (as the query grows, having a good, meaningful name in user jargon for every result column will keep things clear). A few lines of PHP in the EmployeeSearcher defines this base query:
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 );
}
}
}
The EmployeeSearcher sets the stage and demonstration of the actual means of query construction will follow, but first, let's take a closer look at operators.
Hello, Operator
Each operator adds a little bit of flexibility to the application and can also enhance the final output. Here's an example operator called Name, which allows for filtering of employees by first or last 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%" )
)
);
}
}
In the constructor, the operator lets itself know just what sort of an operator it wants to be, in case anyone asks, by setting a hint. The first value, the hint, will be used elsewhere in the application to provide a cheat sheet for your faithful users. The following text is what the user must type to access this functionality: the search operator and some text. In this case, typing
name:Jo
in the search box will tell this operator to reduce the list of employees to those whose first or last name contain "Jo." For their efforts, the user will be rewarded with all "Joshuas," "Johnsons," and other "Jo"-derivative names, and would allow them to quikly issue a casual inquiry about that-John-guy in the company.
Here is another operator, which gives access to filtering based on each employee's manager:
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;
}
}
This operator actually provides two search operators for the price of one! As the hint suggests, adding a tilde changes the query to prevent any managers matching the input from being displayed. Furthermore, this operator provides something else: additional columns. Columns can be added by an operator whether or not they are activated by a user's action. In this case, in its nascent state, the operator will ensure a new table is joined to the query and a new column is added. Also, if the user providing an appropriate search operator activates the operator, the query will be changed again, much like the Name operator above. Specifying the following search string with the Manager operator enabled will yield the following query:
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
Pretty crowded for a little query, but it gets the job done and most of the tedious joins are, again, only written once. Of course, this turns out to be a solution to the age-old that-John-guy-who-works-with-King conundrum as well.
Important to note here is the use of bind variables. The two bind variables, a_1 and b_2, are bound to "%king%" and "%john" respectively. This is information that came from the user and is therefore suspect. By using bind variables, we prevent a user from breaking out of the inputs to the query while garnering some cache advantages.
Where Do Queries Come From?
Aside from the textual samples above, numerical operators are also a cinch. Here are the highlights of the Sales operator:
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";
This operator adds a whole subquery—in the same manner as adding a new join table in the previous examples introduced a new column—and enables two comparative operators. This approach gives the user wonderful new ways to look at sales totals across different employees for that Hawaii trip. Putting all these together down at the query level, though, is a little tricky. We'll need to step aside from doing everything in a user-oriented fashion for a moment and focus on string manipulation.
This method (called Query.class.php in the sample code) goes through the nitty-gritty:
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;
}
This method progresses through all the things that the query has acquired in its time among the operators after starting with the EmployeeSearcher. Columns, tables, and WHERE clauses are all expanded from their arrays, with their SQL aliases as keys in those arrays. There in the center of our query, though, is a very familiar SELECT query from much earlier in the article. By diffusing the complexity of the SQL statement to its individual parts, you end up with a more modular approach without sacrificing much flexibility. All this, combined with a few data model-appropriate operators, gives the user access to a large array of potential search capabilities.
The Last Mile
Commanding all the functions of the query is the searcher, or in this particular case our EmployeeSearcher, which primarily handles the formatting of results. The searcher also handles the dynamic loading of operators. This snippet shows the result set being formatted inside those ugly table tags mentioned earlier:
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>";
}
The important point here is the setting of class in the td tags. Each class is issued a reproducible name that is close to the name of the column, which you can use to format the results depending on the output of the query and clever CSS, defined in main.css. The searcher is controlled by the SearchPage, which handles the finer points of page placement and connection to the database:
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 );
}
}
The last level, which actually puts tag to page, is the humble search.php:
$page = new EmployeeSearchPage( $_REQUEST );
if( @$_REQUEST['q'] ){
$page->setBody( $page->getBody() );
}
include_once( "index.php" );
So What Do I Do With This Thing?
By describing all of your operators in human terms from the beginning, it doesn't take much to put the operator hints into a pleasing format. This method in the searcher handles the HTML:
public function getHints(){
$this->initOperators();
$results = "";
foreach($this->operators as $token => $operator){
$results .= "<li><span class=\"token\">$token:</span>" .
$operator->getHint( $token ) . "</li>\n";
}
return "<ul>$results</ul>";
}
Putting the results of the following method on the page is hints.php, linked to by 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() );
}
Do it Again
As the only form input needed for describing a relatively complex filtering is a short string, these locations are easily bookmarked by users for often-used views.
Working up from basic knowledge of the HR data model and a few distinct user needs, the example application follows pretty much the same pattern of the work above, except for the outcome: By making the decision at design time instead of along the way at implementation time, you've got an extensible system with built-in user documentation. Back on the user side of things, they can seek through their ever-mounting data more efficiently.
This example is an employee-centric viewer of the HR data; defining a new searcher with a different base query could provide views based on countries or departments, with new operators. Some additional operators have been been included in the example code. Additional avenues for extension would be introducing Group By and Order By clauses as operator-modifiable, new and better presentation in PHP and CSS, and Ajax enhancements.
By using bind variable in all places where user data meets the database, we remove much of the danger of SQL injection attacks—which is important when working with potentially sensitive information.
Conclusion
As a front end for data, an extensible system like this one is user-explorable and requires little maintenance. Modularity, and a well-designed data model, makes that possible. By thinking about user documentation early, the application can provide it succinctly, accurately, and in a way that helps provide structure and readability to the code. Good luck on your explorations!
Nick Bollweg [nick.bollweg@gmail.com] is a freelance database and Web developer in the Baltimore, Martyland, area.
Send us your comments |
Bookmark
