List:General Discussion« Previous MessageNext Message »
From:Richard Date:December 15 2003 1:54am
Subject:Getting Records where date is LESS THAN today, AND...
View as plain text  
I am Creating a newsletter site for my employer, which will allow him to just add the
newsletter and choose which day to mail it. a cron job runs everynight and mails out a
notice that the newsletter is ready, along with the link.

I am having a problem getting the date to match up...

I have these 'columns'...

sendoutmon is the MONTH that it will or did get sent out. For example:  12
sendoutday is the DAY of the month it will or did get sent out. For example: 14
sendoutyear is the YEAR that it will or did get sent out. For example 2003
sent Which is an enum, of either 0 or 1. 0 being NOT sent yet. 1 being already sent
archive Which is telling us if we want it added to the archive or not. so it's an enum
like sent

So I'm trying to Call the records into my Perl Application, which does it like this...

my $dbh = Sess::Eco::connect();
my $sth = $dbh->prepare (qq{ SELECT * FROM newsletters WHERE archive  = 1 AND sent = ?
AND sendoutmon <= $_current_month AND sendoutday <= $_current_day AND sendoutyear
<= $_current_year });
$sth->execute("1");
my $_actual_rows = $dbh->selectrow_array(qq{SELECT COUNT(*) FROM newsletters WHERE
archive  = 1 AND sent = 1 AND sendoutmon <= $_current_month AND sendoutday <=
$_current_day AND sendoutyear <= $_current_year});
while ($row = $sth->fetchrow_hashref()) {
#Print my table records...
}
$sth->finish();

That is not working like it should... I have put 3 testing newsletters into the database.
1 with these values: (among other irrelevant ones)

sendoutmon    sendoutday     sendoutyear  archive  sent
11                   15                  2003             1           1
12                   13                  2003             1           1
12                   15                  2003             1           0

So with THOSE 3, test newsletters, 2 of them SHOULD be displayed. But ONLY the middle one
is printed into my table.

I think I'm doing it the totally wrong way.
For instance, if one of the newsletters was last year, then it would not be correct, such
as this...
If today was January 1, 2004 then none of those would be displayed, because the month and
the Day would not be
less then the sendoutmon or sendoutday.

So, how can I do this?
In the "Admin" menu I created for him, when he chooses when to send it, then it uses a
calander where he can
choose a date, which is formated like this: mm/dd/yyyy So I have it check to make sure
it's in the future, but
not to distant future.

Is there a BETTER way to do this? I know there is, but can someone give me an example?
I want to do it right now, instead of finish this project, then have to go re-code half
the scripts/databases.

Thank you in advance for any tips/advice you have.
Richard
FRHweb

Thread
Getting Records where date is LESS THAN today, AND...Richard15 Dec
  • Re: Getting Records where date is LESS THAN today, AND...Richard15 Dec
  • Re: Getting Records where date is LESS THAN today, AND...Martijn Tonies15 Dec
  • Re: Getting Records where date is LESS THAN today, AND...Richard16 Dec
  • Re: Getting Records where date is LESS THAN today, AND...Martijn Tonies16 Dec