Web Analytics Made Easy -
StatCounter Delete an Entry from DB-Help - CodingForum

Announcement

Collapse
No announcement yet.

Delete an Entry from DB-Help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete an Entry from DB-Help

    I searched in many tutorials and forums. Can any one help me to develop a simple code to delete an entry from a mysql database..

    username password

    johnson asdfg123

    philips 123456

    I need cgi/perl script which accepts the username value from an html form and deletes the entry in the DB.

  • #2
    Have you read the documentation for the DBI and DBD:mysql modules, which includes examples?
    http://search.cpan.org/~timb/DBI-1.607/DBI.pm
    http://search.cpan.org/~capttofu/DBD...b/DBD/mysql.pm

    What part are having trouble with, the retrieving the form submission or connecting to the db, or executing the sql delete?

    Please post the code you've tried.

    Comment


    • #3
      Code:
      #!/usr/bin/perl
      use DBI;
      require "connection_strings.cgi";
      
      print "Content-type: text/html\n\n" ;
      $requestor = "$ENV{'REMOTE_USER'}";
      
      $query = "insert into ipm(username, passwd, environ, deposit, requestor) values(";
      
      read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
      @pairs = split(/&/, $buffer);
      
      foreach $pair (@pairs)
      {
              ($name, $value) = split(/=/, $pair);
              $value =~ tr/+/ /;
              $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
              $query .= "\"$value\"";
      
              $query .= ",";
      
              if ($name eq "username")
              {
                      $uname = $value;
              }
      }
      
      $query .= "\"$requestor\")";
      
      $dbh = DBI->connect($connectInfo,$userid,$passwd) or die DBI->errstr();
      $sth = $dbh->prepare($query);
      $sth->execute;
      this is the code i am using to add info to the db. As I am new to this perl script, i dont know how to go to delete an entry from DB. from the html form, username and environment are passed. so the code should be, "delete from environment where username=". It would be great help if some one can help me in this.

      Comment


      • #4
        The first setp would be to add the warnings and strict pragmas and the CGI module.
        Code:
        use warnings;
        use strict;
        use CGI qw/:standard/;
        Next would be to delete this:
        Code:
        read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
        @pairs = split(/&/, $buffer);
        
        foreach $pair (@pairs)
        {
                ($name, $value) = split(/=/, $pair);
                $value =~ tr/+/ /;
                $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
                $query .= "\"$value\"";
        
                $query .= ",";
        
                if ($name eq "username")
                {
                        $uname = $value;
                }
        }
        and replace it with this:
        Code:
        my $uname = param("username");
        Your sql insert statement is specifying 5 fields, but you're only passing in 1 value. I doubt that's what you intended.

        Does this look like what you want?
        Code:
        #!/usr/bin/perl
        
        use warnings;
        use strict;
        use DBI qw/:standard/;
        require "connection_strings.cgi";
        
        print header(), start_html();
        
        my $uname = param("username");
        my $requestor = $ENV{'REMOTE_USER'};
        
        my $dbh = DBI->connect($connectInfo,$userid,$passwd, { RaiseError => 1 })
                  or die DBI->errstr();
        
        # my $sql = "insert into ipm(username, passwd, environ, deposit, requestor) values(?,?,?,?,?)";
        
        
        my $sth = $dbh->prepare("delete from ipm where username = ?");
        $sth->execute($uname);

        Comment


        • #5
          Thanks for your reply...

          Following is the code which passes the value to my delete_db.pl script. This script accepts value from an html form.

          Code:
          #!/usr/bin/perl
          print "Content-type: text/html\n\n" ;
          read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
          #@values = split(/&/,$ENV{QUERY_STRING});
          @values = split(/&/,$buffer);
          $userpair=shift(@values);
          $oupair=shift(@values);
          @user_arr = split(/=/, $userpair);
          @OU_arr = split(/=/, $oupair);
          $user=pop(@user_arr);
          $OU=pop(@OU_arr);
          
          system "sudo ./delete_db.pl $OU $user";
          exit 0;
          i modified the delete_db.pl in the following way.. Through command prompt it is deleting the DB entry. but when i call this through the above script, the script is running but the value is not getting deleted. Please help me to sort out the issue.

          Code:
          #!/usr/bin/perl
          
          use DBI;
          require "connection_strings.cgi";
          
          print "Content-type: text/html\n\n" ;
          
          my $uname = $ARGV[1];
          my $environ = $ARGV[0];
          $query = "delete from $environ where username=\'$uname\'";
          #print $query;
          
          $dbh = DBI->connect($connectInfo,$userid,$passwd);
          $sth = $dbh->prepare($query);
          $sth->execute() or die $dbh->errmsg();
          Last edited by aniwebapp; Apr 15, 2009, 07:18 AM.

          Comment


          • #6
            Why use 3 scripts when all you need is 1 simple script? Using your 3 scripts only serves to make it more difficult without a good reason.

            Combine all 3 scripts and have your form point to that 1 script.

            Code:
            #!/usr/bin/perl
            
            use warnings;
            use strict;
            use DBI qw/:standard/;
            
            print header(), start_html();
            
            my $connectInfo = 'DBI:mysql:dbname:server';
            my $userid = 'dbuser';
            my $passwd = 'dbpasswrd';
            my $requestor = $ENV{'REMOTE_USER'};
            
            my $uname = param('username');
            my $environ = param('environment');
            
            my $dbh = DBI->connect($connectInfo,$userid,$passwd, { RaiseError => 1 })
                      or die DBI->errstr();
            
            my $sth = $dbh->prepare("delete from $environ where username = ?");
            
            $sth->execute($uname);
            
            # output whatever else you need, then
            
            print end_html();
            Note that the script is lacking proper form validation and error handling on the user supplied info.
            Last edited by FishMonger; Apr 15, 2009, 08:46 AM.

            Comment


            • #7
              Thanks a lot for your help :-)

              Comment

              Working...
              X