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
-~----------~----~----~----~------~----~------~--~---
