List:General Discussion« Previous MessageNext Message »
From:Dan Tappin Date:August 17 2005 2:30pm
Subject:Re: SELECT question - query for records over a series of dates
View as plain text  
I think you might be one to something here... is there such a thing  
as a while loop in MySQL?  i.e. can I fill a table with data via a  
MySQL query?  I guess I could do it via PHP...

I could create a temp table with one column of dates for the range I  
am looking for and then LEFT JOIN my log table and match the dates.

Having a dedicated table would work but would be kind of a waste of  
space / resources.  These queries will not be run that often.

Dan T

On Aug 16, 2005, at 10:26 PM, Michael Stassen wrote:

> Add a table:
>
>   CREATE TABLE `dates` (`date` DATE,
>                          UNIQUE KEY `date_idx` (`date`)
>                        );
>
> Insert one row into dates for each day.  Now you can use something  
> like this:
>
>   SELECT dates.date, COUNT(*) as hits
>   FROM dates
>   LEFT JOIN table on dates.date = DATE(table.date_impression)
>   WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16'
>   GROUP BY dates.date;
>
> Populating the dates table initially is a small (one-time) pain.   
> You could keep it filled with a once-a-day script to insert the  
> current date.
>
> Michael
>

Thread
SELECT question - query for records over a series of datesDan Tappin17 Aug
  • Re: SELECT question - query for records over a series of datesDaniel Kasak17 Aug
  • Re: SELECT question - query for records over a series of datesMichael Stassen17 Aug
    • Re: SELECT question - query for records over a series of datesDan Tappin17 Aug
      • Re: SELECT question - query for records over a series of datesMichael Stassen18 Aug