SQL: getting groups with a count from 2 tables

another plea for help with mySQL

[httpwebwitch]

This one seems easy, but it's difficult to describe. I'll do my best.

I have 2 tables, called "hits" and "clicks".

The hits table has a collection of hits, each dated and associated with an affiliate ID.

hitID (autoinc,prim) ¦ affiliateID ¦ datetime

The clicks table is the same - it's a collection of clicks, each dated and associated with an affiliate ID.

clickID (autoinc,prim) ¦ affiliateID ¦ datetime

Given an affiliate ID number and a date range, I want to get a recordset grouped by date, with a COUNT() of the hits and clicks.

That is, I want a count of records in the hits table that have
affiliateID=x AND date=y

AND a count of records in the clicks table that have
affiliateID=x AND date=y

... grouped as one row per date(y).

I want to display them thusly:

while ($row=mysql_fetch_array($result)){
echo "<BR>";
echo $row['datetime'];
echo " ¦ ";
echo $row['hits'];
echo " ¦ ";
echo $row['clicks'];
}

So that it looks like this:

Stats for Affiliate# 5546
DATE ¦ HITS ¦ CLICKS
2004/06/10 ¦ 45 ¦ 23
2004/06/11 ¦ 36 ¦ 22
2004/06/12 ¦ 35 ¦ 15
2004/06/13 ¦ 41 ¦ 26
2004/06/14 ¦ 42 ¦ 23
2004/06/15 ¦ 30 ¦ 24

Here is my attempt:

SELECT 
COUNT(hits.hitID) as hitcount, 
COUNT(clicks.clickID) as clickcount, 
DATE_FORMAT(hits.datetime,'%Y-%m-%d') as date 
FROM hits,clicks 
WHERE 
hits.affiliateID=".$_SESSION['affiliateID']." 
AND clicks.affiliateID=".$_SESSION['affiliateID']." 
AND DATE_FORMAT(hits.datetime,'%Y-%m-%d')>='".$begindate."' 
AND DATE_FORMAT(hits.datetime,'%Y-%m-%d')<='".$enddate."' 
GROUP BY DATE_FORMAT(hits.datetime,'%Y-%m-%d')

This returns the "hits" counted and grouped the way I want them, but the clicks are miscounted; they are the same as the hits. I don't suppose I can put two COUNT() functions in like that.

Am I close? Getting warmer?

[httpwebwitch]

OK, I found a stupid way to do it:

I group and count the hits. then I loop through the result. inside the loop I get a separate COUNT() of the clicks, using the date and affiliateID from the hits.

is there a more elegant way?

[httpwebwitch]

Ah, as I thought, my stupid version isn't perfect. If a date exists in the clicks but not in the hits, the clicks aren't counted.

still searching...

[Lord Majestic]

Its very dangerous to directly insert bits into SQL that can be changed by the client (browser) - this leaves you open to SQL injection attacks. I mean this bit - $_SESSION['affiliateID'].

Few issues with your code:

1) you limit by date only on "hits" table - this will force you to count clicks for ALL date range.

2) you need to ensure that clicks and hits count is for the same date - this means you need to join them on their dateformatted dates

3) counts will be the same because some of rows will be counted twice or more (due to joining that you will have to do)

Try using count(distinct clickID) and count(distinct hitID) - note that this is not performance friendly. I tried it on my local SQL server and it worked.

Alternatively you can do this (you will have to modify query a bit):

SELECT
convert(varchar,hits.date,111) as day,
count(*) as hitscount,
-1 as clickscount
FROM hits
WHERE hits.affiliateID=1
GROUP BY convert(varchar,hits.date,111)

union

SELECT
convert(varchar,clicks.date,111) as day,
-1 as hitscount,
count(*) as clickscount
FROM clicks
WHERE clicks.affiliateID=1
GROUP BY convert(varchar,clicks.date,111)

This will basically count what you need from both table and join together with first row being hit, then click. Not pretty but more efficient than doing "distincts". You can determine which row if what by checking whether clicks or hits are set to -1.

[RonPK]

It's obviously easy to manipulate $_GET/POST/COOKIE, but I don't see how a user can modify values in $_SESSION. Could you elaborate on that?

[Lord Majestic]

oops, my bad - poor knowledge of PHP shows :o

I mistakenly thought that _SESSION contained some user inputted data.

[coopster]

A

UNION

would be one option, but you may as well use a temporary table solution, especially if you are on MySQL < 4.0. This should give you the concept at least...

CREATE TEMPORARY TABLE tmp1 
 SELECT 
 DATE(datetime) AS day, 
 COUNT(*) AS hit 
 FROM hit 
 WHERE affiliateID = 1 
 GROUP BY day 
; 
CREATE TEMPORARY TABLE tmp2 
 SELECT 
 DATE(datetime) AS day, 
 COUNT(*) AS click 
 FROM click 
 WHERE affiliateID = 1 
 GROUP BY day 
; 
SELECT 
 tmp1.day, 
 hit, 
 click 
 FROM tmp1, tmp2 
 WHERE tmp1.day = tmp2.day 
; 
DROP TABLE tmp1, tmp2;

The only reason I state that this may be better than a UNION in this case is first, the obvious, your MySQL version doesn't offer UNION as a solution yet. But second, we are doing two SELECT statements anyway, might as well use a JOIN on the backend rather than merging the two tables. Hope this makes sense.

[httpwebwitch]

temporary tables? cool! well I didn't even know we could do that. Any time I've done any sort of multi-table comparison, I've done separate SELECTs and sorted through the muck with PHP.

I presume doing it with SQL is faster, more efficient than doing it at the PHP level? Such things usually are...

[coopster]

I'm not sure which will be faster. I am sure that it will depend upon how much data you are dealing with ;)

You'll have to test and compare the solutions for your particular installation. I'm guessing the db server is going to be faster.