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

Question

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..

You shouldn't do a sql query inside a loop; why not just do one query between startdateforarray and end_date? — Digital Chris
– Digital Chris, Commented Aug 26, 2014 at 16:26
See this answer stackoverflow.com/a/6024944 you can base yourself on it. — Funk Forty Niner
– Funk Forty Niner, Commented Aug 26, 2014 at 16:29
I´m aware that this is maybe not the most efficient way of doing things. See this post I made: stackoverflow.com/questions/25512088/… — GeniusDesign
– GeniusDesign, Commented Aug 26, 2014 at 17:48

Hanky Panky · Accepted Answer · 2014-08-26 16:21:37Z

1

Assuming by timestamp you mean Unix Timestamp, you can do

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

answered Aug 26, 2014 at 16:21

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related