1#! /usr/bin/perl -w 2 3# dynamic select boxes, using a db 4 5use strict; 6use CGI::Ajax; 7use CGI; 8use DBI; 9 10my $q = new CGI; 11 12### phone book database 13# CREATE TABLE `phonebook` ( 14# `login` varchar(10) NOT NULL, 15# `fullname` varchar(200) NOT NULL, 16# `areacode` int(10) unsigned NOT NULL default '123', 17# `phone` varchar(7) NOT NULL 18# ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Users and phone numbers'; 19# 20my $exported_fx = sub { 21 my $searchterm = shift; 22 my $sql = qq< select login from phonebook where login like ? or fullname like ? >; 23 my $dbh = DBI->connect('dbi:mysql:test:localhost','guestuser','guestp4ss'); 24 my $sth = $dbh->prepare( $sql ); 25 $sth->execute( $searchterm . '%', $searchterm . '%' ); 26 27 # start off the div contents with select init 28 my $html = qq!<select name="users" id="users" style="width:440px;" 29 onClick="details( ['users'],['ddiv'] ); return true;">\n!; 30 31 32 my $firstrow = $sth->fetch(); 33 if ( defined $firstrow ) { 34 $html .= qq!<option selected>! . $firstrow->[0] . qq!</option>\n!; 35 36 # dot on each option from the db 37 while ( my $row = $sth->fetch() ) { 38 # $row->[0] will contain the login name 39 $html .= qq!<option>! . $row->[0] . qq!</option>\n!; 40 } 41 42 } 43 # close off the select and return 44 $html .= qq!</select>\n!; 45 46 return($html); 47}; 48 49my $get_details = sub { 50 my $login = shift; 51 my $sql = qq< select * from phonebook where login = ? >; 52 my $dbh = DBI->connect('dbi:mysql:test:localhost','guestuser','guestp4ss'); 53 my $sth = $dbh->prepare( $sql ); 54 $sth->execute( $login ); 55 56 my $html = ""; 57 58 my $row = $sth->fetch(); 59 if ( defined $row ) { 60 $html .= "Login: " . $row->[0] . "<br>"; 61 $html .= "Full Name: " . $row->[1] . "<br>"; 62 $html .= "Area Code: " . $row->[2] . "<br>"; 63 $html .= "Phone: " . $row->[3] . "<br>"; 64 } else { 65 $html .= "<b>No Such User $login</b>\n"; 66 } 67 return($html); 68}; 69 70 71my $Show_Form = sub { 72 my $html = ""; 73 $html .= <<EOT; 74<HTML> 75<HEAD><title>CGI::Ajax Example</title> 76</HEAD> 77<BODY> 78 Who are you searching for?<br> 79 Start typing and matches will display in the select box.<br> 80 Selecting a match will give you details. 81 <br> 82 <form> 83 <input type="text" name="searchterm" id="searchterm" size="16" 84 onkeyup="search( ['searchterm'], ['rdiv'] ); return true;"><br> 85 86EOT 87 88 $html .= dump_table(); 89 90 $html .= <<EOT; 91 <div id="rdiv" style="border: 1px solid black; width: 440px; 92 height: 80px; overflow: auto"></div> 93 <br> 94 <div id="ddiv" style="border: 1px solid black; width: 440px; 95 height: 80px; overflow: auto"></div> 96 97 <br><a href="pjx_dynselect.txt">Show Source</a><br> 98 </form> 99</BODY> 100</HTML> 101EOT 102 return $html; 103}; 104 105sub dump_table { 106 my $sql = qq< select login from phonebook >; 107 my $dbh = DBI->connect('dbi:mysql:test:localhost','guestuser','guestp4ss'); 108 my $sth = $dbh->prepare( $sql ); 109 $sth->execute(); 110 111 my $html = "<table><tr><th>Current Logins in DB</th></tr>"; 112 113 while ( my $row = $sth->fetch() ) { 114 $html .= "<tr><td>" . $row->[0] . "</td></tr>"; 115 } 116 117 $html .= "</table>"; 118 return($html); 119} 120 121my $pjx = CGI::Ajax->new( 122 search => $exported_fx, 123 details => $get_details 124 ); 125$pjx->JSDEBUG(1); 126$pjx->DEBUG(1); 127 128# not show the html, which will include the embedded javascript code 129# to handle the ajax interaction 130print $pjx->build_html($q,$Show_Form); # this outputs the html for the page 131