Mysql query help... :( its killing me. Ive gone grey! lol

[lynxus]

Hi Guys,

 

Ive got a DB with website visitor details in like below:

 

Table = visitors

 

for this example, lets say it has the colums, ID , DATE

 

1 2010-07-02 00:08:03

2 2010-07-02 00:08:03

3 2010-07-03 00:09:03

4 2010-07-04 00:02:03

5 2010-07-04 00:04:03

 

 

Etc..

 

Now, I want to find HOW many rows for each day for the last 30 days..

So i can output..

Day1 - 30 ( 30 rows match for this day, Thus 30 visitors..  )

Day2 - 20

Day3 - 44

Etc..

 

Does this make sense?

 

This way i can then chart how many visitors have visited in the last 30 days..

 

Does this make sense?

 

Any ideas what syntax to use to do this query?

I suppose i will want to While through it 30 times to retrive the value so i can build the string with values .. ( or an array with 30 values? )

 

Thanks

G

 

[Pikachu2000]

Not tested, but I think this is what you're after. Should return the visitor count for each of the last 30 days.

 

SELECT COUNT(`ID`) AS num, `DATE` FROM `visitors` GROUP BY DATE_FORMAT(`DATE`, '%Y/%m/%d') ORDER BY `DATE` DESC LIMIT 30

[lynxus]

You Sir,

 

Are awesome!

Thanks very much. Seems to work perfectly.

Been working with mysql for years and can never get to grip with dates.

 

TYVM!

 

+-------+---------------------+

| num  | time                |

+-------+---------------------+

|  2523 | 2010-09-16 01:16:44 |

|  5099 | 2010-09-15 01:29:49 |

|  4450 | 2010-09-14 00:59:52 |

|  4520 | 2010-09-13 01:09:22 |

|  3821 | 2010-09-12 00:35:59 |

|  3678 | 2010-09-11 00:38:43 |

|  4113 | 2010-09-10 00:21:41 |

|  8967 | 2010-09-09 00:54:38 |

|  5675 | 2010-09-08 00:26:58 |

|  6443 | 2010-09-07 00:11:01 |

|  6438 | 2010-09-06 00:32:14 |

|  6987 | 2010-09-05 00:22:10 |

|  6520 | 2010-09-04 00:13:09 |

|  6183 | 2010-09-03 00:24:40 |

|  5873 | 2010-09-02 00:36:49 |

|  4198 | 2010-09-01 16:49:55 |

|  7666 | 2010-08-31 00:24:56 |

|  8065 | 2010-08-30 00:12:09 |

|  6659 | 2010-08-29 02:42:18 |

|  6990 | 2010-08-28 00:32:07 |

|  7500 | 2010-08-27 01:22:08 |

|  7835 | 2010-08-26 01:53:50 |

|  9492 | 2010-08-25 01:35:59 |

|  8840 | 2010-08-24 01:41:53 |

|  8897 | 2010-08-23 01:20:19 |

| 10663 | 2010-08-22 01:19:16 |

|  7469 | 2010-08-21 18:53:21 |

|  8764 | 2010-08-20 02:49:38 |

|  8076 | 2010-08-19 01:56:28 |

| 10755 | 2010-08-18 04:42:17 |

+-------+---------------------+

30 rows in set (0.48 sec)