CGI submit form and mysql

[martinasv]

Hi!

I have a problem (again). :-(

This script I wrote is supposed to update mysql table with new data. When I write in mysql: update table set nameMAC='something' where MAC='something else', everything works just fine. But my script just won't do it!

Here's my code, so if anyone can figure out what is it that I did wrong, please let me know!

Code:
_________________________
#! /usr/bin/perl
use CGI qwstandard);
use DBI;

$username = 'root';$password = '';$database = 'arp';$hostname = '';
$dbh = DBI->connect("dbi:mysql:database=$database;" .
"host=$hostname;port=3306", $username, );

my $q=new CGI;
$MAC=$q->param("id");
chomp($MAC);

print header;
print start_html(-title=>'Adding a NIC name',
-bgcolor=>"#EEEEEE");

print h1('Adding a NIC name'),
start_form,
"Type in your NIC name ",textfield('name'),
p,

submit (-name=>'imeMAC',
-value=>'Save'),
end_form,
hr;

my $name=$q->param('name');
print ($name); # this is just to check if $name contains what I want. It prints out exactly what I want in HTML.

$SQL="update promet set imeMAC='$name' where MAC='$MAC'";
$sql=$dbh->prepare($SQL);
$sql->execute();

print end_html;
_____________________________

Please, help!

 

[PaulTEG]

$SQL="update promet set imeMAC='$name' where MAC='$MAC'";
[COLOR=red]print "$SQL\n";[/color]
$sql=$dbh->prepare($SQL);
$sql->execute();$sql->execute() [COLOR=red]or die DBI->errstr[/color];

Try those two and see if anything makes more sense, also add

use CGI qw/fatalsToBrowser/;

too see if anything is echoed to the browser

HTH
--Paul

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[martinasv]

After adding those lines and trying it out, a thought came to me...

There is no action specified in my form, so Submit calls upon this same script, but it loses information about MAC along the way. That's why mysql statement in the end looks like this:

update table set nameMAC='something' where MAC=''.

Of course there is no MAC address '' in my table, so nothing is added to the nameMAC column.

I'll try to specify an action, e.g. starting a new script, with a task of updating a table.

If I encounter problems, I'll let you know.

 

[ChrisHunt]

Yeah, it can be tricky to get your head round how CGI works. It's not like a normal interactive program where you write a form, accept a response, and act upon it all in one pass. Instead, you do it in two stages - once to display the form and once to deal with the results.

Try doing something like this:

#! /usr/bin/perl
use CGI qw(:standard);
use DBI;

my $q=new CGI;
$MAC=$q->param("id");
chomp($MAC);
$NAME=$q->param("name");

if ( $NAME ) {

   # A name parameter is defined, so we must be coming
   # from the form. Connect and do the SQL...

   $username = 'root';$password = '';$database = 'arp';$hostname = '';
   $dbh = DBI->connect("dbi:mysql:database=$database;" .
       "host=$hostname;port=3306", $username, );
   $SQL="update promet set imeMAC='$name' where MAC='$MAC'";
   $sql=$dbh->prepare($SQL);
   $sql->execute();

   # You'll want to send some kind of feedback page here

} else {

   # No name defined, so show a form for the user to enter one

   print header;
   print start_html(-title=>'Adding a NIC name',
                    -bgcolor=>"#EEEEEE");

   print    h1('Adding a NIC name'),
      start_form,
      "Type in your NIC name ",textfield('name'),
      $q->hidden('id',$MAC), # Preserve value for next time round
      p,
      submit (-name=>'imeMAC',
              -value=>'Save'),
      end_form,
      hr,
      end_html;
}

So you call the script, it sees that there's no name parameter so it prints the form and finishes. The user fills in the form and submits to the same script, which this time executes the first bit of the [tt]if[/tt] statement and does the SQL.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd