0

I have to count the rows in several date ranges (today, week, month) from a mysql table.

Currently I am running through a loop in my PHP script and doing an individual query for each date range, which I know is a bad idea.

<?php
$now = "2016-04-21";
$today = $now;
$week = date( 'Y-m-d', strtotime( '-7 day', strtotime( $now ) ) );
$month = substr( $now, 0, 7 );

$res1 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND DATE_FORMAT( date, '%Y-%m-%d' ) = '$today'" );
$res2 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND ( t.date BETWEEN ( '$week' ) AND ( '$today' ) )");
$res3 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND DATE_FORMAT( t.date, '%Y-%m' ) = '$month'" );

So I would like to use a SINGLE QUERY to do so.

I have found count rows in multiple date ranges and query for grabbing multiple date ranges but I'm not sure which is the best, and how to do in my case

Community
  • 1
  • 1
ipel
  • 1,326
  • 1
  • 18
  • 43
  • can you explain the structure of the table a bit? what columns you have? etc, so that I can have a better idea of what you are doing. – Webeng Apr 21 '16 at 08:12
  • Table has 4 columns "id int(10) autoincrement primary", "date date", "user varchar(32) index" and "pageview int(10)". In pageview are stored the total number of daily pageview – ipel Apr 21 '16 at 08:20
  • alright, so each row is a user with a number of page views, and you'll probably have a lot of those rows, and then you want to count how many total page views you had in the past month, past week, and past day, is that correct? Also, you mentioned you are doing a loop currently. Did you mean you are adding user's page views 1 by 1 together? – Webeng Apr 21 '16 at 08:26

3 Answers3

1

If you really want to retrieve the three values using one query:

$res = mysql_query("
SELECT sum1, sum2, sum3 
FROM (SELECT SUM(pageview) AS sum1 FROM statistics WHERE user = '$id' AND DATE_FORMAT( date, '%Y-%m-%d' ) = '$today') AS T1
JOIN (SELECT SUM(pageview) AS sum2 FROM statistics WHERE user = '$id' AND ( t.date BETWEEN ( '$week' ) AND ( '$today' ) )) AS T2
JOIN (SELECT SUM(pageview) AS sum3 FROM statistics WHERE user = '$id' AND DATE_FORMAT( t.date, '%Y-%m' ) = '$month') AS T3");

Although this is not much better performance wise, i don't really see a reason to do this.

P.S. it's a bad habit to insert variables into queries like this. In this case your safe but if you use user inputted variables you're open to SQL injection. Try learning prepared statements

Jester
  • 1,408
  • 1
  • 9
  • 21
  • no because I need three separate results: today, week and month. – ipel Apr 21 '16 at 08:21
  • in that case, you can do it in one query but might as well do it in three. that is much cleaner codewise i think and you don't win much efficiency by making it one query – Jester Apr 21 '16 at 08:23
1

You can combine all your conditions by OR to select all data in one query. And this construction can be used in SELECT to sum rows that meet some condition

SUM(IF(condition, sum_column_name, 0))

Your query can look like this if you want to return one row with three sums

SELECT
  SUM(IF(t.date = '$today', pageview, 0)) AS pageview_today,
  SUM(IF(t.date BETWEEN '$week' AND '$today', pageview, 0)) AS pageview_week,
  SUM(IF(DATE_FORMAT(t.date, '%Y-%m') = '$month', pageview, 0)) AS pageview_month
FROM statistics t
WHERE user = '$id' AND (
  t.date = '$today'
  OR (t.date BETWEEN '$week' AND '$today')
  OR DATE_FORMAT(t.date, '%Y-%m') = '$month'
)

Or you can make 3 queries and combine them with UNION as was mentioned by @quazardous. And this method will give you 3 different rows.

Andrew
  • 1,858
  • 13
  • 15
0

Use Union ?

Select a from B
Union
Select B from C
...
quazardous
  • 846
  • 10
  • 15