Page 1 of 1

[SOLVED]MySQL Query Problem

Posted: Thu Sep 15, 2005 9:47 am
by AGISB
I got a hitcounter table for webmaster (ID between 1000 and 3000) referals. It contains ID, timstamp, IP

Now I want to get the number of hits and uniques for each webmaster in the system even if it is 0

So I got this query working:

Code: Select all

SELECT c.ID, count( DISTINCT w.IP) , count( w.IP) FROM Webmaster c LEFT JOIN Webhits w 
ON ( c.ID = w.ID ) WHERE c.ID BETWEEN '1000' AND '3000'
GROUP BY c.Customer_ID
This works fine except that I need to get another Where clause in. I need to get only those counted between two different timestamps.

Code: Select all

w.Timstamp BETWEEN '$firsttimestamp' AND '$lasttimestamp'
If I simply add this I get all active webmasters but not the ones who got no hits or uniques.

How do I add this clause to get the desired result?

Posted: Thu Sep 15, 2005 12:32 pm
by timvw
Make it a LEFT OUTER JOIN...

This way, if there is no "link" with Webhits, the webhits columns will be NULL... If you now change your SELECT clause to IFNULL(whatevercount, 0) AS count you end up exactly with what you want.. Meaby that MySQL already casts the NULL to 0 by default...

Posted: Thu Sep 15, 2005 12:54 pm
by AGISB
I am not sure if I do this right or if I haven't made clear what I want to achive.

Basically I want output like this:


10152 200 340
10153 0 0
10154 76 78
10155 0 0
...

So both the filled and empty lines should show.

The IFNULL stuff I sure do if I only want to see the empty ones but I am not sure how to do it when showing both

Posted: Thu Sep 15, 2005 1:22 pm
by AGISB
Ok here is what I came up with and seems to work.

Code: Select all

SELECT c._ID, count( DISTINCT w.IP) , count( w.IP)
FROM Webmaster c LEFT JOIN Webmasterhits w ON ( c.ID = w.ID )
WHERE (w.Timstamp BETWEEN '1126742400' AND '1126828799') 
OR w.IP IS NULL
GROUP BY c.ID
HAVING c.ID BETWEEN '10000' AND '30000'