Extract data and store to db

frank1 has asked for the wisdom of the Perl Monks concerning the following question:

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---
[download]

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

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, basn
+m, binf, info, status, price)
                                    VALUES(?,?,?,?,?,?,?)");
                         
                          $update_info->execute('join123', '898', 'ion
+o', $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
[download]

Comment on Extract data and store to db Select or Download Code

Replies are listed 'Best First'.
Re: Extract data and store to db by LanX (Saint) on Nov 01, 2022 at 23:37 UTC
I can see two questions how to parse this text from a textarea and split up the data how to enter multiple rows into a table with just one sql statement. So which one are you asking? Cheers Rolf _{(addicted to the 𐍀𐌴𐍂𐌻 Programming Language :) Wikisyntax for the Monastery}	[reply]
Re^2: Extract data and store to db by frank1 (Monk) on Nov 02, 2022 at 11:07 UTC
Both	[reply]
Re^3: Extract data and store to db by LanX (Saint) on Nov 02, 2022 at 11:20 UTC
> Both second one, best do it in a loop. first one `use v5.12; use warnings; use Data::Dump qw/pp dd/; my @AoH; my $count = 0; while (<DATA>) { chomp; if ( /---START---/ .. /---END---/) { if ( /\\|([A-Z]+): (.+)/) { $AoH[$count]{$1}=$2; } $count++ if /---END---/; } } pp \@AoH; __DATA__ ---START---- \|FULLNAME: JANE DEO \|BINF : 492BBJJ \|PRICE: 10 \|COUNTRY: GR ---END--- ---START---- \|FULLNAME: JOHN DEO \|BINF : K92BBJJ \|PRICE: 24 \|COUNTRY: AS ---END---` [download] OUTPUT: `[ { COUNTRY => "GR", FULLNAME => "JANE DEO", PRICE => 10 }, { COUNTRY => "AS", FULLNAME => "JOHN DEO", PRICE => 24 }, ]` [download] Cheers Rolf _{(addicted to the 𐍀𐌴𐍂𐌻 Programming Language :) Wikisyntax for the Monastery}	[reply] [d/l] [select]