Any way to make less queries??

I have a database of users. I need to display the number of signups during different time periods:

today - 100 signups
this week - 1000 signups
this year - 100000 signups
etc.

Is there any way to do this without querying the database for each time period? Is it ok to query so many times?

The database has tens of thousands of rows.

 

either way you do it, you would NEED to query the database at one point to see the results?!

Graham

 

I would do one query to get the "TOTAL" then just do simple date/time functions to figure out signups with each portion.

Personally hitting the db for those are very quick (provided you have decent indexing setup)

They are one line instant queries.

Going back to my first thought to avoid hitting the db just read they entire results into an array with a date stamp. Then count through each month, day & year to come up with your totals.

I think the 3 queries would be less taxing on the system then the building this count function.

Jon

 

You need at least these 3 queries..
a database is made for sorting and counting, so it will be much faster than anything you try to write yourself (ie get all rows and make a count function yourself in perl/scripting/..)

just make sure there is an index on the signup time

the query will run for each hit but the database should cache the result

if you do notice it delays the generating of the page, and as these are numbers don't really change a lot, you could make a seperate table to keep these numbers.. store the 3 values, and change the signup-page so it increases them when someone signs up.
That way you only need to read the 3 values out of the table and the database never has to do a count() anymore.. this will be the quickest way.

 

SELECT
  SUM(CASE WHEN signupdate = CURDATE() THEN 1 ELSE 0) AS numtoday,
  SUM(CASE WHEN WEEK(signupdate) = WEEK(CURDATE()) THEN 1 ELSE 0) AS numthisweek,
  SUM(CASE WHEN YEAR(signupdate) = YEAR(CURDATE()) THEN 1 ELSE 0) AS numthisyear
FROM tablename

Code (markup):

 

Ok. Modifying the above snippett of code, this what I came up with:

// Get clicks
$clicks = mysql_query("SELECT
					  SUM(CASE WHEN clickdate = CURDATE() THEN 1 ELSE 0) AS 'numtoday',
					  SUM(CASE WHEN WEEK(clickdate) = WEEK(CURDATE()) THEN 1 ELSE 0) AS 'numthisweek',
					  SUM(CASE WHEN MONTH(clickdate) = MONTH(CURDATE()) THEN 1 ELSE 0) AS 'numthismonth',
					  SUM(CASE WHEN YEAR(clickdate) = YEAR(CURDATE()) THEN 1 ELSE 0) AS 'numthisyear'
					  FROM clicks WHERE affiliateID = '$affiliateID'");

// Check for errors
if(!$clicks){ mysql_error(); }

// Gets results from database
while($row  = mysql_fetch_array($clicks, MYSQL_ASSOC)){

	$clicksDay = $row['numtoday'];
	$clicksWeek = $row['numthisweek'];
	$clicksMonth = $row['numthismonth'];
	$clicksYear = $row['numthisyear'];
	
	
}

Code (markup):

But it tells me that:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource