Implementing a search engine

In order to implement a search engine, we need to make provision for multiple columns to be included in the search. In addition, it's important to recognize that the search item might be found in the middle of the field, and that very rarely will users provide enough information for an exact match. Accordingly, we will rely heavily on the SQL LIKE %value% clause.

How to do it...

First, we define a basic class to hold search criteria. The object contains three properties: the key, which ultimately represents a database column; the operator (LIKE, <, >, and so on); and optionally an item. The reason why an item is optional is that some operators, such as IS NOT NULL, do not require specific data:
```
namespace Application\Database\Search;
class Criteria
{
  public $key;
  public $item;
  public $operator;
  public function __construct($key, $operator, $item = NULL)
  {
    $this->key  = $key;
    $this->operator = $operator;
    $this->item = $item;
  }
}
```

Next we need to define a class, Application\Database\Search\Engine, and provide the necessary class constants and properties. The difference between $columns and $mapping is that $columns holds information that will ultimately appear in an HTML SELECT field (or the equivalent). For security reasons, we do not want to expose the actual names of the database columns, thus the need for another array $mapping:

namespace Application\Database\Search;
use PDO;
use Application\Database\Connection;
class Engine
{
  const ERROR_PREPARE = 'ERROR: unable to prepare statement';
  const ERROR_EXECUTE = 'ERROR: unable to execute statement';
  const ERROR_COLUMN  = 'ERROR: column name not on list';
  const ERROR_OPERATOR= 'ERROR: operator not on list';
  const ERROR_INVALID = 'ERROR: invalid search criteria';

  protected $connection;
  protected $table;
  protected $columns;
  protected $mapping;
  protected $statement;
  protected $sql = '';

Next, we define a set of operators we are willing to support. The key represents actual SQL. The value is what will appear in the form:

  protected $operators = [
      'LIKE'     => 'Equals',
      '<'        => 'Less Than',
      '>'        => 'Greater Than',
      '<>'       => 'Not Equals',
      'NOT NULL' => 'Exists',
  ];

The constructor accepts a database connection instance as an argument. For our purposes, we will use Application\Database\Connection, defined in Chapter 5, Interacting with a Database. We also need to provide the name of the database table, as well as $columns, an array of arbitrary column keys and labels, which will appear in the HTML form. This will reference $mapping, where the key matches $columns, but where the value represents actual database column names:
```
public function __construct(Connection $connection, 
                            $table, array $columns, array $mapping)
{
  $this->connection  = $connection;
  $this->setTable($table);
  $this->setColumns($columns);
  $this->setMapping($mapping);
}
```

After the constructor, we provide a series of useful getters and setters:

public function setColumns($columns)
{
  $this->columns = $columns;
}
public function getColumns()
{
  return $this->columns;
}
// etc.

Probably the most critical method is the one that builds the SQL statement to be prepared. After the initial SELECT setup, we add a WHERE clause, using $mapping to add the actual database column name. We then add the operator and implement switch() which, based on the operator, may or may not add a named placeholder that will represent the search item:

public function prepareStatement(Criteria $criteria)
{
  $this->sql = 'SELECT * FROM ' . $this->table . ' WHERE ';
  $this->sql .= $this->mapping[$criteria->key] . ' ';
  switch ($criteria->operator) {
    case 'NOT NULL' :
      $this->sql .= ' IS NOT NULL OR ';
      break;
    default :
      $this->sql .= $criteria->operator . ' :' 
      . $this->mapping[$criteria->key] . ' OR ';
  }

Now that the core SELECT has been defined, we remove any trailing OR keywords, and add a clause that causes the result to be sorted according to the search column. The statement is then sent to the database to be prepared:
```
  $this->sql = substr($this->sql, 0, -4)
    . ' ORDER BY ' . $this->mapping[$criteria->key];
  $statement = $this->connection->pdo->prepare($this->sql);
  return $statement;
}
```
We are now ready to move on to the main show, the search() method. We accept an Application\Database\Search\Criteria object as an argument. This ensures that we have an item key and operator at a minimum. To be on the safe side, we add an if() statement to check these properties:
```
public function search(Criteria $criteria)
{
  if (empty($criteria->key) || empty($criteria->operator)) {
    yield ['error' => self::ERROR_INVALID];
    return FALSE;
  }
```

We then call prepareStatement() using try / catch to trap errors:

try {
    if (!$statement = $this->prepareStatement($criteria)) {
      yield ['error' => self::ERROR_PREPARE];
      return FALSE;
}

Next we build an array of parameters that will be supplied to execute(). The key represents the database column name that was used as a placeholder in the prepared statement. Note that instead of using =, we use the LIKE %value% construct:

$params = array();
switch ($criteria->operator) {
  case 'NOT NULL' :
    // do nothing: already in statement
    break;
    case 'LIKE' :
    $params[$this->mapping[$criteria->key]] = 
    '%' . $criteria->item . '%';
    break;
    default :
    $params[$this->mapping[$criteria->key]] = 
    $criteria->item;
}

The statement is executed, and the results returned using the yield keywords, which effectively turns this method into a generator:

    $statement->execute($params);
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
      yield $row;
    }
  } catch (Throwable $e) {
    error_log(__METHOD__ . ':' . $e->getMessage());
    throw new Exception(self::ERROR_EXECUTE);
  }
  return TRUE;
}

How it works...

Place the code discussed in this recipe in the files Criteria.php and Engine.php under Application\Database\Search. You can then define a calling script, chap_10_search_engine.php, which sets up autoloading. You can take advantage of the Application\Database\Connection class discussed in Chapter 5, Interacting with a Database, and the form element classes covered in Chapter 6, Building Scalable Websites:

<?php
define('DB_CONFIG_FILE', '/../config/db.config.php');
require __DIR__ . '/../Application/Autoload/Loader.php';
Application\Autoload\Loader::init(__DIR__ . '/..');

use Application\Database\Connection;
use Application\Database\Search\ { Engine, Criteria };
use Application\Form\Generic;
use Application\Form\Element\Select;

You can now define which database columns will appear in the form, and a matching mapping file:

$dbCols = [
  'cname' => 'Customer Name',
  'cbal' => 'Account Balance',
  'cmail' => 'Email Address',
  'clevel' => 'Level'
];

$mapping = [
  'cname' => 'name',
  'cbal' => 'balance',
  'cmail' => 'email',
  'clevel' => 'level'
];

You can now set up the database connection and create the search engine instance:

$conn = new Connection(include __DIR__ . DB_CONFIG_FILE);
$engine = new Engine($conn, 'customer', $dbCols, $mapping);

In order to display the appropriate drop-down SELECT elements, we define wrappers and elements based on Application\Form\* classes:

$wrappers = [
  Generic::INPUT => ['type' => 'td', 'class' => 'content'],
  Generic::LABEL => ['type' => 'th', 'class' => 'label'],
  Generic::ERRORS => ['type' => 'td', 'class' => 'error']
];

// define elements
$fieldElement = new Select('field',
                Generic::TYPE_SELECT,
                'Field',
                $wrappers,
                ['id' => 'field']);
                $opsElement = new Select('ops',
                Generic::TYPE_SELECT,
                'Operators',
                $wrappers,
                ['id' => 'ops']);
                $itemElement = new Generic('item',
                Generic::TYPE_TEXT,
                'Searching For ...',
                $wrappers,
                ['id' => 'item','title' => 'If more than one item, separate with commas']);
                $submitElement = new Generic('submit',
                Generic::TYPE_SUBMIT,
                'Search',
                $wrappers,
                ['id' => 'submit','title' => 'Click to Search', 'value' => 'Search']);

We then get input parameters (if defined), set form element options, create search criteria, and run the search:

$key  = (isset($_GET['field'])) 
? strip_tags($_GET['field']) : NULL;
$op   = (isset($_GET['ops'])) ? $_GET['ops'] : NULL;
$item = (isset($_GET['item'])) ? strip_tags($_GET['item']) : NULL;
$fieldElement->setOptions($dbCols, $key);
$itemElement->setSingleAttribute('value', $item);
$opsElement->setOptions($engine->getOperators(), $op);
$criteria = new Criteria($key, $op, $item);
$results = $engine->search($criteria);
?>

The display logic mainly orients towards rendering the form. A more thorough presentation is discussed in Chapter 6, Building Scalable Websites, but we show the core logic here:

  <form name="search" method="get">
  <table class="display" cellspacing="0" width="100%">
    <tr><?= $fieldElement->render(); ?></tr>
    <tr><?= $opsElement->render(); ?></tr>
    <tr><?= $itemElement->render(); ?></tr>
    <tr><?= $submitElement->render(); ?></tr>
    <tr>
    <th class="label">Results</th>
      <td class="content" colspan=2>
      <span style="font-size: 10pt;font-family:monospace;">
      <table>
      <?php foreach ($results as $row) : ?>
        <tr>
          <td><?= $row['id'] ?></td>
          <td><?= $row['name'] ?></td>
          <td><?= $row['balance'] ?></td>
          <td><?= $row['email'] ?></td>
          <td><?= $row['level'] ?></td>
        </tr>
      <?php endforeach; ?>
      </table>
      </span>
      </td>
    </tr>
  </table>
  </form>

Here is sample output from a browser:

Previous Chapter

Building a binary search class

Next Chapter

Displaying a multi-dimensional array and accumulating totals

Table of Contents for PHP 7: Real World Application Development

Implementing a search engine

How to do it...

How it works...

Table of Contents for
PHP 7: Real World Application Development