#!/usr/bin/env perl
use warnings;
use 5.014;
use utf8;
use Mojolicious::Lite;
use DBI;
my $table = 'my_test_table';
my $dbh = DBI->connect( 'dbi:SQLite:dbname=my_test_db.db', '', '',
{ RaiseError => 1, PrintError => 0, AutoCommit => 1, sqlite_unicode => 1, }
) or die $DBI::errstr;
$dbh->do( "CREATE TEMP TABLE $table ( firstname TEXT, lastname TEXT, id INTEGER UNIQUE )" );
my $sth = $dbh->prepare( "INSERT INTO $table ( firstname, lastname, id ) VALUES ( ?, ?, ?)" );
$sth->execute( 'Charlie', 'Harper', '321456' );
$sth->execute( 'Rachel Karen', 'Green', '253422' );
$sth->execute( 'John', 'Dorian', '433542' );
$sth->execute( 'Homer ☺', 'Simson', '433541' );
get '/eingabe';
get '/search_db/:opt' => sub {
my $self = shift;
my $opt = $self->param( 'opt' );
my $term = $self->param( 'term' );
my $ref;
if ( $opt eq 'autocomplete' ) {
my $sth = $dbh->prepare( "SELECT id FROM $table WHERE id LIKE ?" );
$sth->execute( $term . '%');
while ( my $row = $sth->fetchrow_arrayref() ) {
push @$ref, @$row;
}
} elsif ( $opt eq 'row' ) {
$ref = $dbh->selectall_arrayref( "SELECT * FROM $table WHERE id == ?", { Slice => {} }, $term );
die scalar @$ref if @$ref != 1;
$ref = $ref->[0];
}
$self->render( json => $ref );
};
app->start;
__DATA__
@@ eingabe.html.ep