Need Help: Checking date field to see if an item is close to needing calibration

[zoominomad]

I'm trying to teach myself SQL/PHP by building a database driven local site for keeping track of my calibrated tools (lot of them).

 

Basically my table looks like this:

 

Table: calibrated_items

Fields: serial_number, part_number, location, nomenclature, calib_freq, date_calib

 

These tools have different intervals at which they need to be calibrated (180 days, 360 days, etc). I'm trying to figure out how I can query my list of tools and show how many days they have until due calibration. The date_calib field is a date field for when the tool was last calibrated (ie; 2013-07-24). calib_freq is the interval frequency for calibration (ie; 180 days).

 

I'm currently querying the database like this:

  $callist = mysqli_query($con, "SELECT * FROM calibrated_items WHERE date_calib <= (NOW() - INTERVAL 180 DAY)");

I know this a jacked up way to try and accomplish what I'm aiming for. I just don't know enough to figure out how to use NOW() date_calib and and calib_freq to output a number of days until due field. I don't really need to save that field, I just want to output it to the webpage.

 

I hope I included enough information for this to make sense. If not, let me know. Thanks!

 

 

[Psycho]

SELECT *,
       DATEDIFF(DATE_ADD(date_calib, INTERVAL calib_freq day), NOW()) AS days_till_calib
FROM calibrated_items

This will give you ALL the records fom the database along with the number of days until calibration is needed. A negative number will mean it has past the date

 

EDIT: To explain this. We first take the last calibration date and the calibration frequency and use DATE_ADD() to calculate the next date that calibration is due. Then we take that date and NOW() and use the DATEDIFF() function to calculate the number of days between now and the next calibration date.

 

EDIT #2: I just realized there is a simpler solution. Calculate the number of days that have passed since the last calibration (using DATEDIFF() ), then just subtract that from the frequency.

 

SELECT *,
       (calib_freq - DATEDIFF(NOW(), date_calib)) AS days_till_calib
FROM calibrated_items

Edited July 25, 2013 by Psycho

[zoominomad]

Awesome! Will I need to create a field in the table for days_till_calib? I also really appreciate the explanations for each example! It amazes me how efficient SQL is in the hands of someone who like you who knows how to use it!

[Psycho]

Will I need to create a field in the table for days_till_calib?

 

No, it is a dynamic value that is auto-generated when you run the query.

[zoominomad]

Ok great! I really appreciate it!