Mojo database routing

rajaman has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am just starting to learn Mojolicious with a toy example:

1. User inputs query keywords (e.g. 'John Doe') in a search form (at home page '/')
2. The keywords are used to search a sqlite table with three columns (id, name, department)
3. The output page shows the search box above the result from sqlite: 'id, name, department' for John Doe (at page '/John Doe')

My raw pseudo-code with comments is below, but I am getting lost on how to connect different pieces together.

Please guide how such a task could be done efficiently.

Thanks a lot!

#!/usr/bin/env perl
use strict;
use warnings;
#use Mojo::SQLite;
use Mojolicious::Lite;
use DBI;

#hypnotoad
app->config(
    hypnotoad => {
        listen => [ 'http://127.0.0.1:8083/' ],
        proxy  => 1,
    },
);

# interact with database
my $dbh = DBI->connect("dbi:SQLite:mydb.db","","") or die "Could not c
+onnect";
helper db => sub { $dbh };

#get '/' => { text => q{
#       <form method="POST"><input name="q"><input type="submit" value
+="query"></form>
#}};
#my $query=param(q);

#how to pass 'John Doe' as a variable from the user input
helper select => sub {
  my $self = shift;
  my $sth = eval { $self->db->prepare('SELECT * FROM contacts where na
+me like '%John AND Doe%'') } || return undef;
  $sth->execute;
  return $sth->fetchall_arrayref;
};
app->select;

# setup route, such that search query gets appended to root in the out
+put, e.g., /John Doe for 'John Doe' search query. How to connect 'par
+am(q)' and ':query'?
get '/:query' =>
    sub {
        my $self = shift;
        my $query=param(q);
        my $rows = $self->select;
        $self->stash( rows => $rows );
        $self->render('index');
};
app->start;

__DATA__

@@ index.html.ep

<!DOCTYPE html>
<html>
<head><title>Emp</title></head>
<body>
<form method="POST"><input name="q"><input type="submit" value="John D
+oe"></form>
  <br><br>
  <table border="1">
    <tr>
      <th>id</th>
      <th>name</th>
      <th>department</th>
    </tr>
    % foreach my $row (@$rows) {
      <tr>
        % foreach my $text (@$row) {
          <td><%= $text %></td>
        % }
      </tr>
    % }
  </table>
</body>
</html>
[download]

Back to Seekers of Perl Wisdom