Help needed with a simple string

[Facte]

The string below runs as a maintenance script on one of my sites and at the moment it emails anyone who's plan has expired (which is fine) but it also emails people who haven't selected a plan yet because the value is less than today. I was wondering if someone could help tweak the code so it only emails the people who's plans have actually expired only???

Here's the script.

<?include_once "index.php";
$EXday = date('d', mktime(0,0,0,0, date(d) + 0, 0));
$EXmonth = date('m', mktime(0,0,0, date(m), date(d) + 0, 0));
$EXyear = date('Y', mktime(0,0,0,date(m) ,date(d) + 0, date(Y)));

$todays_date = "$EXmonth/$EXday/$EXyear";
$today = strtotime($todays_date);

$aq = mysql_query("SELECT * FROM jobs order by jobid asc");
while($bq = mysql_fetch_array( $aq )) 

$az = mysql_query("SELECT * FROM employers order by employerid asc");
while($bz = mysql_fetch_array( $az )) 
{
$expiry3 = strtotime($bz[expiry]);

if ($expiry3 < $today)
{
$message = file_get_contents($basedir . $slash . "../lang/$a18[lang]/emails/employers/plan_expired.txt");
$subject = "Your employer plan at $a18[sitename] has expired";

$message = str_replace("[[[sitename]]]", $a18[sitename], $message);
$message = str_replace("[[[fullurl]]]", $fullurl, $message);
$message = str_replace("[[[contactname]]]", $bz[contactname], $message);
$message = str_replace("[[[plan]]]", $bz[plan], $message);
$message = str_replace("[[[EXmonth]]]", $EXmonth, $message);
$message = str_replace("[[[EXday]]]", $EXday, $message);
$message = str_replace("[[[EXyear]]]", $EXyear, $message);
$message = str_replace("[[[footer]]]", $a18[footer], $message);
$message = str_replace("[[[contactemail]]]", $a18[mailfromemail], $message);

$from = "From: $a18[mailfromemail]";

mail ($bz[ename], $subject, $message, $from);

$fis = "update employers set
	  plan = \"\",
	  expiry = \"\",
	  resume = \"No\",
	  resumenum = \"\",
	  posting = \"No\",
	  featured = \"No\",
	  homepage = \"No\",
	  posts = \"\",
	  paid = \"0\"
        where employerid = \"$bz[employerid]\"";
$rfis = mysql_query($fis) or die(mysql_error());
}
}

[diafol]

First of all you seem to be doing a lot of needless date calculation.

You need to check your DB for the existence of a plan (i.e. where the date field is not null) and use this subset in your query to check if the expiry date is less than today.

Depending on your MySQL version, you could use DATE() or DATE_FORMAT to check this.

Your dates in MySQL DB should be in unix format (Y-m-d), I'd suggest changing them it it's not.

"SELECT * FROM table WHERE datefield IS NOT NULL AND datefield < DATE(now())"

If DATE(now()) is not supported, use DATE_FORMAT(now(),'%Y-%m-%d')

[Facte]

I was wondering if anything could be done with the "if ($expiry3 < $today)" part of the code because the script wasn't written by me and the above is only part of 1 page. Thanks for your help.

[diafol]

Ahem, I've just sorted out your spaghetti code by giving you a bit of sql. If you want to stick with:

$EXday = date('d', mktime(0,0,0,0, date(d) + 0, 0));
$EXmonth = date('m', mktime(0,0,0, date(m), date(d) + 0, 0));
$EXyear = date('Y', mktime(0,0,0,date(m) ,date(d) + 0, date(Y)));

$todays_date = "$EXmonth/$EXday/$EXyear";
$today = strtotime($todays_date);

$aq = mysql_query("SELECT * FROM jobs order by jobid asc");
while($bq = mysql_fetch_array( $aq )) 

$az = mysql_query("SELECT * FROM employers order by employerid asc");
while($bz = mysql_fetch_array( $az )) 
{
$expiry3 = strtotime($bz[expiry]);

if ($expiry3 < $today)

good luck to you!

[Facte]

I wasn't being rude, I'm just not a good enough coder to apply what you told me, so was wondering if there is something simple (to me) that I can do to fix it. Apologies for any misunderstanding.

[diafol]

No offence taken. See my PM.

[=OTS=G-Man]

yes pleases get rid of that overly complicated date assignment

$today = date("m/d/Y");

just the thought of the wasted bits of memory is killing me