...

View Full Version : sendmail won't send mail...???



awayne96
12-09-2009, 07:55 AM
I am trying to add sendmail to a cgi script I have.

Well my first question is can I add it to an existing cgi script as I have below?

If I can, then why isn't it send the message? I was testing it with my $to ="myemail@tome.com\n"; just for testing purposes but I would need to use the email address for the users that are being notified. The first part of this script is sending a notification to my internal message system which is also suppose to send a message to the users external email but its not working and I can't figure out why.... so, I thought I would just add the sendmail into it so it finally works. This is the one thing I am trying to get working 100% before I want to launch the site.

If I cannot combine them then will I be able to call both scripts at the sametime without any problems?

Here is the code:


#!/usr/bin/perl -w
use CGI::Carp qw(fatalsToBrowser carpout);
use CGI qw(:standard);
use DBD::mysql;
use LWP::Simple;
use URI;
print "Content-type: text/html \n\n";

###############################
# find the root if this is IIS:
###############################
if($ENV{'SERVER_SOFTWARE'} =~ m/IIS/)
{
if($root eq "" && $ENV{'PATH_TRANSLATED'} ne "")
{
my $pt = $ENV{'PATH_TRANSLATED'};
my @pts = split(/\\/, $pt);
$pts[-1] = "";
$root = join "/", @pts;
$OS = "NT";
}
}

###############################
# functions library:
###############################
if(!$dontparse){
require "${root}data/config/pmpre.cfg";
require "${root}lib/mysql.lib";
require "${root}lib/pm.lib";
require "${root}lib/phpbb.lib" if($plugin_phpbb eq "yes");
}

###############################
# Read the data coming from the form..
###############################
if(!$dontparse){ &readit; }
###############################

if($use_cookie eq "yes" && !$dontparse && $input{'ID'}<1 && $input{'action'} ne "register"){
#- retrieve existing cookie
$lp = $req->cookie("pmpre");
my ($clogin, $cID) = split(/\|/, pm_decode($lp));
if(($clogin ne "") and ($cID ne "")){
if($input{'login'} eq ""){
$input{'login'} = $clogin;
}
if($input{'ID'} eq "" && $input{'password'} eq ""){
$input{'ID'} = $cID;
}
}

}


my $count = 0;
my $subject = "New posting in your Location";
my $flag = "1";
my $from = "admin";
my $time = &get_time;

$query = "SELECT MAX(mailno) FROM `mailbox`";
my $sth = $dbh->prepare($query);
$sth->execute();
my $data = $sth->fetchrow_array();
my $mailno = ($data + 1);


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures` WHERE `departures_states` = ? AND `departures_state_region` = ?" ) or die $DBI::errstr;
$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;
while(my $profiles = $profiles_sth->fetchrow_hashref()){
my $firstname = "$profiles->{'firstname'}";
my $login = "$profiles->{'login'}";
my $to = "$profiles->{'email'}";

# add personalized mail
my $body = "You have recent posts. <a href=pm.cgi?action=app_list&app=ship1&app_id=".$input{'app_id'}.">Click Here</a> to view it.";

my $mail_sth = $dbh->prepare("INSERT INTO `mailbox` (`mailno`, `time`, `newflag`, `from`, `to`, `subject`, `body`)
VALUES (?, ?, ?, ?, ?, ?, ?)") or die $DBI::errstr;
$mail_sth->execute($mailno, $time, $flag, $from, $login, $subject, $body) or die $DBI::errstr;
$mail_sth->finish;
$count = ($count + 1);
$mailno = ($mailno + 1);
}


my $sendmail = "/usr/sbin/sendmail -t";
my $from = "register@domain.com\n";
my $subject = "Subject: New Posting\n";
my $body = "Thanks for your submission.... testing.... testing...\n";
my $to = "tome@tome.com\n";


{

open(MAIL, "|$sendmail") || die
"Content-type: text/plain\n\nCan't open $sendmail: $!\n";
}

print MAIL "$to\n";
print MAIL "$from\n";
print MAIL "$subject\n";
print MAIL "$body\n";


close(MAIL);


$profiles_sth->finish;


print "<br>$count ";

if ($count == 1){
print "Member has";
} else {
print "members have";
}

print " been alerted about this post..<br><br>";
exit;

Thanks in advance for the help!

awayne96
12-09-2009, 10:27 AM
Also in playing around with the script I am also "at times" getting No recipient addresses found in header... Also got something about more then one "from"... I think thats what it was.

Just figured I would post more info as I continued to sort it out.

awayne96
12-10-2009, 12:01 AM
Whats up with the last post.... Was it spam?

But I am still looking for help lol :)

awayne96
12-10-2009, 05:28 AM
OK, so working on this a bit more I got it to work 100% when I had an actual email address entered on line my
$to = 'email@mydomain.com';

But what I need it to do is to send an email to the users that are getting notified on the internal message system. I thought by putting my
$email = "$profiles->{'email'}"; when it was fetching login and name that it would send the email to those addresses.... But I get "No recipient addresses found in header" as well as dead.letter error.

Can anyone shed any light on how to get this working 100%+? Once I have it working I will also want to know how to post the data into the email being sent that was entered into the database when the user is notified... But I will prob start a new thread for that.

Here is the code that I have updated now and thanks again for any and all help with this:


#!/usr/bin/perl -w
use CGI::Carp qw(fatalsToBrowser carpout);
use CGI qw(:standard);
use DBD::mysql;
use LWP::Simple;
use URI;
print "Content-type: text/html \n\n";

###############################
# find the root if this is IIS:
###############################
if($ENV{'SERVER_SOFTWARE'} =~ m/IIS/)
{
if($root eq "" && $ENV{'PATH_TRANSLATED'} ne "")
{
my $pt = $ENV{'PATH_TRANSLATED'};
my @pts = split(/\\/, $pt);
$pts[-1] = "";
$root = join "/", @pts;
$OS = "NT";
}
}

###############################
# functions library:
###############################
if(!$dontparse){
require "${root}data/config/pmpre.cfg";
require "${root}lib/mysql.lib";
require "${root}lib/pm.lib";
require "${root}lib/phpbb.lib" if($plugin_phpbb eq "yes");
}

###############################
# Read the data coming from the form..
###############################
if(!$dontparse){ &readit; }
###############################

if($use_cookie eq "yes" && !$dontparse && $input{'ID'}<1 && $input{'action'} ne "register"){
#- retrieve existing cookie
$lp = $req->cookie("pmpre");
my ($clogin, $cID) = split(/\|/, pm_decode($lp));
if(($clogin ne "") and ($cID ne "")){
if($input{'login'} eq ""){
$input{'login'} = $clogin;
}
if($input{'ID'} eq "" && $input{'password'} eq ""){
$input{'ID'} = $cID;
}
}

}


my $count = 0;
my $subject = "New posting in your Location";
my $flag = "1";
my $from = "admin";
my $time = &get_time;

$query = "SELECT MAX(mailno) FROM `mailbox`";
my $sth = $dbh->prepare($query);
$sth->execute();
my $data = $sth->fetchrow_array();
my $mailno = ($data + 1);


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures` WHERE `departures_states` = ? AND `departures_state_region` = ?" ) or die $DBI::errstr;
$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;
while(my $profiles = $profiles_sth->fetchrow_hashref()){
my $firstname = "$profiles->{'firstname'}";
my $login = "$profiles->{'login'}";
my $email = "$profiles->{'email'}";




# add personalized mail
my $body = "You have recent posts from your state.";

my $mail_sth = $dbh->prepare("INSERT INTO `mailbox` (`mailno`, `time`, `newflag`, `from`, `to`, `subject`, `body`)
VALUES (?, ?, ?, ?, ?, ?, ?)") or die $DBI::errstr;
$mail_sth->execute($mailno, $time, $flag, $from, $login, $subject, $body) or die $DBI::errstr;
$mail_sth->finish;
$count = ($count + 1);
$mailno = ($mailno + 1);
}

$profiles_sth->finish;


my $to = $email;
my $from = 'register@mydomain.com';
my $subject = 'New posting in your location';
my $body = 'Hi, testing location email';
my $header = "To: $to
CC: $cc
Bcc: $bcc
From: $from
Subject: $subject
Content-type: text/html

";

open(MAIL, '| /usr/sbin/sendmail -t -oi') || die "Unable to open sendmail";
print(MAIL
"${header}$body
");
close(MAIL);



print "<br>$count ";

if ($count == 1){
print "Member has";
} else {
print "members have";
}

print " been alerted about this post..<br><br>";
exit;

FishMonger
12-11-2009, 02:39 PM
Sorry for my delay in responding.

The first step is to run the script through perl's -c switch to see some of the problems.


C:\TEMP>perl -c awayne96.pl
Prototype mismatch: sub main::head: none vs ($) at awayne96.pl line 5
[Fri Dec 11 05:13:00 2009] awayne96.pl: "my" variable $from masks earlier declaration in same scope at awayne96.pl line 94.
[Fri Dec 11 05:13:00 2009] awayne96.pl: "my" variable $subject masks earlier declaration in same scope at awayne96.pl line 95.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::email" used only once: possible typo at awayne96.pl line 93.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::plugin_phpbb" used only once: possible typo at awayne96.pl line 31.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::bcc" used only once: possible typo at awayne96.pl line 97.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::req" used only once: possible typo at awayne96.pl line 42.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::use_cookie" used only once: possible typo at awayne96.pl line 40.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::cc" used only once: possible typo at awayne96.pl line 97.
[Fri Dec 11 05:13:00 2009] awayne96.pl: Name "main::OS" used only once: possible typo at awayne96.pl line 20.

When you declare a var with the 'my' keyword, that var is only visible within the block where it was defined. Which means that the $email var that you assigned on line 74 i.e.,
my $email = "$profiles->{'email'}";wont be accessible when you try to use it on line 93 i.e.,
my $to = $email; # this is actually a completely different $email var than the one assigned on line 74, it just happens to have the same name
If the script was using the strict pragma, like it should be doing, then perl would have told you about that error.

You're assigning $header a multi line string. You should treat that assignment as such and use the proper quoting method.
In this case that would be a here document.
my $header = <<"HEADER";
To: $to
CC: $cc
Bcc: $bcc
From: $from
Subject: $subject
Content-type: text/html

HEADER


Lines 107 through 109:
print(MAIL
"${header}$body
");
Should be written as:
print(MAIL "$header$body");or
print MAIL $header, $body;or
print MAIL $header . $body;

awayne96
12-11-2009, 06:15 PM
No Problem FishMonger, I appreciate the help when your able to get to it!

I made the changes and actually took out the part of the script that is sending the message to the internal system to try to get it working.... Well it is still giving me "No recipient addresses found in header"

Here is what I am currently working with now:


#!/usr/bin/perl -w
use CGI::Carp qw(fatalsToBrowser carpout);
use CGI qw(:standard);
use DBD::mysql;
use LWP::Simple;
use URI;
print "Content-type: text/html \n\n";

###############################
# find the root if this is IIS:
###############################
if($ENV{'SERVER_SOFTWARE'} =~ m/IIS/)
{
if($root eq "" && $ENV{'PATH_TRANSLATED'} ne "")
{
my $pt = $ENV{'PATH_TRANSLATED'};
my @pts = split(/\\/, $pt);
$pts[-1] = "";
$root = join "/", @pts;
$OS = "NT";
}
}

###############################
# functions library:
###############################
if(!$dontparse){
require "${root}data/config/pmpre.cfg";
require "${root}lib/mysql.lib";
require "${root}lib/pm.lib";
require "${root}lib/phpbb.lib" if($plugin_phpbb eq "yes");
}

###############################
# Read the data coming from the form..
###############################
if(!$dontparse){ &readit; }
###############################

if($use_cookie eq "yes" && !$dontparse && $input{'ID'}<1 && $input{'action'} ne "register"){
#- retrieve existing cookie
$lp = $req->cookie("pmpre");
my ($clogin, $cID) = split(/\|/, pm_decode($lp));
if(($clogin ne "") and ($cID ne "")){
if($input{'login'} eq ""){
$input{'login'} = $clogin;
}
if($input{'ID'} eq "" && $input{'password'} eq ""){
$input{'ID'} = $cID;
}
}

}


my $count = 0;
my $subject = "New posting in your Location";
my $flag = "1";
my $from = "admin";
my $time = &get_time;

$query = "SELECT MAX(mailno) FROM `mailbox`";
my $sth = $dbh->prepare($query);
$sth->execute();
my $data = $sth->fetchrow_array();
my $mailno = ($data + 1);


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures` WHERE `departures_states` = ? AND `departures_state_region` = ?" ) or die $DBI::errstr;
$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;
while(my $profiles = $profiles_sth->fetchrow_hashref()){
my $firstname = "$profiles->{'firstname'}";
my $login = "$profiles->{'login'}";
my $to = "$profiles->{'email'}";




my $sendmail = "/usr/sbin/sendmail -t";
my $from = 'register@domain.com';
my $subject = 'New posting in your location';
my $body = 'Hi, testing location email';

my $header = <<"HEADER";
To: $to
CC: $cc
Bcc: $bcc
From: $from
Subject: $subject
Content-type: text/html

HEADER


{

open(MAIL, "|$sendmail") || die
"Content-type: text/plain\n\nCan't open $sendmail: $!\n";
}

print(MAIL "$header$body");


close(MAIL);


$profiles_sth->finish;


print "<br>$count ";

if ($count == 1){
print "Member has";
} else {
print "members have";
}

print " been alerted about this post..<br><br>";
exit;}

FishMonger
12-11-2009, 07:47 PM
Are you sure that your select statement actually returned the email address(es)?

You should add a check to see that $profiles->{'email'} is defined and holds a properly formatted email address.

awayne96
12-11-2009, 08:46 PM
Are you sure that your select statement actually returned the email address(es)?

You should add a check to see that $profiles->{'email'} is defined and holds a properly formatted email address.

I know that there is my email address in there formatted me@mydomain.com.

We I run the script, it should be sending me an email since I am setup to be notified but nothing was sent.

I did look just to be sure that $profiles->{'email'} is the field and has my email address in it and it does....

FishMonger
12-11-2009, 09:41 PM
This needs more tweaking, but see what happens.

#!/usr/bin/perl

#use strict;
use warnings;
use CGI::Carp qw(fatalsToBrowser carpout);
use CGI qw(:standard);
use DBD::mysql;
use LWP::Simple;
use URI;
print "Content-type: text/html \n\n";

###############################
# find the root if this is IIS:
###############################
if($ENV{'SERVER_SOFTWARE'} =~ m/IIS/)
{
if($root eq "" && $ENV{'PATH_TRANSLATED'} ne "")
{
my $pt = $ENV{'PATH_TRANSLATED'};
my @pts = split(/\\/, $pt);
$pts[-1] = "";
$root = join "/", @pts;
$OS = "NT";
}
}

###############################
# functions library:
###############################
if(!$dontparse){
require "${root}data/config/pmpre.cfg";
require "${root}lib/mysql.lib";
require "${root}lib/pm.lib";
require "${root}lib/phpbb.lib" if($plugin_phpbb eq "yes");
}

###############################
# Read the data coming from the form..
###############################
if(!$dontparse){ &readit; }
###############################

if($use_cookie eq "yes" && !$dontparse && $input{'ID'}<1 && $input{'action'} ne "register"){
#- retrieve existing cookie
$lp = $req->cookie("pmpre");
my ($clogin, $cID) = split(/\|/, pm_decode($lp));
if(($clogin ne "") and ($cID ne "")){
if($input{'login'} eq ""){
$input{'login'} = $clogin;
}
if($input{'ID'} eq "" && $input{'password'} eq ""){
$input{'ID'} = $cID;
}
}

}


my $count;
#my $subject = "New posting in your Location";
my $flag = "1";
#my $from = "admin";
my $time = &get_time;
my $sendmail = "/usr/sbin/sendmail -t";

$query = "SELECT MAX(mailno) FROM `mailbox`";
my $sth = $dbh->prepare($query);
$sth->execute();
my ($data) = $sth->fetchrow_array();
my $mailno = ($data + 1);


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures`
WHERE `departures_states` = ?
AND `departures_state_region` = ?" )
or die $DBI::errstr;

$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $firstname = $profiles->{'firstname'};
my $login = $profiles->{'login'};
my $to = $profiles->{'email'} || die "no email retrived from db";

# if these are the same for each email,
# then they should be declared prior to the while loop
my $from = 'register@domain.com';
my $subject = 'New posting in your trucks location';
my $body = 'Hi, testing truck location email';

my $header = <<"HEADER";
To: $to
From: $from
Subject: $subject
Content-type: text/html

HEADER

print "attempting to sent email:<br>",
"$header$body<br>";

open(MAIL, "|$sendmail") or die "Can't pipe $sendmail: $!\n";
print(MAIL "$header$body");
close(MAIL);
}

$profiles_sth->finish;

my $plural = $count == 1 ? "Member has" : "Members have";
print "<br>$count $plural been alerted about this post..<br><br>";

exit;

FishMonger
12-11-2009, 09:45 PM
It's possible that the email was sent, but got blocked for some reason.

Personally I prefer to use one of Perl's mail modules rather than piping sendmail.

Take a look at MIME::Lite
http://search.cpan.org/~rjbs/MIME-Lite-3.027/lib/MIME/Lite.pm

awayne96
12-12-2009, 12:22 AM
Well, I created the cgi file with the above code and I got this error:

no email retrived from db at line 83.

So I was wondering if login and firstname would work so I put the error message next to both of them in the script and guess what, it doesn't pull the first name. So I deleted the error message from firstname and left it next to login.... Tried it again and it came up with only the error no email retrived from db at line 83.

So now the question, what would be causing this? I know there is a email in the field 'email'. the firstname isn't important at this time since I really want to get them email working first... But I would like to be able to use the firstname in the email body.

Could this possibly be linked to a .lib file or even the .cfg file?

Again, I really appreciate all of your assistance with this.

FishMonger
12-12-2009, 12:50 AM
Reduce your while loop to this and post its output.

use Data::Dumper;

while( my $profiles = $profiles_sth->fetchrow_hashref ){

print '<pre>' . Dumper($profiles) . '</pre>';

}

awayne96
12-12-2009, 01:33 AM
Here is the output:

$VAR1 = {
'departures_dispatch_phone' => '3332221111',
'departures_state_region' => 'Florida',
'departures_truck_name' => 'test',
'departures_number_spaces' => '2',
'departures_trailer_type' => 'Open',
'departures_states' => 'Ohio',
'added' => '2009-12-08 17:09:49',
'app_id' => '15',
'departures_city_area' => 'Cleveland',
'departures_inops_okay' => 'Yes',
'departures_departure_time' => 'Morning',
'departures_heading_direction' => 'South',
'departures_departure_date' => '12-12-2009',
'updated' => '2009-12-08 18:22:53'
};
Software error:
no email retrived from db at line 88

Now, the email is not in the departures table... only in the profile table

awayne96
12-12-2009, 01:46 AM
Do I need to add a hidden field to input the users email address to the departures table and into a field 'email'?

awayne96
12-12-2009, 01:55 AM
Ok, so I tested it by using another field to input the email address in the departures table.

Ran the script and it worked.

I am doing this from another computer right now so it still needs some tweaking which I will be working on in a little while once I get home.

I guess I should just remove that last bit of code you posted since that was just for testing purposes?

FishMonger
12-12-2009, 02:09 AM
If the email address you want is in the profile table of the db, then obviously you'll need to query that table instead of or in addition to the departures table.

I'm not sure why you think you need a hidden field

awayne96
12-12-2009, 02:17 AM
If the email address you want is in the profile table of the db, then obviously you'll need to query that table instead of or in addition to the departures table.

I'm not sure why you think you need a hidden field

Not every user gets notified when its posted.... Only the users that match with what the posting user has available. It is match state1 & state2 to the departures table.

I just figured it would be easier to have the hidden field like this:

<input type=hidden name=departures_field_name value="%%email%%">

My guess is I would need another type of statement to pair up the login name listed in the departures to query the profiles database to retrieve the email address to send the email.?.?.?

awayne96
12-12-2009, 07:21 AM
I started to build what I thought would work to query the profile database. I am getting no addresses found in header message with what I have:


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures`
WHERE `departures_states` = ?
AND `departures_state_region` = ?" )
or die $DBI::errstr;

$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $login = $profiles->{'login'};
if($input{'login'} eq "yes"){
$query = "SELECT 'email' FROM 'profiles'";
my $sth = $dbh->prepare($query);
$sth->execute();
my $firstname = $profiles->{'firstname'};
my $to = 'email';
}

oesxyl
12-12-2009, 08:28 AM
I started to build what I thought would work to query the profile database. I am getting no addresses found in header message with what I have:


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures`
WHERE `departures_states` = ?
AND `departures_state_region` = ?" )
or die $DBI::errstr;

$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $login = $profiles->{'login'};
if($input{'login'} eq "yes"){
$query = "SELECT 'email' FROM 'profiles'";
my $sth = $dbh->prepare($query);
$sth->execute();
my $firstname = $profiles->{'firstname'};
my $to = 'email';
}
second query must have a condition else you fetch all 'email' from 'profiles'.
I guess you don't need to query twice to do this.

best regards

awayne96
12-12-2009, 08:51 PM
Do I need to use a temp table to accomplish this? From what I have been reading I would. I have tried several different things and still continue to get no address found in header.

FishMonger
12-12-2009, 09:05 PM
oesxyl was saying that you need a where clause in this select statement.

$query = "SELECT 'email' FROM 'profiles'";

awayne96
12-12-2009, 09:38 PM
So I have:


$query = "SELECT email FROM profiles WHERE login = 'login'";

But I also tried this:


$email = ("SELECT 'email' FROM 'profiles' WHERE 'login' = 'login'");

neither one of these worked... Got errors on both. On the second one I got no address n header...

FishMonger
12-12-2009, 09:47 PM
Have you tried running your query on the command line? Does it return the email address? If it doesn't, then you need to verify that the database is setup the way you think.

awayne96
12-13-2009, 02:40 AM
Here are images of both tables

http://i879.photobucket.com/albums/ab355/wj2005/departurestable.jpg

http://i879.photobucket.com/albums/ab355/wj2005/profilestable.jpg

This is the code that I have tried and I continue to get no address found in header:


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures`
WHERE `departures_states` = ?
AND `departures_state_region` = ?" )
or die $DBI::errstr;

$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $login = $profiles->{'login'};
if($input{'login'} eq "yes"){
$query = ("SELECT 'email' FROM 'profiles' WHERE 'login' = 'email'" );
my $firstname = $profiles->{'firstname'};
my $to = 'email';
}


$query = ("SELECT 'email' FROM 'profiles' WHERE 'login' = 'login'" );

I have tried different combinations on the query to get the email from the profile and non have worked.

FishMonger
12-13-2009, 03:49 AM
The profiles table doesn't have an 'email' field and you never execute the $query statement handle and since you never executed and atempted to fetch the data, where do you expect the email address to come from? And doing:
my $to = 'email';doesn't make any sense.

If that's the actual code you're executing, then there's absolutely no reason to ever expect to be able to send the email based on that code.

awayne96
12-13-2009, 03:56 AM
Sorry about that, for some reason I uploaded the wrong profile table... Here is the correct one:

http://i879.photobucket.com/albums/ab355/wj2005/profilestable-1.jpg

FishMonger
12-13-2009, 03:59 AM
Ok, that takes care of the missing 'email' field. So where's your $query execute and fetch statements?

awayne96
12-13-2009, 04:15 AM
my $profiles_sth = $dbh->prepare("SELECT * FROM `departures`
WHERE `departures_states` = ?
AND `departures_state_region` = ?" )
or die $DBI::errstr;

$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $login = $profiles->{'login'};
if($input{'login'} eq "yes"){
$query = "SELECT 'email' FROM 'profiles' WHERE 'email' = 'login'";
my $sth = $dbh->prepare($query);
$sth->execute();
my ($data) = $sth->fetchrow_hashref();
my $email = ($data);
my $firstname = $profiles->{'firstname'};
my $to = 'email';
}

FishMonger
12-13-2009, 04:41 AM
Why are you naming your statement handle
$profiles_sthwhen you're querying the `departures` table?

Why are you doing this?

my $email = ($data);

And what do you expect this to achieve?

my $to = 'email';

awayne96
12-13-2009, 04:49 AM
Why are you naming your statement handle
$profiles_sthwhen you're querying the `departures` table?

That was designed by someone else like that.... That is why I didn't realize there wasn't an email field in the departures table.... I guess I could change just change it from profiles_sth to departures_sth since that is what its querying.


Why are you doing this?

my $email = ($data);

I was looking though some code an was copying from another area of the site... I'm guessing I don't need it then since your asking me this?


And what do you expect this to achieve?

my $to = 'email';

I thought that was needed to refer to what $to should equal

awayne96
12-13-2009, 04:51 AM
Why are you naming your statement handle
$profiles_sthwhen you're querying the `departures` table?

I wish this would not have been like this because it would have saved me and you (since you were helping me) alot of headaches.

oesxyl
12-13-2009, 12:57 PM
try to run this query:



select d.*, p.* from departures as d, profiles as p
where
d.departures_state = 'state1' and d.departures_state_region = 'state2' and
p.login = d.login and
p.email = 'login'

replace state1, state2 and login with some valid values.
I try to explain a little:
- first line after where clause will select the departures_state and departures_region rows from departures table
- second line will link rows from profiles with rows from departures tables
- last line will filter the results using login value.
I don't know if this will work or not, that depend of the meaning of the columns in both tables and what values are inside.

best regards

awayne96
12-14-2009, 04:42 AM
try to run this query:



select d.*, p.* from departures as d, profiles as p
where
d.departures_state = 'state1' and d.departures_state_region = 'state2' and
p.login = d.login and
p.email = 'login'

replace state1, state2 and login with some valid values.
I try to explain a little:
- first line after where clause will select the departures_state and departures_region rows from departures table
- second line will link rows from profiles with rows from departures tables
- last line will filter the results using login value.
I don't know if this will work or not, that depend of the meaning of the columns in both tables and what values are inside.

best regards

Thanks for the reply! I am running the following code now:


my $profiles_sth = $dbh->prepare("SELECT * FROM `departures`
WHERE `departures_states` = ?
AND `departures_state_region` = ?" )
or die $DBI::errstr;

$profiles_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $login = $profiles->{'login'};
}
("select d.*, p.* from departures as d, profiles as p
where
d.departures_states = 'state1' and d.departures_state_region = 'state2' and
p.login = d.login and
p.email = p.login");

my $to = 'email';

# if these are the same for each email,
# then they should be declared prior to the while loop
my $from = 'register@domain.com';
my $subject = 'New posting in your trucks location';
my $body = 'Hi, testing truck location email';

my $header = <<"HEADER";
To: $to
From: $from
Subject: $subject
Content-type: text/html

HEADER

print "attempting to sent email:<br>",
"$header$body<br>";

Getting the following error: "To: email From: register@domain.com Subject: New posting in your trucks location Content-type: text/html Hi, testing truck location email
/home/content/m/o/t/domain/dead.letter... Saved message in /home/content/m/o/t/domain/dead.letter"

I have tried a few other ways and got no address in header error aswell.

Also, the values for login in both tables are the same...

FishMonger
12-14-2009, 05:27 PM
Either the code you posted is not the exact same code you tested or the "error message" you posted is not exactly what you received.

Your code indicates that $to is set the 'email', which obviously won't work, but your "error message" indicates that the $to var is undefined. Which is it?

Also, due to scoping this isn't going to do anything useful

while( my $profiles = $profiles_sth->fetchrow_hashref ){
my $login = $profiles->{'login'};
}$login is now private to the while loop and won't be seen outside of that block.

My assumption is that your $to assignment is really this:

my $to = $login;If that's the case, then that would explain, due to the scoping issue I just mentioned, why your $to var is undefined.

awayne96
12-15-2009, 01:26 AM
What I had posted above is what I used... that is the error I got when I tried it.

I tried many different combinations to try to get it to work and no go with all of them.... Some would give me dead letter, other no address in header error.

I really don't know where to go next with this.?.?.?

I would think it would be just as easy as if login in departures = login in profiles and login in profiles = email in profiles, fetch email from profiles that = login in departures.... something along those lines.

FishMonger
12-15-2009, 01:58 AM
What's the output of this section of your code?

my $to = 'email';

# if these are the same for each email,
# then they should be declared prior to the while loop
my $from = 'register@domain.com';
my $subject = 'New posting in your trucks location';
my $body = 'Hi, testing truck location email';

my $header = <<"HEADER";
To: $to
From: $from
Subject: $subject
Content-type: text/html

HEADER

print "attempting to sent email:<br>",
"$header$body<br>";

If it's anything other than the following, then the code you posted is not what you're testing.

attempting to sent email:
To: email From: register@domain.com Subject: New posting in your trucks location Content-type: text/html Hi, testing truck location email

If that is the output you receive, then that would explain why the email is failing. 'emal' is not a valid email address.


Some would give me dead letter, other no address in header error.I have no clue what that is supposed to mean.

FishMonger
12-15-2009, 02:26 AM
I would think it would be just as easy as if login in departures = login in profiles and login in profiles = email in profiles, fetch email from profiles that = login in departures.... something along those lines.

I don't know what you actually have in your DB, so it's hard to give recommendations in this area.

awayne96
12-15-2009, 07:46 AM
I don't know what you actually have in your DB, so it's hard to give recommendations in this area.

Login in both the departures and profiles are the same. If a user has a departures listed table, it has there login in the departures login field which matches the login in the profiles table.

So something like this:


$query = "SELECT 'login' FROM 'departures' WHERE 'departures_states' = ? AND 'departures_state_region' = ?";

$departures_sth->execute($input{'state1'}, $input{'state2'}) or die $DBI::errstr;

my $departures_sth = $dbh->prepare($query);
$departures_sth->execute();
while( my $departures = $departures_sth->fetchrow-hashref ){
my $login = $departures->{'login'};
}

$query = "SELECT 'email' FROM 'profiles' if $input{'login'} = 'login'";

I don't know if I am headed in the right direction and even if I am I know there is some parts missing from this....

I am thinking that I must first get the login for the users that are currently in the departures table in the locations state1 & state2. Once you have that, query the db to match that login name in the profiles table and retrieve the email for that user(s) and send the email.

awayne96
12-18-2009, 05:37 PM
Well after all of the problems I have had I ended up putting an actual email field on the form related to the departures that writes to that table.

This will also allow the user to have notifications emailed to whatever address they want and not just the one in there profile.

My only other things are the following:

How do you code mutiple lines in the $body of the code?

And how can a get the $input{'state1'} and whatever other data I would like to input into the body of the email that is coming from the form the user is posting? I want to be able to have city state and zip and also maybe contact info put into the body of the message.

Thanks again for the help.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum