Table of Contents | Summary 1 | Step 8

Now that we've finished the search form, we need to write some methods to look in the database for records based on our search criteria and display the results. Before we do this, let's consider a few query guidelines:

Changes to Handler.pm

First, modify the %REQUIRED_PARAMS hash:

  my %REQUIRED_PARAMS = (
    start               => {},
    search              => {
      last_name         => [ 0, 0, 'Last Name' ],
      first_name        => [ 0, 0, 'First Name' ],
      prefix            => [ 0, 0, 'Prefix' ],
      city              => [ 0, 0, 'City' ],
      area_code         => [ 0, 0, 'Area Code' ],
      state             => [ 0, 0, 'State' ]
    }
  );
  

Here, we've added the next action method, search, and the parameters passed in from the form. Each parameters has an array reference associated with it. In the array ref, the first element is a boolean (0 or 1) value that indicates if the parameter is required. The second element is an integer that represents the minimum length the parameter can be. The last element is a text description of the parameter. We need to provide a hash key and array ref for each form element we're interested in passing to the action method. Doing so helps guarantee the required_parameters method called in the cgi will prepare only parameters we're interested in, no matter what key-value pairs the user attempts to pass in. Since we want the search to return all records if every form element is left blank, none of the parameters are required (hence all have a boolean value of 0).

Next, add the search action method:

  sub search {
    my ($self, @args) = @_;
    my $args = $self->_get_args(@args);
    my $error = $self->_check_args($args);

    if ($error) {
      $self->{_TEMPLATE}->{ERROR} = $error;
      return $self->start();
    }

    my $record = PEACE::AddressBook::Record->new($self->_db);
    $self->{_TEMPLATE} = {
      RESULTS => $record->search_records($args)
    };
    return $self->start();
  }
  

First, we'll pass in the argument array prepared in the cgi. Then, we'll call our internal method _get_args to put the arguments into a hash. Next, we call the internal method _check_args to verify the arguments are acceptable (this really isn't necessary, since none of the arguments are required, but it can't hurt either). We'll store any error messages in $error, and if there is an error, we'll set the ERROR template variable and call the start action method. Since we're setting a template variable, then calling start, we'll need to change the code in start that sets the other template variables (otherwise, start will overwrite the template hash and remove the ERROR value). The template part of start should be changed from:

  $self->{_TEMPLATE} = {
    AREA_CODES => $record->select_distinct_area_codes(),
    STATES => $record->select_distinct_states()
  };
  

to:

  $self->{_TEMPLATE}->{AREA_CODES} = $record->select_distinct_area_codes();
  $self->{_TEMPLATE}->{STATES} = $record->select_distinct_states();
  

If there's no errors, we'll construct an object reference to Record.pm and call a new method, search_records, passing it the argument hash. Then, we'll call the start action method, so we can build the area_code and state array references for the search form (remember, the search form will be included with the results).

Changes to Record.pm

Add the search_records method:

  sub search_records {
    my ($self, $args) = @_;

    my $query = PEACE::AddressBook::SQL->search_start();
    my @args;

    foreach (keys(%$args)) {

      if ($args->{$_}) {
        my $action = "search_by_" . $_;
        $query .= PEACE::AddressBook::SQL->$action();
        push(@args,
          ($_ eq 'last_name' || $_ eq 'first_name')
            ? ("%" . $args->{$_} . "%")
            : ($args->{$_})
        );
      }
    }

    $query .= PEACE::AddressBook::SQL->search_end();

    my $sth = $self->dbh->prepare($query);
    $sth->execute(@args);
    my @results;

    while (my $ref = $sth->fetchrow_hashref()) { push(@results, $ref); }

    $sth->finish();
    return \@results;
  }
  

First, we read in the argument hash. Then, we'll start calling methods in SQL.pm to create the query to search the database. For each argument key in the hash, we'll create an action variable $action to help us add to the query. Each element value that's TRUE will execute an SQL.pm method (based on the key value) that will add a line to the query, then push that element's value onto an argument array. Since we're going to do a 'like' search on the first and last names, we have to add a percent wildcard in front of and behind the value (we want to query by saying, 'find all records similar to this value' instead of 'find all records exactly like this value'). Afterward, we'll add on the final line of the query, prepare, execute, and put the result hash into an array. We'll pass a reference to the array back to Handler.pm.

Changes to SQL.pm

We're calling several SQL methods from the utility class Record.pm, so let's add these:

  sub search_start {
    return <<QEND;
    select A.last_name as LAST_NAME, A.first_name as FIRST_NAME,
           A.middle_name as MIDDLE_NAME, A.image as IMAGE
    from person A, address B, number C, person_address D, person_number E
    where A.person_id=D.person_id
    and A.person_id=E.person_id
    and B.address_id=D.address_id
    and C.number_id=E.number_id
  QEND
  }

  sub search_by_last_name {
    return <<QEND;
    and A.last_name like ?
  QEND
  }

  sub search_by_first_name {
    return <<QEND;
    and A.first_name like ?
  QEND
  }

  sub search_by_prefix {
    return <<QEND;
    and C.prefix=?
  QEND
  }

  sub search_by_city {
    return <<QEND;
    and B.city=?
  QEND
  }

  sub search_by_area_code {
    return <<QEND;
    and C.area_code=?
  QEND
  }

  sub search_by_state {
    return <<QEND;
    and B.state=?
  QEND
  }

  sub search_end {
    return <<QEND;
    order by A.last_name
  QEND
  }
  

We start out with a basic query that will return all records if called. Then, depending on which form parameters were passed in, we'll have to add the appropriate SQL to filter out the matching records. Finally, we add an order_by clause to sort our records.

Changes to search_form.tmpl

We'll need to add an ERROR tag:

  <TMPL_IF NAME=ERROR>
  <table>
  <tr>
  <td>
    <font color="#FF0000">
    <TMPL_VAR NAME=ERROR>
    </font>
  </td>
  </tr>
  </table>
  </TMPL_IF>
  

I usually like to put any error messages at the top, but feel free to put it anywhere you want. Notice we're not escaping the output, because we're including HTML tags in the error message (the _check_args method in Handler.pm).

We'll have to add a <TMPL_LOOP> for the results too:

  <table>
  <tr>
  <th>Last Name</th>
  <th>First Name</th>
  <th>Middle Name</th>
  <th>Image</th>
  </tr>

  <TMPL_LOOP NAME=RESULTS>
  <tr>
  <td><TMPL_VAR ESCAPE=HTML NAME=LAST_NAME></td>
  <td><TMPL_VAR ESCAPE=HTML NAME=FIRST_NAME></td>
  <td><TMPL_VAR ESCAPE=HTML NAME=MIDDLE_NAME></td>
  <td><TMPL_VAR ESCAPE=HTML NAME=IMAGE></td>
  </tr>
  </TMPL_LOOP>

  </table>
  

I put this under the search form, but again, you can put it anywhere in the template file you like. For now, we'll just include the image name. Later we'll wrap it in <a href> and <img src> tags (when we upload the images later, we'll also create a thumbnail to display here).

That's all the changes we need for the search utility to work. If you've added some test data, try the search utility out. You should see something like this:

Coming next - Methods to display record details.