If you're concerned about speed and redundancy, can you count registrations for a day, save that data in a separate table and only count data for the most recent day? Registration data for past dates will never change so why recount it every time? Just save it in another table. Count the most recent registration day, save it in that other table, lather, rinse repeat. When you want to draw your graph, just query the table where your past counts have been saved and draw the graph from that. If you need to, draw the graph with your past counts, do a query on the results for the current day thus far and append the data. Sound logical? > -----Original Message----- > From: John W. Holmes [mailto:holmes072000@charter.net] > Sent: Thursday, May 01, 2003 4:22 PM > To: 'Merlin'; php-db@lists.php.net > Subject: RE: difficult count statement > > > > I am running a mysql db and I would like to figure out how to count > member > > growth. > > > > For a chart I would need the number of members who have registered > each > > day. > > > > Something like this: > > count (*) AS c > > WHERE > > regdate = '20030501' > > > > does not really make sense, since I would have to query the db for > each > > day > > making it dramaticly slow. > > Has anybody an idea on how to get the numbers registered each day to > fead > > a > > graph with data? > > SELECT date_column, COUNT(*) AS c FROM table GROUP BY date_column > > Will give you a count for each date in the table. If you want it for a > range, then use: > > SELECT date_column, COUNT(*) AS c FROM table WHERE date_column BETWEEN > 20030101 AND 20030201 GROUP BY date_column > > Substitute your own dates, of course. > > ---John W. Holmes... > > PHP Architect - A monthly magazine for PHP Professionals. Get > your copy > today. http://www.phparch.com/ > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
