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

In reply to DBI insert: Need to store results in a MySQL-database (not in a file) by Perlbeginner1

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.