newtoperl101 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monkish gentlemen, Hope it is a serene time to look at a new problem and help a brother out. I have developed a phone contacts book, and here are the details.

Firstly, my program consists of a HTML file and a CGI file. The HTML form basically has two fields : one for Adding Contact , second for Searching contact using first&last names.

In case of Search submit, it prints the contact information, and displays two new submit buttons Update & Delete in a new form. Here is the problem:

In the Deletecontact() function, if I change the parameter $lname1 to a fixed value for testing, the sql works. It somehow isn't passing $lname1 on updating the CGI page with submit button. Same goes for Update() function.

Secondly with Update() function as I try to show the default values in the textfields of the new input form, it is only displaying the variable names instead of the values.

Here is my CGI file: 
#!C:\Dwimperl\perl\bin\perl.exe
# PERL MODULES WE WILL BE USING
use CGI;
use DBI;
use DBD::mysql;
use CGI::Carp 'fatalsToBrowser';
use CGI qw(:standard);

# Config DB variables
our $platform = "mysql";
our $database = "test";
our $host = "localhost";
our $port = "3306";
our $tablename = "addressbook";
our $user = "root";
our $pw = "password";
our $q = new CGI;
our $message;
our @rows;

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306"; 

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);


#Get the parameter from your html form.
$lname=$q->param('lname');
$fname=$q->param('fname');
$phone=$q->param('phone');
$email=$q->param('email');
$address=$q->param('address');
$zip=$q->param('zip');
$action=$q->param('action');
$lname1=$q->param('lname1');
$fname1=$q->param('fname1');
$phone1=$q->param('phone1');
$email1=$q->param('email1');
$address1=$q->param('address1');
$zip1=$q->param('zip1');

#Subroutines for each user function

sub addcontact{
$sql="INSERT INTO test.addressbook(last_name,first_name,address,zip,em
+ail,phone) values('$lname','$fname','$address','$zip','$email','$phon
+e')";
$sth = $connect->prepare($sql)
or die "Can't prepare $sql: $connect->errstrn";
$rv = $sth->execute;
if ($rv == 1){
  $message =  q(Record has been successfully updated!!!);
}else{
  $message =  q(<b style="color:red">Error!!while inserting records</b
+>);
}
}

sub search{
$sql="SELECT * FROM test.addressbook where last_name='$lname1' and fir
+st_name='$fname1'";
$sth = $connect->prepare($sql);
$sth->execute();
$ref = $sth->fetchall_arrayref;
foreach $row (@{$ref}){
    push @rows, @$row;
}
$rows=@rows;
if ($rows<1){
  $message =  q(<b style="color:red">Error! Record not found</b>);
}
return @rows;
}

sub deletecontact{
$sql="DELETE FROM test.addressbook where last_name='$lname1'";
$sth=$connect->prepare($sql);
$rd=$sth->execute;
if ($rd == 1){
  $message =  q(Record has been successfully deleted!!!);
}else{
  $message =  q(<b style="color:red">Error! Couldn't delete record</b>
+);
}
}

sub update{
$sql="UPDATE test.addressbook SET last_name='$lname1',first_name='$fna
+me1',address='$address1',zip='$zip1',email='$email1',phone='$phone1' 
+WHERE last_name='$lname1' and first_name='$fname1'";
$sth = $connect->prepare($sql);
$sth->execute();
$ru=$sth->execute;
}

# Choose action Add/Search/Delete/Update to call function
if ($action eq "Add"){
    addcontact()
  }
  elsif ($action eq "Search"){
    search()
  } 
  elsif ($action eq "Delete"){
    deletecontact()
  }
  elsif ($action eq "Update"){
    update()
  } 
  else {
    print " Got No parameters";
  } 


# Return to html page
my $URL = '/contactsform.html';
print $q->header( 
 -type=>"text/html");

#Print search results and ask for delete/update
print $q->start_html("Address Book"),
$q->p($message);
if ($action eq "Search"){
print
$q->h3("Search Results"),
$q->p("Last Name: $rows[1]"),
$q->p("First Name: $rows[2]"),
$q->p("Address: $rows[3]"),
$q->p("Zip: $rows[4]"),
$q->p("Phone: $rows[5]"),
$q->p("Email: $rows[6]"),
$q->start_form(),
$q->p(submit(-name =>'action',-value =>'Update'), submit(-name =>'acti
+on',-value =>'Delete')),
$q->end_form();
}

#Opens new form for updating entry
if ($action eq "Update"){
print $q->start_form(),        
$q->p("Last Name", textfield(-name  => 'lname1',-value => '$lname',)),
$q->p("First Name", textfield(-name  => 'fname1',-value => '$fname')),
$q->p("Address", textfield(-name  => 'address1',-value => '$address'))
+,
$q->p("Zip", textfield(-name  => 'zip1',-value => '$zip')),
$q->p("Phone", textfield(-name  => 'phone1',-value => '$phone')),
$q->p("Email", textfield(-name  => 'email1',-value => '$email')),
$q->p(submit("Update")),
$q->end_form();
}
print $q->p( qq!<a href="$URL">Back to Main Page</a>! );
print $q->end_html;

[download]
Here is the HTML form: 
<html>
<head><title>ADDRESS BOOK</title></head>
<body bgcolor="#FFFFFF" link="#0000FF" alink="#FF0000" vlink="#C000FF"
+>
<h1 align="center">ADDRESS BOOK CONTACTS</h1>

<div style="margin:0 auto;width:50%;text-align:center">
<form method="get" action="/cgi-bin/PROJECT/sqlconfig.cgi" style="disp
+lay:inline-block;background-color:#CCDFEF">
<table>
<h2> Add New Contact</h2>
  <tr>
  <td align="right">Last Name:</td>
  <td align="left"><input type="text" name="lname" size="15" maxlength
+="50"></td>
  <td align="right">First Name:</td>
  <td align="left"><input type="text" name="fname" size="15" maxlength
+="50"></td>
  </tr>
  <tr>
  <td align="right">Phone:</td>
  <td align="left"><input type="text" name="phone" size="15" maxlength
+="50"></td>
  <td align="right">Email:</td>
  <td align="left"><input type="text" name="email" size="15" maxlength
+="50"></td>
  </tr>
  <tr>
  <td align="right">Address:</td>
  <td align="left"><input type="text" name="address" size="15" maxleng
+th="100"></td>
  <td align="right">Zip Code:</td>
  <td align="left"><input type="text" name="zip" size="15" maxlength="
+50"></td>
  </tr>
  </table>
<input type="submit" name="action" value="Add" />
<h2>Search Contacts</h2>
<table>
  <tr>
  <td align="right">Last Name:</td>
  <td align="left"><input type="text" name="lname1" size="15" maxlengt
+h="50"></td>
  <td align="right">First Name:</td>
  <td align="left"><input type="text" name="fname1" size="15" maxlengt
+h="50"></td>
  </tr>
</table>
<input type="submit" name="action" value="Search" />
</form>
</div>
</body>
</html>

[download]
Please help.

Replies are listed 'Best First'.
Re: Passing form parameters same CGI page
by poj (Abbot) on Jul 08, 2013 at 19:42 UTC
    Take out the single quotes around the variables to get values not names 
    if ($action eq "Update"){
  print $q->start_form(),        
  $q->p("Last Name", textfield(-name  => 'lname1',-value => $lname,)),
  $q->p("First Name", textfield(-name  => 'fname1',-value => $fname)),
  $q->p("Address", textfield(-name  => 'address1',-value => $address))
+,
  $q->p("Zip", textfield(-name  => 'zip1',-value => $zip)),
  $q->p("Phone", textfield(-name  => 'phone1',-value => $phone)),
  $q->p("Email", textfield(-name  => 'email1',-value => $email)),
  $q->p(submit("Update")),
  $q->end_form();
}

    [download]
    poj
[reply]
[d/l]

      I removed the quotes, and what's weird the values show up in the url, but not in the textfield. (I changed textfield values to lname1, fname1 from lname and fname to display them) Actually, the action doesn't get updated when I hit update or delete. This is what comes up on hitting delete:

      http://localhost/cgi-bin/PROJECT/sqlconfig.cgi?lname=&fname=&phone=&email=&address=&zip=&lname1=Puma&fname1=Matthew&action=Search

      and in browser: Error! Couldn't delete record. Back to Main Page

      What's weird it satisfies action=update if condition, but doesn't pass the parameter or refresh the url.
[reply]

        Is this re-using a form at any point? Try copying-and-pasting that URL into a new window. That'll rule out anything to do with the form being part of your problem. (Which doesn't look to be at fault anyway.

        But it looks like 'DeleteContact' is being fired off when it really shouldn't be. I would be quite suspicious (and this is with the caveat - I don't have a test environment, and even if I did, the first thing to do would be reformat and use strict and warnings on your code).

        This bit would make me suspicious though:

            $q->start_form(),
    $q->p(submit(-name =>'action',-value =>'Update'), submit(-name =>'
+action',-value =>'Delete')),
    $q->end_form();

        [download]

        Having had a quick look at 'CGI' (and not much experience) it _seems_ this will create two buttons called 'action' with different values? And more importantly - the default for 'start_form' is that 'action' is POST. Not 'GET'.

        From: CGI Starting/ending forms

        "start_form() will return a <form> tag with the optional method, action and form encoding that you specify. The defaults are:

            method: POST
    action: this script

        [download]

        So I think it's possible that what's happening is you're resubmitting the same URL (with your 'GET' encoded parameters in the URL) but POSTing data to it too.

        From: Mixing POST and URL parameters "The param() method will always return the contents of the POSTed fill-out form, ignoring the URL's query string."

        So I think what you need to do is fix that bit - change the method to 'GET' and you'll at least see it doing the right thing. (But seriously - strict and warnings are good, and so is formatting your code cleanly. perltidy can make this easier)

[reply]
[d/l]
[select]
        You really need to start using placeholders (?) in your SQL like this. ; 
        sub deletecontact{
  my $message;
  my $sql = 'DELETE FROM test.addressbook where last_name = ?';
  my $sth = $connect->prepare($sql);
  my $rd = $sth->execute( $q->param('lname1') );
  if ($rd == 1){
    $message =  q(Record has been successfully deleted!!!);
  } else {
    $message =  q(<b style="color:red">Error! Couldn't delete record</
+b>);
  }
  return $message;
}

        [download]
        poj
[reply]
[d/l]
Re: Passing form parameters same CGI page
by Preceptor (Deacon) on Jul 08, 2013 at 19:30 UTC

    I can't reproduce your problem, because I don't have a suitable environment. My first thought is that you've got a single 'form' but two different buttons to 'submit' it. If you're getting the right parameters in the generated URL though, that'll _probably_ be ok.

    Failing that though - 'use strict;' and 'use warnings;' are you friends. They're _really_ good for picking out where things might be going wrong. But if as you've identifed, 'lname1' isn't getting passed, it's worth checking what the value of it - and $sql is, within the subroutines in question.

[reply]

Back to Seekers of Perl Wisdom