CODEWITHSUNDEEP
phpmysql
GeniusDesign
GeniusDesign

Reputation: 499

PHP - MySQL query - counting results where timestamp is within specific date ("Y-m-d")

This is pretty basic MySQL, but I have not been able to figure this one out, how to do it correctly..

Example: I have a DB table named "table1" with a list of records of user visitors data. Columns: "ID", "TM" and "IP"

"TM" contains timestamp for when the record is stored.

I have a PHP code where I loop through days from a start date to current day. Like this example:

// Start date
$startdateforarray = '2010-07-21';
// End date
$end_date = date("Y-m-d");

    while (strtotime($startdateforarray) <= strtotime($end_date)) {
        $timestamp = strtotime($startdateforarray);

    //Here I want to run my MySQL Query...

        $startdateforarray = date ("Y-m-d", strtotime("+1 day", strtotime($startdateforarray)));

}

Now, inside the loop I want to make a query to count how many results there are in "table1" for each day.

So the MySQL query should be something like:

"SELECT * FROM table1 WHERE TM = (day of $timestamp)"

Of course (day of $timestamp) is where I have a problem. I know that this should be pretty simple to do, but I havent found a solution yet..

Upvotes: 0

Views: 168

Answers (1)

Hanky Panky
Hanky Panky

Reputation: 46900

Assuming by timestamp you mean Unix Timestamp, you can do

SELECT * FROM table1 WHERE FROM_UNIXTIME(TM,'%Y-%m-%d') =  '2010-07-21'

Upvotes: 1

Related Questions