[SOLVED]MySQL Query Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

[SOLVED]MySQL Query Problem

Post 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?
Last edited by AGISB on Thu Sep 15, 2005 1:23 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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'
Post Reply