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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
