thanks for the code. will definitely try it :)
On Jun 26, 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
-~----------~----~----~----~------~----~------~--~---
