thanks for the code. will definitely try it :)
--
http://frinity.blogspot.com
On Thu, Jun 26, 2008 at 6:13 PM, grigri <[EMAIL PROTECTED]> wrote:
>
> Assuming you're using an auto-incrementing id, and entries are never
> deleted, you could do this:
>
> SELECT d1.date,d2.date
> FROM hits h1
> INNER JOIN hits h2
> ON (h2.id=h1.id+1)
> WHERE h1.date<>h2.date
> AND TO_DAYS(h2.date)-TO_DAYS(h1.date)>1
>
> cakeified:
>
> class Hit extends AppModel {
> function findGaps() {
> $this->bindModel(array(
> 'hasOne' => array(
> 'NextHit' => array(
> 'className' => 'Hit',
> 'foreignKey' => false,
> 'conditions' => 'NextHit.id=1+Hit.id',
> 'type' => 'inner'
> )
> )
> ));
> $data = $this->find('all', array(
> 'fields' => array(
> '(DATE_ADD(Hit.date, INTERVAL 1 DAY)) AS start_date',
> '(DATE_SUB(NextHit.date, INTERVAL 1 DAY)) AS end_date'
> ),
> 'recursive' => 0,
> 'conditions' => array(
> 'Hit.date<>NextHit.date',
> '(TO_DAYS(NextHit.date)-TO_DAYS(Hit.date)>1)'
> )
> ));
> // Post-process here
> return $data;
> }
> }
>
> (Just quickly typed - might have typos or need a bit of coaxing)
>
> hth
> grigri
>
> On Jun 26, 11:30 am, Fahad <[EMAIL PROTECTED]> wrote:
>> hi,
>> i am currently working on a site that requires reporting daily hits of
>> its web pages.
>> this is the SQL structure
>> ============
>> table : hits
>> id, page_id, hits, date (yyyy-mm-dd)
>> ============
>>
>> there is a possibility that there will be days when the site receives
>> no hits at all. therefore, no record will be stored for that 'date' in
>> the hits table. and the table may look like this (assume i am
>> recording stats only for the page with id #1):
>> =======
>> table : hits (with records)
>> id, page_id, hits, date
>> 1, 1, 4, 2008-06-22
>> 2, 1, 7, 2008-06-23
>> 3, 1, 5, 2008-06-26 (notice the gap of 2 days)
>> =======
>>
>> is there any way how i can get the missing days values (hits value
>> will be set to 0) via model (probably using a behaviour)?
>>
>> thanks
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
