Need to calculate number of days between two dates... how?

[simcoweb]

I have people posting ads and when the ad is posted I have two fields populated at that time:

 

date_created

date_expired

 

With the date expired being 7 days after the date created. Both are in this format: 03/12/07

 

What I am doing is preventing someone from posting more than one ad per 7 days. I'm validating against their email address for that. But I want to display the date that they would be eligible as well as determine if they are eligible to post or not which would require comparing today's date with probably using the now() function against the date_expired in the database. If the today's date is past then no problem. If not, then it would display an error. Here's what I have on this right now:

 

<?php
// duplicate email check
function emailCheck()
{
  $email = $_POST['email'];
  $sql = "SELECT * FROM users WHERE email='$email'";
  $results = mysql_query($sql) or die(mysql_error());
  $newad_date = $results['date_created'];
  $num_rows = mysql_query($results);
  if ($num_rows >= 0){
    echo "<h3>Duplicate Ad Error</h3><p>You are allowed one ad per every 7 days. You will be eligible to sumit a new ad on $newad_date.";
}
}
?>

[Greaser9780]

If the value is kept as a timestamp in the db then pull both out with a query. Convert them with strtotime(). Then do the math. $timeleft = ($exptime-$timenow);

$time = ($timeleft / 86400);

echo "You have $time day(s) left";

[genericnumber1]

Of course you might want to round(), ceil(), or floor() off any remaining decimals.

[Greaser9780]

I forgot the floor() part ty for adding it.

[genericnumber1]

Thank you for doing the bulk of the answer

[paul2463]

of course you could use a MySql built in function called DATEDIFF()

 

$query = "SELECT DATEDIFF(date_created, date_expired) AS difference from table WHERE id = '$id'";
$result = mysql_query($query) or die ("Query was rubbish" . mysql_error());
while($row = mysql_fetch_assoc($result))
{
    if ((difference > 7)||(difference < -7)) // you may have fed the numbers in the wrong way round
    { // post away !!!
    }
    else
    { //no more posts for you old chap
    }
}

[Greaser9780]

Good to know. Didn't know mysql had a built in function for it.

[simcoweb]

Thanks to each and every one of you for the responses!

 

I like the idea of the DATEDIFF function simply because I didn't insert the dates as timestamps and i'd have to convert them to timestamps then back again, etc. if I did it another way. I'll plug in that code and read up on the DATEDIFF function.

[skali]

You can use this code to find difference b/w two dates.

$from = GregorianToJD(03, 13, 1970);
$to = GregorianToJD(03, 15, 1970);
$diff = ($to-$from)+1

[simcoweb]

skali, that's good to know. I'll save that as a snippet and use it as it looks quite compact.

[obsidian]

Thanks to each and every one of you for the responses!

 

I like the idea of the DATEDIFF function simply because I didn't insert the dates as timestamps and i'd have to convert them to timestamps then back again, etc. if I did it another way. I'll plug in that code and read up on the DATEDIFF function.

 

Another way to allow MySQL to do the work for you is simply to query based on your day limit. Then, you throw it into your system the way you have it, and you should be good to go: if there is no record returned, let them post, otherwise, they're not eligible yet.

SELECT * FROM users
WHERE email = '$email' 
AND DATE(date_created) >= CURDATE() - INTERVAL 7 DAY;

 

This returns records only if there is one that has been posted in the last 7 days matching the email address provided. Then, you handle the results just as you are in the OP.

 

Good luck.

[simcoweb]

Wow..3 great ways to get 'er done! Thanks! I'm snagging all these as 'snippets' in my code library.

[mbtaylor]

Wow theres a DATEDIFF function?

 

* remembers to read up on all MySQL functions...

 

I always did it the strtotime() way

[obsidian]

Wow theres a DATEDIFF function?

 

* remembers to read up on all MySQL functions...

 

I always did it the strtotime() way

 

Best link in the entire MySQL manual:

Date and Time Functions