recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year

within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is

mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

the complete feild is another contained within the datebase. i have also tried

SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted

9 Years
Discussion Span
Last Post by twodayrule

It seems that you are selecting on the wrong check. You are saying that the function datediff should be less or equal to 7. This is not entirely correct. Perhaps you could try testing on the complete datediff result.


this is my own solution: try it. works for me
select * from mytable where
mydate BETWEEN date_format(Now() - INTERVAL 1 DAY,'%e/%c/%Y - %H:%i:%s') AND date_format(Now(),'%e/%c/%Y - %H:%i:%s') ";

this one sorts for 1 day.
if you need week, month, 45, 52 or 665 days :)

change this
Interval 1 to Interval 45

number is important....

to add dates change minus sign before interval 1
and 1 day would be added automatically. :)


thank you for the help i have it working and made a nice start to monday morning at work getting it working :)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.