Extract data and store to db-CodePudding

I have some Question and really need help on this thing but i really dont know where to start from

I want to submit such data via html at once using text area

---START----
|FULLNAME: JANE DEO
|BINF : 492BBJJ
|PRICE: 10
|COUNTRY: GR
 ---END---

---START----
|FULLNAME: JOHN DEO
|BINF : K92BBJJ
|PRICE: 24
|COUNTRY: AS
 ---END---

my main point is i want to be able to store multiple data to database instead of inserting 1 by 1

so i want to store each data where is START and END

#!/usr/bin/perl -w
use DBI;
use CGI qw/:standard/;

my $CGI = CGI->new;
my $host = "localhost";
my $dbname = "";
my $usr = "";
my $pwd = '';
my $dbh_usr = DBI->connect("DBI:mysql:$dbname:$host", $usr, $pwd, {RaiseError => 1,}) 
              or die $DBI::errstr;
# $binf extracted from  data where is |BINF : 
# $price extracted from data where is |PRICE: 
# $info this is the whole ---START---- and  ---END---
my $upload = $CGI->param("data");      
if ($upload) {
    my $update_info = $dbh_usr->prepare("INSERT INTO ITEMS(user, pid, basnm, binf, info, status, price) VALUES(?,?,?,?,?,?,?)");
    $update_info->execute('join123', '898', 'iono', $binf, $info, 'Active', $price);
    $update_info->finish;                     
}              
$dbh_usr->commit;
$dbh_usr->disconnect;
print "Content-type: text/html\n\n";
print <<HTML;

<!DOCTYPE html>
<html>
<head>
</head>
<body>

<h1>The textarea</h1>

<form method="POST">
  <textarea name="data" rows="4" cols="50"></textarea>
  <br>
  <input type="submit" value="Submit">
</form>


</body>
</html>

HTML

CodePudding user response：

my @d1 = split /\n\n/, $upload;

my @data;

foreach (@d1) {
    my @a = split /\n/;
    $a[2] =~ m/\|BINF : (. )/;
    my $binf  = $1;
    $a[3] =~ m/\|PRICE: (. )/;
    my $price = $1;
    push @data, {binf => $binf, price => $price};
}

foreach (@data) {
    say $_->{binf};
    say $_->{price};
}

CodePudding user response：

Following sample demo code builds SQL query to add all data in one shot.

Repurpose the sample to your DB table structure.

use strict;
use warnings;
use feature 'say';

my($table,$record,@data,$re);

$table = 'tb_users';
$re    = qr/\|(\S )\s*:\s(.*)/;

while( <DATA> ) {
    if( /---START----/ .. / ---END---/ ) {
        $record->{$1} = $2 if /$re/;
    }
    if( / ---END--/ ) {
        push @data, $record;
        $record = undef;
    }
}

my @keys   = keys %{$data[0]};
my @values = map { "(" . join(',', map { "'$_'"} $_->@{@keys}) . ")" } @data;

my $query = "INSERT INTO $table
    (" . join(',', map { "`$_`" } @keys) . ")
VALUES
    " . join(",\n\t", @values) . ";";

say $query;

exit 0;


__DATA__

---START----
|FULLNAME: JANE DEO
|BINF : 492BBJJ
|PRICE: 10
|COUNTRY: GR
 ---END---

---START----
|FULLNAME: JOHN DEO
|BINF : K92BBJJ
|PRICE: 24
|COUNTRY: AS
 ---END---

Output

INSERT INTO tb_users
        (`BINF`,`COUNTRY`,`FULLNAME`,`PRICE`)
VALUES
        ('492BBJJ','GR','JANE DEO','10'),
        ('K92BBJJ','AS','JOHN DEO','24');