Calculating Difference between Two Dates?

[jrotunda85]

I am trying to write a script for my site that will check if a user has submitted a review for a particular cigar in the past 30 days and if they have, prevent them from creating a new review. I am trying to figure out the code but I'm getting stuck on how to calculate the 30 days from today's date? Any help?

Here's what I have now -

<?PHP

$SQL = "SELECT max(DateAdded) from reviews_cigar WHERE $UserID = UserID AND $CigarID = CigarID";
$Result = mysql_query($SQL)

If ($Result < ?????) {
	echo "Sorry but you've submitted a review for this cigar in the past 30 days"
	}

?>

[mschroeder]

Assuming DateAdded is a Date or DateTime column in mysql.

SELECT COUNT(CigarID) AS ReviewCount FROM reviews_cigar WHERE $UserID = UserID AND $CigarID = CigarID AND WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DateAdded

This should find any rows that have been added within the last 30 days of the DateAdded column.
**You may not want to use the COUNT() like I did because it will always return 1 row even when the count is 0.**

[jrotunda85]

I think it will work as you have it because the query is still returning a value (0 in the case of no reviews). I can then use that value in the IF Statement, something like

If ($Result > 0) {echo "Sorry but you've submitted a review for this cigar in the past 30 days";}

Thanks for the help! :)

[jrotunda85]

Hrrm, the code I just posted is not working, it's still letting me submit reviews. Here's the revised code:

//Check User's Last Review
$SQL = "SELECT COUNT(CigarID) FROM reviews_cigar WHERE UserID = $UserID AND CigarID = $CigarID AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DateAdded";
$Result = mysql_query($SQL);

if ($Result != 0) 
	{
	die('Sorry but you have submitted a review for this cigar in the past 30 days');
	}

[mschroeder]

//Check User's Last Review
$SQL = "SELECT COUNT(CigarID) AS ReviewCount FROM reviews_cigar WHERE UserID = $UserID AND CigarID = $CigarID AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DateAdded";
$Result = mysql_query($SQL);

//Error out if the query fails for some reason
if( !$Result ){
 die( mysql_error() );
}

//Query should ALWAYS return 1 row.
//Reviews in last 30 days : ReviewCount = 1+
//No Reviews in last 30 days : ReviewCount = 0
$row = mysql_fetch_array( $Result );
if( $row['ReviewCount'] > 0 ){
  die('Sorry but you have submitted a review for this cigar in the past 30 days');
}

You could also drop the count() portion of the query and return a field for every review the user has created in the last 30 days.
Then implement similar functionality using if( mysql_num_rows( $Result ) > 0 ){ die(); }

[jrotunda85]

Quick question, if I wanted to change this to say 15 days, would I just need to change this part??

DATE_SUB(CURDATE(),INTERVAL [B]30[/B] DAY)

[mschroeder]

Thats it.