Mercurial > rlmcintyre

     1 #!C:/strawberry/perl/bin/perl.exe

     2 

     3 # dynamic select boxes, using a db

     4 

     5 use strict;

     6 use CGI::Ajax;

     7 use CGI;

     8 use DBI;

     9 

    10 my $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 #

    20 my $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 

    49 my $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 

    71 my $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 

    86 EOT

    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>

   101 EOT

   102   return $html;

   103 };

   104 

   105 sub 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 

   121 my $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

   130 print $pjx->build_html($q,$Show_Form); # this outputs the html for the page
author	Robert McIntyre <rlm@mit.edu>
date	Mon, 27 Sep 2010 16:57:26 -0400
parents
children