# dynamic select boxes, using a db
use strict;
use CGI::Ajax;
use CGI;
use DBI;
my $q = new CGI;
### phone book database
# CREATE TABLE `phonebook` (
# `login` varchar(10) NOT NULL,
# `fullname` varchar(200) NOT NULL,
# `areacode` int(10) unsigned NOT NULL default '123',
# `phone` varchar(7) NOT NULL
# ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Users and phone numbers';
#
my $exported_fx = sub {
my $searchterm = shift;
my $sql = qq< select login from phonebook where login like ? or fullname like ? >;
my $dbh = DBI->connect('dbi:mysql:test:localhost','guestuser','guestp4ss');
my $sth = $dbh->prepare( $sql );
$sth->execute( $searchterm . '%', $searchterm . '%' );
# start off the div contents with select init
my $html = qq!<select name="users" id="users" style="width:440px;"
onClick="details( ['users'],['ddiv'] ); return true;">\n!;
my $firstrow = $sth->fetch();
if ( defined $firstrow ) {
$html .= qq!<option selected>! . $firstrow->[0] . qq!</option>\n!;
# dot on each option from the db
while ( my $row = $sth->fetch() ) {
# $row->[0] will contain the login name
$html .= qq!<option>! . $row->[0] . qq!</option>\n!;
}
}
# close off the select and return
$html .= qq!</select>\n!;
return($html);
};
my $get_details = sub {
my $login = shift;
my $sql = qq< select * from phonebook where login = ? >;
my $dbh = DBI->connect('dbi:mysql:test:localhost','guestuser','guestp4ss');
my $sth = $dbh->prepare( $sql );
$sth->execute( $login );
my $html = "";
my $row = $sth->fetch();
if ( defined $row ) {
$html .= "Login: " . $row->[0] . "<br>";
$html .= "Full Name: " . $row->[1] . "<br>";
$html .= "Area Code: " . $row->[2] . "<br>";
$html .= "Phone: " . $row->[3] . "<br>";
} else {
$html .= "<b>No Such User $login</b>\n";
}
return($html);
};
my $Show_Form = sub {
my $html = "";
$html .= <<EOT;
<HTML>
<HEAD><title>CGI::Ajax Example</title>
</HEAD>
<BODY>
Who are you searching for?<br>
Start typing and matches will display in the select box.<br>
Selecting a match will give you details.
<br>
<form>
<input type="text" name="searchterm" id="searchterm" size="16"
onkeyup="search( ['searchterm'], ['rdiv'] ); return true;"><br>
EOT
$html .= dump_table();
$html .= <<EOT;
<div id="rdiv" style="border: 1px solid black; width: 440px;
height: 80px; overflow: auto"></div>
<br>
<div id="ddiv" style="border: 1px solid black; width: 440px;
height: 80px; overflow: auto"></div>
<br><a href="pjx_dynselect.txt">Show Source</a><br>
</form>
</BODY>
</HTML>
EOT
return $html;
};
sub dump_table {
my $sql = qq< select login from phonebook >;
my $dbh = DBI->connect('dbi:mysql:test:localhost','guestuser','guestp4ss');
my $sth = $dbh->prepare( $sql );
$sth->execute();
my $html = "<table><tr><th>Current Logins in DB</th></tr>";
while ( my $row = $sth->fetch() ) {
$html .= "<tr><td>" . $row->[0] . "</td></tr>";
}
$html .= "</table>";
return($html);
}
my $pjx = CGI::Ajax->new(
search => $exported_fx,
details => $get_details
);
$pjx->JSDEBUG(1);
$pjx->DEBUG(1);
# not show the html, which will include the embedded javascript code
# to handle the ajax interaction
print $pjx->build_html($q,$Show_Form); # this outputs the html for the page