#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    2

    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.

    Code:
     my $q= new CGI;
        print $q->header;
        print $q-> start_html(
           -title 	=> "",
        );
        
        # print $q->start_form;
        ## mysql user database name
        my $db = "peoples";
        ## 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 * from person";
         
        
            my $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
            my $sqlQuery  = $dbh->prepare($query)
            or die "Can't prepare $query: $dbh->errstr\n";
             
            my $rv = $sqlQuery->execute
            or die "can't execute the query: $sqlQuery->errstr";
            
            
            while (@data = $sqlQuery->fetchrow_array()) { 
            	 
                   print start_form ( -method => 'post',-action => "update.pl" );
                    print hidden('ID',$data[0]);
            			$name_first = $data[1];
            			$name_last = $data[2];
            			$gender = $data[3];
            			$bt1 = submit('action','delete');
            			$bt2 = submit('action','update');
                   print "<br>";
            print $q->table({-border=>'1', -align=>'center',  -width => '100%'},
            		 $q->Tr([$q->th( [ 'Name', 'Surname', 'Gender','','', ] )]),
            
            		 $q->Tr([$q->td([$name_first,$name_last,$gender,$bt1,$bt2])])
            );
            }
            
            print $q->end_form;  
            print $q->end_html;
    delete.pl
    Code:
     my $cgi = CGI->new();
        my $db = "peoples";
        my $user = "root"; 
        my $pass = "";
        
        my $host="127.0.0.1";
        my $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
        my $action = $cgi->param('action');
        given ($action){
                   when('delete'){
                       my $row_id = $cgi->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 "U fshi";
            my $sth->finish();           
          my $dbh->commit or die $DBI::errstr;
                  }
            }
    I dont know where may be the problem
  2. #2
  3. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,264
    Rep Power
    1810
    I don't have time this morning for a complete answer, but might be able to get you thinking about how to approach the problem.

    Code:
     print hidden('ID',$data[0]);
            			$name_first = $data[1];
            			$name_last = $data[2];
            			$gender = $data[3];
            			$bt1 = submit('action','delete');
            			$bt2 = submit('action','update');
                   print "<br>";
            print $q->table({-border=>'1', -align=>'center',  -width => '100%'},
            		 $q->Tr([$q->th( [ 'Name', 'Surname', 'Gender','','', ] )]),
            
            		 $q->Tr([$q->td([$name_first,$name_last,$gender,$bt1,$bt2])])
    Looks like you are building a single button item for delete, and then reusing it in a loop for each row. That won't work because the button, or link, needs to have specific data in it to identify the row.

    For now, stay away from the Tr table shortcut and just build a simple loop which prints, so you can think about how to create the correct link. I'm not even sure a form is the best way, it would be enough to simply build a link. You can style the link however you want later.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    2
    Thank you , i solved it

IMN logo majestic logo threadwatch logo seochat tools logo