CODEWITHSUNDEEP
htmlperl
Armida
Armida

Reputation: 65

Deleting and updating data from database

I have created a code that connects to database and i want to delete data from database using a button the same for update. but i just can display data in a table and cant delete.

  my $q= new CGI;
    print $q->header;
    print $q-> start_html(
       -title   => "",
    );

    # print $q->start_form;
    ## mysql user database name
    my $db = "people";
    ## mysql database user name
    my $user = "root"; 
    ## mysql database password
    my $pass = "";

    ## user hostname : This should be "localhost" but it can be diffrent too
    my $host="127.0.0.1";

    ## SQL query
    my $query = "select ID,Name,Surname,Gender from person";

    my $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
    my $sqlQuery  = $dbh->prepare($query)
    or die "Can't prepare $sqlQuery: $dbh->errstr\n";
    my $rv = $sqlQuery->execute
    or die "can't execute the query: $sqlQuery->errstr";

    print start_form (-method => 'post', -action => "modify.pl" );

    my @aRows;

    while (my @data = $sqlQuery->fetchrow_array()) {      
       my $cRowId = hidden('ID', $data[0]);

       my $bt1 = submit('action','delete');
       my $bt2 = submit('action','update');

       push @aRows, ($cRowId, $q->Tr($q->td([$data[1], $data[2], $data[3],$bt1,$bt2])));
    }

    print $q->table({-border =>'1', -align =>'center',  -width => '100%'},
       $q->Tr([$q->th([ 'Name', 'Surname', 'Gender', 'Delete', 'Update', ])]),
       @aRows,
    );

    print $q->input({-type => 'button', -class => 'button', -onclick => "window.location.href='insert.pl';", -value => 'Shto'});

    print $q->end_form;  
    print $q->end_html;

delete.pl

use CGI;
use CGI qw(standard);
use DBI;
use CGI::Carp qw(set_die_handler);
use CGI qw/:all/;
    BEGIN {
       sub handle_errors {
          my $msg = shift;
          print "content-type: text/html\n\n";
          #proceed to send an email to a system administrator,
          #write a detailed message to the browser and/or a log,
          #etc....
      }

      set_die_handler(\&handle_errors);
}

my $q = CGI->new();



my $db = "people";
my $user = "root"; 
my $pass = "";
my $host="127.0.0.1";
my $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
my $action = $q->param('action'){
given ($action){
           when('delete'){
               my $row_id = $q->param('ID');
               my $sth = $dbh->prepare("DELETE FROM person WHERE ID = $row_id ") or die "Can't prepare $query: $dbh->errstr\n";
               my $rv = $sth->execute() or die $DBI::errstr;               
               print "deleted";
my $sth->finish();           
my $dbh->commit or die $DBI::errstr;
          }
    } }

I dont know where may be the problem

Upvotes: 0

Views: 243

Answers (3)

choroba
choroba

Reputation: 241918

Do not use my if you do not want a new variable. Remove all my's from the method calls:

my $sth->finish();           
my $dbh->commit or die $DBI::errstr;

Upvotes: 0

Dave Cross
Dave Cross

Reputation: 69274

The vast majority of Perl CGI problems can be solved by:

  • Adding use strict and use warnings to your code
  • Fixing all of the errors that now appear in your error log

Upvotes: 3

Quentin
Quentin

Reputation: 943615

You assign a value to $row_id after you try to use that variable to create your query.

Additionally, using raw user input in SQL queries makes you vulnerable to XSS attacks. Rewrite your code to use parameterized queries

Upvotes: 2

Related Questions