help please with text to database...

[crienoloog]

I'm not a programmer but I desperatly need help with the following please... it was written by someone but never tested, and contains some small mistakes that I can't solve. The person is not available anymore, so here my plea for help.

I have a mailmessage looking like this: http://www.criens.nl/lambada/sailmailmessage.txt
A getmail perl script is looking at a Windows server mailbox:
http://www.criens.nl/lambada/getmail.txt
and puts the seperate information into a mysql database.
Here is where the problem lies. The message "b" in brackets is not taken out of the entire mailmessage and the database field is empty.
After the getmail checks every hour the mailbox and places the data in the database, a php script places the data in a website page using a Google Earth API.
The php looks like this: http://www.criens.nl/lambada/longlatphp.txt

How the whole thing looks like you can see here in the website: http://www.criens.nl/lambada/ and click the Lambada Position on the left. The reason why you see the text message correct is because I edit the mailmessage by hand in the database. a thing I like to do automatically.

I sincerely hope somebody can have a look at the script and help me out here. I'm not really looking at learning Perl, but just need help this instance. Any assistance would be appreciated.

Regards, Crienoloog

[KevinADC]

I maybe can help if you can clarify what the problem is in more detail. This makes no sense to me:

Here is where the problem lies. The message "b" in brackets is not taken out of the entire mailmessage and the database field is empty.

[KevinADC]

I may have figured it out, try this in place of your existing perl script:

#!/usr/bin/perl -wT
#   popchecker.pl


use warnings;
use lib 'd:/www/criens.nl/sailmailscript';
use Mail::POP3Client;
use DBI;
#
$pop3user   = '[e-mail]';
$pop3pass   = 'password';
$pop3host   = "pop.criens.nl";
#
$pop = new Mail::POP3Client( USER => $pop3user, 
              PASSWORD => $pop3pass,
              DEBUG     => 1,
              AUTH_MODE => PASS,
              HOST => $pop3host ) || die "Can't connect";

my $checked = 0;


sub storeurl
{
    my $long          = $_[0];
    my $lat           = $_[1];
    my $omschrijving  = $_[2];
    my $longneg       = $_[3];
    my $latneg        = $_[4];

    my $headers       = shift;
    my $dbhostname    = 'mysql.criens.nl';
    my $dbuser        = 'datebase username';
    my $dbpwd         = 'database password';
    my $dbbase        = 'database name';
    my $dbtabel       = 'longlat';
    my $sql           = "INSERT INTO longlat (longitude, latitude, tijd, omschrijving, longneg, latneg) 
       VALUES (?, ?, ?, ?, ?, ?)";

    my $dbh = DBI->connect( 'dbi:mysql:' . $dbbase, $dbuser,
       $dbpwd, { RaiseError => 1 }) || die
                "Database connection not made: $DBI::errstr";

    my $dbh = DBI->connect ("DBI:mysql:host=mysql.criens.nl;database=name", "name", "password", {PrintError => 0, RaiseError => 1});

    my $sth = $dbh->prepare( $sql );
    print qq~( $long, $lat, time(), $omschrijving, $longneg, $latneg )~;
    $dbh->disconnect();
}

sub main
{
    my $find;
    my $url;
    my $i;
    my $longi;
    my $lati;
    my $longitude;
    my $latitude;
    my $omschrijving;
    my $longneg = 0;
    my $latneg = 0;
    
    
    for ($i = 0 ; $i <= $pop->Count(); $i++) 
     {
         my @FOO = $pop->Body( $i );
         $longi = 0;
         $lati = 0;
         my $blog = 0;
             foreach $line (@FOO)
             {

                 if ($line =~ /\[b\]/){
                     $blog = 1;

                 }
                 if ($line =~ /\[c1\]p\:/){

                 @A = split(":", $line);

                 $longi 	= $A[1];
                 $lati	= $A[2];

                     if ($longi =~ /S/)
                     {
                        $longneg = 1;
                     }

                     if ($lati =~ /W/)
                     {
                         $latneg = 1;
                     }

               $longi  =~ s/\.//;
               $lati   =~ s/\.//;

               $longitude  = substr($longi, 0, -2);
               $longitude  =~ s/=B0/./;

               $latitude   = substr($lati, 0, -2);
               $latitude   =~ s/=B0/./;

                 }

                 if ($blog == 1){
                     $line =~ s/[\r\n]//g;
                     $line = $line . "<br>";
                     $omschrijving = $omschrijving . $line;
                     $line =~  s/\[b\]//;
                 }
                 if ($line =~ /\#\#\#/){$omschrijving =~ s/###//; $blog = 0;}
             }

             if ($longitude) {

                 storeurl($longitude, $latitude, $omschrijving, $longneg, $latneg);

                 $pop->Delete( $i );
             }
     }
     $pop->Close();
}

main();

[KevinADC]

in the code I posted, change this line:

print qq~( $long, $lat, time(), $omschrijving, $longneg, $latneg )~;

to:

$sth->execute( $long, $lat, time(), $omschrijving, $longneg, $latneg );

[crienoloog]

@KevinADC:
First of all: Thank you very much for your help.
I'm going to give your code a try and will let you know as soon as possible.
Kind regards,
C.

[crienoloog]

Thank you very much KevinADC !
I did a test, and it seems to work just fine.
I really appreciated your help.
Regards, Crienoloog