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.&nbsp;
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