[RESOLVED] PHP MySQL Calculating the time remaining on a customer's warranty

[shaneshack]

Everything in my project works perfectly except this. My database table (customerpurchases) has two particular fields: invoicedate and warranty. The invoicedate (type is DATE) column is the date of purchase, the warranty (type is INT) column is the length of the warranty in months.

I'm trying to do two things:

1. Take the value from warranty and add it to invoicedate. This will get me the warranty deadline. (THIS IS WHERE I AM CURRENTLY STUCK)

2. Subtract the current date from the warranty deadline to obtain the number of months remaining in the warranty.

I've seen dozens of samples that involve using DATE_ADD. I can make it work when I statically supply a date, expr and unit (ex. ('2007-11-30', INTERVAL 12 MONTH)), but when I replace '2007-11-30' with 'invoicedate' and 12 with 'warranty' it will not work. Any help provided will be deeply appreciated.

Here's a sample of what I've tried (among several variations):

$sql = "SELECT * FROM customerpurchases DATE_ADD('invoicedate', INTERVAL 'warranty' MONTH) WHERE serial = '$searchserial'";
$result_sql = mysql_query($sql) or die(mysql_error());
echo $result_sql;

[bradgrafelman]

Well, your first problem is that you use single quotes which delimit strings, so MySQL is trying to add the interval 'warranty' to the string 'invoicedate'. Obviously, this causes problems. Secondly, you can't just throw in the DATE_ADD() stuff wherever you want... if you're trying to SELECT that as data, then it should be in the column list.

Try doing this:

SELECT warranty, DATEDIFF(CURDATE(), invoicedate) AS months
FROM customerpurchases
WHERE serial = '$searchserial'
LIMIT 1

Then, you can do some simple PHP logic:

if($result['warranty'] < $result['months']) {
    echo "Oh no! You are out-of-warranty!";
}

[shaneshack]

This did not work, although it maybe a lack of understanding on my part. I don't understand the logic of your MySQL statements. I probably need a more detailed/explained answer. I also need the results to display the number of months remaining in the warranty, not just a warning that the warranty has expired. Thank you, though, for responding so quickly.

[shaneshack]

Nevermind, I understand your answer now. Just a little groggy this morning. Thank you for your help.