Range of dates?

[techiefreak05]

I am building an analytics service, and whenever a page is loaded, it inserts a row into the database with tons of user information. Simple enough.

 

Each row has a DATETIME field (ex, "2010-09-30 11:53:14") and after a while I've been able to count the number of unique visits, and total visits for a particular day. Here's the query that will run for EACH day:

 

SELECT COUNT(DISTINCT(ip)) as unique_hits,
COUNT(ip) as total_hits,
date as raw_date 
FROM tracking
WHERE
date_format(date,'%Y-%m-%d') = '2010-09-30'

 

Basically I want to be able to pick 2 dates from a form, and then loop through each day between them, and run the query above for each day.

 

I hope I made sense... haha.

 

Thanks a ton.

 

[Pikachu2000]

Are you talking about a BETWEEN . . . AND query?

 

SELECT `field` FROM `table` WHERE `date` BETWEEN `start` AND `end`

[techiefreak05]

Actually, I think that would work... but it depends.

 

Does the range go THROUGH the "end" date? or to midnight of?

[Pikachu2000]

It would select all that are greater than or equal to 'start', and less than or equal to 'end'.

[techiefreak05]

After some tweaking, I got it. You were correct, it worked as needed.

 

I ended up with this, in case you're curious:

 

SELECT DISTINCT date_format(date,'%Y-%m-%d') as xdate FROM `tracking` 
WHERE owner_id = '18' AND 
project_id = '1' AND 
date_format(date,'%Y-%m-%d') BETWEEN '".$start."' AND '".$end."'

 

[Pikachu2000]

Excellent. Glad it worked for you.

 

Usually my answers are so far off that people's servers have actually physically exploded!

 

 

[just kidding]