List:General Discussion« Previous MessageNext Message »
From:Richard Date:December 15 2003 4:13am
Subject:Re: Getting Records where date is LESS THAN today, AND...
View as plain text  
Ok, I got it... I THINK :o)

$sth = $dbh->prepare (qq{ SELECT * FROM newsletters WHERE sent = "1" AND
archive = "1" AND ((sendoutmon <= $_current_month AND sendoutday <=
$_current_day AND sendoutyear <= $_current_year) OR (sendoutyear <=
$_current_year)) });


Just checking a second time for anything where the year is less then the
current will cover previous year, regardless of the month, then everything
within this year is covered by the first check.

So far it's working. I'm still open to better ways though ;o)

Thanks,
Richard


----- Original Message ----- 
From: "Richard" <mysql@stripped>
To: <mysql@stripped>
Sent: Sunday, December 14, 2003 7:54 PM
Subject: Getting Records where date is LESS THAN today, AND...


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