how populate Tables?

[wny2u]

Hi,
I need quick help with Perl
(Unfortunately, my level of Perl does not exceed "Hello, World"
program...)

Please, if you can - be very descriptive for the scripts - so, i can
customize it to fit my Queries.


Here is the problem:


I have a table similar to tbl_Have and I need to populate Tables
tbl_need1 and tbl_need2 with Data from tbl_Have.


Please note: to populate data only with the first 4 records
i.e. for the id =7
only first 4 records are used.


Please, let me know if you need additional explanation.
Thanks ahead!


PS. I'm using MySQL 4.0.


tbl_Have
___________
|id| data |
|_________|
|1 | a |
|1 | b |
|1 | c |
|2 | d |
|2 | e |
|7 | f |
|7 | g |
|7 | h |
|7 | i |
|7 | j |
|7 | k |
|9 | l |
|9 | m |
|3 | n |
__________|


tbl_need1 (only First 4 Records)
_____________
|id| data |
_____________|
|1 | a,b,c |
|2 | d,e |
|7 | f,g,h,i |
|9 | l, m |
|3 | n |
_____________|


tbl_need2 ( only 4 columns needed)
____________________________________
|id | data1 | data2 | data3 | data4 |
____________________________________|
|1 | a | b | c | |
|2 | d | e | | |
|7 | f | g | h | i |
|9 | l | m | | |
|3 | n | | | |
____________________________________|

 

[Gunnar Hjalmarsson]

Hi,
I need quick help with Perl

Quick? Then hire a consultant.

(Unfortunately, my level of Perl does not exceed "Hello, World"
program...)

http://learn.perl.org/

 

[Dr.Ruud]

(e-mail address removed) schreef:

Please, if you can - be very descriptive for the scripts -

Please read the Posting Guidelines.

 

[Bart Van der Donck]

I have a table similar to tbl_Have and I need to populate Tables
tbl_need1 and tbl_need2 with Data from tbl_Have.

Please note: to populate data only with the first 4 records
i.e. for the id =7
only first 4 records are used.

tbl_Have
___________
|id| data |
|_________|
|1 | a |
|1 | b |
|1 | c |
|2 | d |
|2 | e |
|7 | f |
|7 | g |
|7 | h |
|7 | i |
|7 | j |
|7 | k |
|9 | l |
|9 | m |
|3 | n |
__________|


tbl_need1 (only First 4 Records)
_____________
|id| data |
_____________|
|1 | a,b,c |
|2 | d,e |
|7 | f,g,h,i |
|9 | l, m |
|3 | n |
_____________|


tbl_need2 ( only 4 columns needed)
____________________________________
|id | data1 | data2 | data3 | data4 |
____________________________________|
|1 | a | b | c | |
|2 | d | e | | |
|7 | f | g | h | i |
|9 | l | m | | |
|3 | n | | | |
____________________________________|

#!/usr/bin/perl

# Assumption: I'm trusting [tbl_Have]![id] to be a numeric value.

# load needed modules
use strict;
use warnings;
use DBI;

# database connection info
my $name = 'DBname';
my $host = 'localhost';
my $user = 'DBuser';
my $pass = 'BDpassword';

# I'm using § as a chacaracter of which I suppose it
# will not appear in [tbl_Have]![data].
my $not_appear_in_data = '§';

# init hash, mandatory with 'strict' & 'warnings' turned on
my %f = ();

# load data from tbl_Have and tie to hash
my $db = DBI->connect("DBI:mysql:$name:$host",$user,$pass);
my $query = $db->prepare
("SELECT id, data FROM tbl_Have ORDER BY id");
$query->execute;
my $numrows = $query->rows;
while (@_ = $query->fetchrow_array)
{
$_[1] =~ s/,/$not_appear_in_data/g;
$_[1] =~ s/'/\\'/g;
$f{$_[0]} = '' unless $f{$_[0]};
$f{$_[0]} .= $_[1] . ','
unless scalar @{[$f{$_[0]} =~ /,/g]} > 3;
# thanks Larry Rosler, clpm July 13 1998
}
$query->finish;
$db->disconnect;

# walk through hash and do both insert-actions in MySQL
while ( my ($s,$w) = each %f)
{
$w =~ s/,$//;
my @nf = split /,/, $w;
$w =~ s/$not_appear_in_data/,/g;
$nf[$_] =~ s/$not_appear_in_data/,/g for (0..$#nf);
$nf[$_] = '' for ($#nf+1..3);

my $insert1 = DBI->connect("DBI:mysql:$name:$host",$user,$pass);
my $insert1_query = $insert1->prepare
("INSERT INTO tbl_need1 VALUES ($s,'$w')");
$insert1_query->execute;
$insert1_query->finish;
$insert1->disconnect;

my $insert2 = DBI->connect("DBI:mysql:$name:$host",$user,$pass);
my $insert2_query = $insert2->prepare
("INSERT INTO tbl_need2 VALUES
($s,'$nf[0]','$nf[1]','$nf[2]','$nf[3]')
");
$insert2_query->execute;
$insert2_query->finish;
$insert2->disconnect;
}