Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: How to get input text boxes populated

by poj (Priest)
on Jul 06, 2014 at 17:33 UTC ( #1092487=note: print w/ replies, xml ) Need Help??


in reply to How to get input text boxes populated

Select the data into a hash with the field names as keys. Note the use of a place holder in the query and a hidden field for the selected EmployeeID

my $sql = 'SELECT * FROM "Employees" WHERE "EmployeeID" = ?'; my $hr = $dbh->selectrow_hashref($sql,undef,$input{empid}); print qq! <form action="" method="post"> Employee ID : $hr->{'EmployeeID'}<br/> <input type="hidden" name="EmployeeID" value="$hr->{'EmployeeID'}"/> Last Name :<input name="lastname" value="$hr->{'LastName'}"/><br/> First Name :<input name="firstname" value="$hr->{'FirstName'}"/><br/> <input type="submit" name="go" value="FETCH"/> </form>!;
poj


Comment on Re: How to get input text boxes populated
Download Code
Re^2: How to get input text boxes populated
by terrykhatri (Acolyte) on Jul 07, 2014 at 06:35 UTC
    Hi Poj, As you must have guessed what I am trying to do here is to create an employees edit form in which like the delete form which you created select an employee from the drop down box which should bring up the employee data in another form or whatever ready to be edited. I know its too much to ask and I'm myself finding it hard to ask but if you can give me something like you did for the delete form that will be a life saver :). All the fields are listed above, whenever you have time. Many thanks Rgds Terry
      Hi, I have tried to write the edit script like Poj did but its throwing errors and I have no clue how to fix them : Here is the scrip :
      #!/usr/local/bin/perl use strict; use CGI; use CGI::Carp 'fatalsToBrowser'; #remove for prod use DBI; # get form parameters my $q = new CGI; my $action = $q->param('go'); my $empid = $q->param('empid'); my $dbh = dbh(); # connect to db $dbh->do("SET search_path to northwind") or die; # If the confirm form was properly submitted, update the record my $msg; # change validation to suit if ( ($action eq "UPDATE") && ($empid =~ /\d+/)) { my $sql = qq!UPDATE "Employees" SET "LastName" = "." \'$input{lastname}\', + "FirstName" = "." \'$input{firstname}\' +, "Title" = "." \'$input{title}\', "TitleOfCourtesy" = "." \'$input{toc}\', "BirthDate" = "." \'$input{dob}\', "HireDate" = "." \'$input{doh}\', "Address" = "." \'$input{address}\', "City" = "." \'$input{city}\', "Region" = "." \'$input{region}\', "PostalCode" = "." \'$input{pcode}\', "Country" = "." \'$input{country}\', "HomePhone" = "." \'$input{homephone}\' +, "Extension" = "." \'$input{ext}\', "Notes" = "." \'$input{notes}\', "ReportsTo" = "." \'$input{repto}\' WHERE "EmployeeID" = ? !; my $count = $dbh->do( $sql,undef,$empid ); $msg = "$count Record updated - $sql, $empid"; } else { $msg = "Please complete form"; } # get employees my $sql = qq!SELECT "EmployeeID" AS empid, "FirstName"::text || ' ' ||"LastName"::text AS name FROM "Employees" !; my $ar = $dbh->selectall_arrayref($sql); # Make up a pulldown menu my $options = qq!<option value="">select name</option>!; for my $row (@$ar) { $options .= qq!<option value="$row->[0]">$row->[1]</option>\n!; } # build html page my $style = q! body { background-color: pink ; color: #3300cc; } .container { width: 500px; clear: both; } .container input { width: 100%; clear: both;} !; # Send out the header and form print $q->header; print $q->start_html(-title=>'Update an employee record', -style=>{ -code=>$style } ); print qq!<h1 style="color:3300CC">Please update an employee</h1>!; # Fetch data if ( $action eq "FETCH" ) { #print qq!<h3>Please make the necessary updates to $empid ?</h3> my $sql = 'SELECT * FROM "Employees" WHERE "EmployeeID" = ?'; my $hr = $dbh->selectrow_hashref($sql,undef,$empid); #my $hr = $dbh->selectrow_hashref($sql,undef,$input{empid}); print qq! <form action="" method="post"> Employee ID : $hr->{'EmployeeID'}<br/> <input type="hidden" name="EmployeeID" value="$hr->{'EmployeeID'}"/> Last Name :<input name="lastname" value="$hr->{'LastName'}"/><br/> First Name :<input name="firstname" value="$hr->{'FirstName'}"/><br/> Title :<input name="title" value="$hr->{'Title'}"/><br/> Title Of Courtesy :<input name="toc" value="$hr->{'TitleOfCourtesy'}"/ +><br/> Birth Date :<input name="dob" value="$hr->{'BirthDate'}"/><br/> Hire Date :<input name="doh" value="$hr->{'HireDate'}"/><br/> Address :<input name="address" value="$hr->{'Address'}"/><br/> City :<input name="city" value="$hr->{'City'}"/><br/> Region :<input name="region" value="$hr->{'Region'}"/><br/> PostalCode :<input name="pcode" value="$hr->{'PostalCode'}"/><br/> Country :<input name="country" value="$hr->{'Country'}"/><br/> Home Phone :<input name="homephone" value="$hr->{'HomePhone'}"/><br/> Extension :<input name="ext" value="$hr->{'Extension'}"/><br/> Notes :<input name="notes" value="$hr->{'Notes'}"/><br/> <input type="submit" name="go" value="UPDATE"/> </form>!; my $count = $dbh->do( $sql,undef,$empid ); $msg = "$count Record fetched - $sql, $empid"; } else { print qq!<div class="container"> Select Employee to be updated : <form method="post" action=""> <select name="empid"> $options </select><br/> <input type="submit" name="go" value="FETCH"/> </form></div><hr/>!; # Standard links to the rest of the application print <<"FOOTER"; <b>$msg</b> <hr/> Jump to - <a href="emp2.pl">View Employees Listing</a><br/> Jump to - <a href="addemp.pl">Add an Employee</a><br/> Jump to - <a href="updatephoto.pl">Add or update Employee Photo</a><br +/> <hr/> Edited by Terry on July, 06 2014. FOOTER } print $q->end_html; # connect to database sub dbh { my $dsn = 'DBI:Pg:dbname=northwind;host=localhost'; my $user = 'postgres'; my $pwd = 'postgres'; my $dbh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1}); return $dbh; }
      Here are the errors:
      Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Global symbol "%input" requires explicit package name at /usr/share/pe +rlproj/cgi-bin/edit.pl line 19. Execution of /usr/share/perlproj/cgi-bin/edit.pl aborted due to compil +ation errors.
      Its complaining on $input and I don't know what to do, this format of script that I copied from Poj's style is much easier for you monks to review, I hope. Many thank for your help in advance. Rgds Terry
        Use placeholders (?) in your sql and extract the data from the parameters.
        if ( ($action eq "UPDATE") && ($empid =~ /\d+/)) { my @data=(); my @fields = qw!lastname firstname title toc dob doh address city region pcode country homephone ext notes repto!; for my $f (@fields)[ push @data,$q->param($f) || '' ; } push @data,$empid; my $sql = qq!UPDATE "Employees" SET "LastName" = ?, "FirstName" = ?, "Title" = ?, "TitleOfCourtesy" = ?, "BirthDate" = ?, "HireDate" = ?, "Address" = ?, "City" = ?, "Region" = ?, "PostalCode" = ?, "Country" = ?, "HomePhone" = ?, "Extension" = ?, "Notes" = ?, "ReportsTo" = ? WHERE "EmployeeID" = ? !; my $count = $dbh->do( $sql,undef,@data); $msg = "$count Record updated - $sql, @data"; } else { $msg = "Please complete form"; }
        poj
        Hi Poj, What happens now is when I press UPDATE it sends me back to the first screen to select an employee to be updated, its not updating anything, worse is that its not even sending update query to the database, can you PLEASE go over the script again to see where things are going wrong. Here is the updated script :
        #!/usr/local/bin/perl use strict; use CGI; use CGI::Carp 'fatalsToBrowser'; #remove for prod use DBI; # get form parameters my $q = new CGI; my $action = $q->param('go'); my $empid = $q->param('empid'); my $dbh = dbh(); # connect to db $dbh->do("SET search_path to northwind") or die; # If the confirm form was properly submitted, update the record my $msg; # change validation to suit if ( ($action eq "UPDATE") && ($empid =~ /\d+/)) { my @data=(); my @fields = qw!lastname firstname title toc dob doh address city region pcode country homephone ext notes !; for my $f (@fields){ push @data,$q->param($f) || '' ; } push @data,$empid; my $sql = qq!UPDATE "Employees" SET "LastName" = ?, "FirstName" = ?, "Title" = ?, "TitleOfCourtesy" = ?, "BirthDate" = ?, "HireDate" = ?, "Address" = ?, "City" = ?, "Region" = ?, "PostalCode" = ?, "Country" = ?, "HomePhone" = ?, "Extension" = ?, "Notes" = ?, WHERE "EmployeeID" = ? !; my $count = $dbh->do( $sql,undef,@data); $msg = "$count Record updated - $sql, @data"; } else { $msg = "Please complete form"; } # get employees my $sql = qq!SELECT "EmployeeID" AS empid, "FirstName"::text || ' ' ||"LastName"::text AS name FROM "Employees" !; my $ar = $dbh->selectall_arrayref($sql); # Make up a pulldown menu my $options = qq!<option value="">select name</option>!; for my $row (@$ar) { $options .= qq!<option value="$row->[0]">$row->[1]</option>\n!; } # build html page my $style = q! body { background-color: pink ; color: #3300cc; } .container { width: 500px; clear: both; } .container input { width: 100%; clear: both;} !; # Send out the header and form print $q->header; print $q->start_html(-title=>'Update an employee record', -style=>{ -code=>$style } ); print qq!<h1 style="color:3300CC">Please update an employee</h1>!; # Fetch data if ( $action eq "FETCH" ) { #print qq!<h3>Please make the necessary updates to $empid ?</h3> my $sql = 'SELECT * FROM "Employees" WHERE "EmployeeID" = ?'; my $hr = $dbh->selectrow_hashref($sql,undef,$empid); #my $hr = $dbh->selectrow_hashref($sql,undef,$input{empid}); print qq! <form action="" method="post"> Employee ID : $hr->{'EmployeeID'}<br/> <input type="hidden" name="EmployeeID" value="$hr->{'EmployeeID'}"/> Last Name :<input name="lastname" value="$hr->{'LastName'}"/><br/> First Name :<input name="firstname" value="$hr->{'FirstName'}"/><br/> Title :<input name="title" value="$hr->{'Title'}"/><br/> Title Of Courtesy :<input name="toc" value="$hr->{'TitleOfCourtesy'}"/ +><br/> Birth Date :<input name="dob" value="$hr->{'BirthDate'}"/><br/> Hire Date :<input name="doh" value="$hr->{'HireDate'}"/><br/> Address :<input name="address" value="$hr->{'Address'}"/><br/> City :<input name="city" value="$hr->{'City'}"/><br/> Region :<input name="region" value="$hr->{'Region'}"/><br/> PostalCode :<input name="pcode" value="$hr->{'PostalCode'}"/><br/> Country :<input name="country" value="$hr->{'Country'}"/><br/> Home Phone :<input name="homephone" value="$hr->{'HomePhone'}"/><br/> Extension :<input name="ext" value="$hr->{'Extension'}"/><br/> Notes :<input name="notes" value="$hr->{'Notes'}"/><br/> <input type="submit" name="go" value="UPDATE"/> </form>!; my $count = $dbh->do( $sql,undef,$empid ); $msg = "$count Record fetched - $sql, $empid"; } else { print qq!<div class="container"> Select Employee to be updated : <form method="post" action=""> <select name="empid"> $options </select><br/> <input type="submit" name="go" value="FETCH"/> </form></div><hr/>!; # Standard links to the rest of the application print <<"FOOTER"; <b>$msg</b> <hr/> Jump to - <a href="emp2.pl">View Employees Listing</a><br/> Jump to - <a href="addemp.pl">Add an Employee</a><br/> Jump to - <a href="updatephoto.pl">Add or update Employee Photo</a><br +/> <hr/> Edited by Terry on July, 06 2014. FOOTER } print $q->end_html; # connect to database sub dbh { my $dsn = 'DBI:Pg:dbname=northwind;host=localhost'; my $user = 'postgres'; my $pwd = 'postgres'; my $dbh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1}); return $dbh; }
        Many many thanks in advance. Rgds Terry
        Hi Poj, I changed it to empid, but the behavior is still the same, still its not executing the update query, no updates are being sent to the database and even worse no errors are being displayed. There's got to be something else. Rgds Terry
        I just ran Crap::Always against the script and got the following indication:
        perl -MCarp::Always edit2.pl Use of uninitialized value $action in string eq at edit2.pl line 22 (# +1)
        Which is the Action UPDATE line, makes any sense ? Tks Terry
        Norhing happened, it sends me back to start page where you select the employee to be updated, with following lines for the msg :
        Please complete form Debug : action=[UPDATE] empid=[]
        Tks Terry
        Hi Poj, First of all thank you very very much for all your help. The problem finally got solved, I had also posted it on PERLGURU and a Guru there his name is Chris noticed a typo in our script, I was calling a wrong hash key at:
        <input type="hidden" name="empid" value="$hr->{'empid'}"/>
        When I changed it to EmployeeID it worked. Thanks once again for your kind help. I have already voted you on this thread and other one. Rgds Terry
        Hi, What should be the Regex if data contains just alphabet letters for the following :
        if ( ($action eq "UPDATE") && ($custid =~ /\d+/)) {
        CustomeIDs are like :
        CustomerID ------------ ALFKI ANATR ANTON AROUT BERGS BLAUS BLONP BOLID
        Many thanks Terry
Re^2: How to get input text boxes populated
by terrykhatri (Acolyte) on Jul 07, 2014 at 07:31 UTC
    Hi Poj, Here is my script for editing employees which has become too complicated and is not working may be whenever you have time you can put it right if not that's absolutely ok !!!
    #!/usr/local/bin/perl use CGI; use DBI; # Make up a pulldown menu of all the employees for reports to field $db_handle = DBI -> connect("DBI:Pg:dbname=northwind;host=localhost", +"postgres", "postgres", {'RaiseError' => 1}); $query = "select \"EmployeeID\" AS repto, \"FirstName\"::text || ' ' | +|\"LastName\"::text as name from \"Employees\""; $db_handle->do("SET search_path to northwind") or die; $qh = $db_handle->prepare($query); $qh->execute; while (@row = $qh->fetchrow) { $hh .= "<option value=$row[0]>$row[1]</option>\n"; } $qry = "select \"EmployeeID\" AS empid, \"FirstName\"::text || ' ' ||\ +"LastName\"::text as name from \"Employees\""; $qq = $db_handle->prepare($qry); $qq->execute; while (@row = $qq->fetchrow) { $tt .= "<option value=$row[0]>$row[1]</option>\n"; } # Send out the header and form print "content-type: text/html\n\n"; print <<"HEADER"; <html> <head> <title>Updte en employee record</title> <body bgcolor=pink text=#3300CC border=2 bordercolor=pink > <h1 style="color:3300CC;">Please update an employee record</h1> <style type="text/css"> .container { width: 500px; clear: both; } .container input { width: 100%; clear: both; } </style> </head> <div class="container"> <form method=POST> Select an employee to be updated and press FETCH:<select name=empid>$t +t</select><br> <input type=submit name=go value=FETCH> <input type="hidden" name="employeeid" value="$ei->{'ei'}"/> Last Name :<input name="lastname" value="$ln->{'ln'}"/><br/> First Name :<input name="firstname" value="$fn->{'fn'}"/><br/> Title :<input name="title" value="$tl->{'tl'}"/><br/> Title Of Courtesy :<input name="toc" value="$tc->{'tc'}"/><br/> Birth Date :<input name="dob" value="$bd->{'bd'}"/><br/> Hire Date :<input name="doh" value="$hd->{'hd'}"/><br/> Address :<input name="address" value="$ad->{'ad'}"/><br/> City :<input name="city" value="$ct->{'ct'}"/><br/> Region :<input name="region" value="$rg->{'rg'}"/><br/> Postal Code :<input name="pcode" value="$pc->{'pc'}"/><br/> Country :<input name="country" value="$cy->{'cy'}"/><br/> Home Phone :<input name="homephone" value="$hp->{'hp'}"/><br/> Extention :<input name="ext" value="$xt->{'xt'}"/><br/> Notes :<input name="notes" value="$nt->{'nt'}"/><br/> Reports To :<select name=repto>$hh</select><br> <input type=submit name=go value=UPDATE> </div> </form><hr> HEADER # Read form inputs read(STDIN,$buffer,$ENV{CONTENT_LENGTH}); @pairs = split(/&/,$buffer); foreach (@pairs) { ($n,$v) = split(/=/); $v =~ tr/+/ / ; $v =~ s/%(..)/pack("C",hex($1))/ge; $input{$n} = $v; } # If the form has been completed, save data entered if ($input{"go"} eq "FETCH") { $query = "SELECT \"EmployeeID\" AS ei, \"LastName\" AS ln, \"FirstName\" AS fn, \"Title\" AS tl, \"TitleOfCourtesy\" AS tc, \"BirthDate\" AS bd, \"HireDate\" AS hd, \"Address\" As ad, \"City\" AS ct, \"Region\" AS rg, \"PostalCode\" AS pc, \"Country\" AS cy, \"HomePhone\" AS hp, \"Extension\" AS xt, \"Notes\" AS nt, \"ReportsTo\" AS rt FROM \"Employees\" WHERE \"EmployeeID\"="." \'$input{empid}\'"; $ins = $db_handle->prepare($query); $ins->execute; while (@row = $ins->fetchrow) { $ei .= "<input value=$row[0]></input>\n"; $ln .= "<input value=$row[1]></input>\n"; $fn .= "<input value=$row[2]></input>\n"; $tl .= "<input value=$row[3]></input>\n"; $tc .= "<input value=$row[4]></input>\n"; $bd .= "<input value=$row[5]></input>\n"; $hd .= "<input value=$row[6]></input>\n"; $ad .= "<input value=$row[7]></input>\n"; $ct .= "<input value=$row[8]></input>\n"; $rg .= "<input value=$row[9]></input>\n"; $pc .= "<input value=$row[10]></input>\n"; $cy .= "<input value=$row[11]></input>\n"; $hp .= "<input value=$row[12]></input>\n"; $xt .= "<input value=$row[13]></input>\n"; $nt .= "<input value=$row[14]></input>\n"; $rt .= "<input value=$row[15]></input>\n"; } #$db_handle -> do($ins); $action = "Record saved - $ins"; # If no form entered, ask user to complete one } else { $action = "Please complete form"; } # If the form has been completed, save data entered if ($input{"go"} eq "UPDATE") { $query = "UPDATE \"Employees\" SET \"LastName\" = "." \'$input{lastname}\', \"FirstName\" = "." \'$input{firstname}\', \"Title\" = "." \'$input{title}\', \"TitleOfCourtesy\" = "." \'$input{toc}\', \"BirthDate\" = "." \'$input{dob}\', \"HireDate\" = "." \'$input{doh}\', \"Address\" = "." \'$input{address}\', \"City\" = "." \'$input{city}\', \"Region\" = "." \'$input{region}\', \"PostalCode\" = "." \'$input{pcode}\', \"Country\" = "." \'$input{country}\', \"HomePhone\" = "." \'$input{homephone}\', \"Extension\" = "." \'$input{ext}\', \"Notes\" = "." \'$input{notes}\', \"ReportsTo\" = "." \'$input{repto}\' WHERE \"EmployeeID\"="." \'$input{employeeid}\'"; $db_handle -> do($query); $action = "Record saved - $query"; # If no form entered, ask user to complete one } else { $action = "Please complete form"; } # Links to other pages in the application print <<"FOOTER"; <b>$action</b> <hr> Jump to - <a href=http://localhost/perlproj/cgi-bin/emp2.pl>View Emplo +yees Listing</a><br> Jump to - <a href=http://localhost/perlproj/cgi-bin/delemp.pl>Delete a +n Employee</a><br> Jump to - <a href=http://localhost/perlproj/cgi-bin/updatephoto.pl>Add + or update Employee Photo</a><br> <hr> Edited by Terry on July, 06 2014. </body></html> FOOTER
    Rgds Terry

      Hi Poj, Here is my script for editing employees which has become too complicated and is not working may be whenever you have time you can put it right if not that's absolutely ok !!!

      You can do that at any time by actually using CGI and not that %input nonsense

      use CGI; is not a magic spell, if you don't use any of its methods (preferred) or functions, it won't do anything for you

      use CGI or die;

      "too complicated and is not working may"

      "not working" isn't an error report. Consider reading and understanding How do I post a question effectively?. You load CGI but seem to be ignoring it!? If you are starting a new project using CGI, don't. It's very old and has lots of issues. Consider using a modern framework like Dancer. Get into the habbit of reading the documentation and example code provided.

      Read the section on placeholders and bind variables from the DBI documentation. Never forget little Bobby Tables.

      If you insist on loading (or actually using) CGI work your way through Ovid's CGI Course. You'll learn how to actually use the features provided by the module and how to debug problems.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1092487]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2014-12-25 17:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (161 votes), past polls