Hello Monks!


i can run the programme /(see further below) with ease - it gives back the data in a large xml-file. Now i want to store it in a MySQL-database. This is what i have now:

Here there are the data: Data:
0 location:
1 name: mySchool
2 type: HighSchool
3 adress: Paris Champs Ellysees 1
4 description: Ecole Superieur


the database is structured with the following values:
`LOCATION` char(1) NOT NULL,
`NAME` varchar(130) NOT NULL,
`TYPE` varchar(40) DEFAULT NULL,
`ADRESS` smallint(6) NOT NULL,
`DESCRIPTION` varchar(30) NOT NULL,


And here the trials of doing it:

Connecting to a database:

use DBI;
$dbh = DBI->connect('DBI:mysql:databasename', 'username', 'password'
               ) || die "Could not connect to database: $DBI::errstr";
# (insert query examples here...)

i do the Simple query

$dbh->do('CREATE TABLE school_tbl (id INT, val VARCHAR(100))');

with the following values: 
  `LOCATION` char(1) NOT NULL,
  `NAME` varchar(130) NOT NULL,
  `TYPE` varchar(40) DEFAULT NULL,
  `ADRESS` smallint(6) NOT NULL,
  `DESCRIPTION` varchar(30) NOT NULL,


$dbh->do('INSERT INTO school_tbl VALUES(LOCATION, ?)', undef, 'Hello')
+;
$dbh->do('INSERT INTO school_tbl VALUES(NAME, ?)', undef, 'World');
$dbh->do('INSERT INTO school_tbl VALUES(TYPE, ?)', undef, 'Hello');
$dbh->do('INSERT INTO school_tbl VALUES(ADRESS, ?)', undef, 'World');
$dbh->do('INSERT INTO school_tbl VALUES(DESCRIPTION, ?)', undef, 'Hell
+o');

print "Insert the  $c rows\n";

$dbh->disconnect();
[download]

See the programme that runs with ease - but stores the data in a large file:

#!/usr/bin/perl

use strict;            
use warnings;           
use diagnostics;        
use File::Find::Rule;   
use HTML::TokeParser;  # parse HTML-files

# Array where all data should be stored in my @schools;
 
my $search_dir='.'; 


my $out_file='./output.xml';


my @files= File::Find::Rule->file()            # 
                ->name('einzelergebnis*.html') # 
                ->in($search_dir);             # 


for my $file (@files)
{
 
  print "running the job - parsing now: $file!\n";


  my %school;

  # start  a new parser-instcance
  my $p = HTML::TokeParser->new($file) or die "Can't open $file: ($!)"
+;

  
  while (my $tag = $p->get_tag('div', '/html'))
  {
    # first move to the right div that contains the information
    last if $tag->[0] eq '/html';
    next unless exists $tag->[1]{'id'} and $tag->[1]{'id'} eq 'inhalt_
+large';

    $p->get_tag('h1');
    $school{'location'} = $p->get_text('/h1');

    while (my $tag = $p->get_tag('div'))
    {
      last if exists $tag->[1]{'id'} and $tag->[1]{'id'} eq 'fusszeile
+';

      # get the school name from the heading
      next unless exists $tag->[1]{'class'} and $tag->[1]{'class'} eq 
+'fm_linkeSpalte';
      $p->get_tag('h2');
      $school{'name'} = $p->get_text('/h2');

      # verify format for school type
      $tag = $p->get_tag('span');
      unless (exists $tag->[1]{'class'} and $tag->[1]{'class'} eq 'sch
+ulart_text')
      {
        warn "unexpected format: parsing stopped";
        last;
      }

      $school{'type'} = $p->get_text('/span');

      # verify format for address
      $tag = $p->get_tag('p');
      unless (exists $tag->[1]{'class'} and $tag->[1]{'class'} eq 'ein
+zel_text')
      {
        warn "unexpected format: parsing stopped";
        last;
      }
      $school{'address'} = clean_address($p->get_text('/p'));

      # find the description
      $tag = $p->get_tag('p');
      $school{'description'} = $p->get_text('/p');
    }
  }

  # stpre a refenz on the  hash with the data of the actual school in 
+the Array with all schools
  push(@schools,\%school);
}

# output in a  file formated with  "XML" 
open(my $fh, '>', $out_file) or die("Error open $out_file ($!)\n");
print $fh "<schools>\n";
for my $school (@schools)#!/usr/bin/perl

{
  print $fh "  <school>\n";
  print $fh "    <name>$school->{name}</name>\n";
  print $fh "    <location>$school->{location}</location>\n";
  print $fh "    <type>$school->{type}<type>\n";
  print $fh "    <address>\n";
  for my $address (@{$school->{address}})
  {
    print $fh "      <entry>$address</entry>\n";
  }
  print $fh "    </address>\n";
  print $fh "    <description>$school->{description}</description>\n";
  print $fh "  </school>\n";
}
print $fh "</schools>\n";
close($fh);


# sanitize 
# give back as  Array each line 
sub clean_address
{
  my $text = shift;
  my @lines = split "\n", $text;
  for (@lines)
  {
    s/^s+//;
    s/s+$//;
  }
  return \@lines;
}
[download]

as mentioned above - the parser-programme works well - but i want to store the data in a mysql-database... this is intended.... How do do that!?

look forward to some ideas!

regards perlbeginner1