Where clause using MySQL Date value?

BLottman

Hello:

I've been beating my head against a wall trying to figure this out. I've looked in the MySQL manual and keep finding brick walls instead of paper walls. I need some help to bust through.

I'm trying to write a Where expression so that I can compare a rows DATEEXPIRES date column with the CURDATE() or a string similiar to the current date and neither seems to work.

Here's my code:

<?php
$date_month = date(m);
$date_year = date(Y);
$date_day = date(d);
$time_hour = date(H);
$time_min = date(i);
$Datesession = "$date_year$date_month$date_day";
$jddatefromcurdate = gregoriantojd($date_month, $date_day, $date_year);
$dateexpires = jdtogregorian($jddatefromcurdate);
$dateexpires = strtotime($dateexpires);
$dateyear = date("Y",$dateexpires);
$datemonth = date("m",$dateexpires);
$dateday = date("d",$dateexpires);

$Inactivedate ='$dateyear-$datemonth-$dateday';

include("settings.inc");
mysql_select_db("$mydb", $connection);

$sql = "UPDATE properties SET STATUS='InActive',
DISABLEDDATE='$Inactivedate'
Where DATEEXPIRES < $Inactivedate;

$result = @($sql) or die("Error Deleting Record");
?>

Thanks in advance.

😕

tha_mink

Did you design the db? Why are you doing all the date manipulation w/php? Does the expire date change or is it static...like 2 weeks after it was created. Mysql has TONS of date functions that work great for exactly what you are trying to do. Is it too late to redesign the db? [this is where i should say..."you should have thought this out better during the design phase" 🙂]

More info please.

tha_mink

bretticus

ditto ;-)

Also, I might add:

This may not fit your database but this is as simple as it can or (should) be:

// no php date stuff.
$sql = "UPDATE properties SET STATUS='InActive',
DISABLEDDATE=NOW() Where DATEEXPIRES < NOW()";

BLottman

Bretticus:

What sets you apart from the some of coders on this site is your capacity to inspire rather than to concentrate solely on condemning another's code.

The suggested code worked as noted. Although I wasn't looking for a coding example it helped.

Sometimes it's much easier to provide a code example than to explain in depth various nuances of the code or to provide a link to the reference page in the MySql manual. I need to do a better job of searching the manual. Rather than just hitting the Search documentation button, I should have searched through the contents of the manual for functions then date functions.

You're right, MySQL is much better than PHP when it comes to handling MySQL date functions!

Thanks again.

Bill.