[RESOLVED] get count of entries between dates

I'm not sure if this is a PHP or SQL question, as they sort of go hand-in-hand here.

I've got a mysql table named closedtickets with many entries in it. One of the columns is called "closed" and contains the unix timestamp (reported by PHP's time() function at the time of creation)

I'm trying to create some script that returns something like "15 records closed in the past 10 days."

I guess I need something to the effect of

Code:

---

$backthen = mktime(0,0,0,date("m"),date("d")-10,date("Y"));

---

and then an SQL query of something like

Code:

---

SELECT COUNT(closed) FROM closedtickets WHERE closed>$backthen

---

I'm not really familiar with the syntaxes of either language and wondering if someone could help me out with this.

I'd use MySQL's date functions without involving PHP:

Code:

---

SELECT COUNT(closed) FROM closedtickets WHERE closed>DATE_SUB(closed,INTERVAL 10 DAY)

---

because he's using UNIX timestamps, you'll need to use FROM_UNIXTIME() to convert closed to a MySQL date format. also, you should be checking against the current date, because closed will never be above closed minus 10 days.

Code:

---

SELECT * FROM table WHERE FROM_UNIXTIME(closed) > DATE_SUB(NOW(), INTERVAL 10 DAY)

---

Quote:

---

closed will never be above closed minus 10 days

---

Where's the button to subtract from my rep?

Awesome, kows!

My final code:

Code:

---

        $q = 'SELECT * FROM closedtickets WHERE FROM_UNIXTIME(closed) > DATE_SUB(NOW(), INTERVAL 15 DAY)';
        $r = mysql_query($q);
        $num_rows = mysql_num_rows($r);
        echo "$num_rows tickets closed in the past 15 days.";

---

SQL surprises me every day :)