List visitors between two dates. But also ouput "empty" dates in same query.

[gladideg]

I have a simple logger that monitors bandwidth.

 

This query outputs all dates from $sqlfrom and $sqlto that has bandwidth used.

 

 

SELECT sum(t.bytes) AS bytes, DAY(t.timestamp) AS day
FROM traffic AS t
INNER JOIN folders AS f ON (f.ID = t.folderID) 
WHERE t.timestamp BETWEEN '$sqlfrom' AND '$sqlto'
GROUP BY DAY(t.timestamp)

 

What it doesn't do, is to output the dates that are empty (0). How can I do it?

 

I want it like to include as I mentioned above, dates that are empty; like the red text below. (I don't want to use PHP to do this if possible)

 

Array

(

  [0] => Array

  (

      [bytes] => 13434

      [day] => Sep-01-2009

  )

  [1] => Array

  (

      [bytes] => 0

      [day] => Sep-02-2009

  )

  [2] => Array

  (

      [bytes] => 17777

      [day] => Sep-03-2009

  )

 

To clarify, this is what my query normaly outputs if a date doesn't match any date on a set date.

 

Array

(

  [0] => Array

  (

      [bytes] => 13434

      [day] => Sep-01-2009

  )

  [1] => Array

  (

      [bytes] => 17777

      [day] => Sep-03-2009

  )

 

Ie. Missing Sep-02-2009

 

[inspireddesign]

Take out GROUP BY and see what output you get... this might be what's causing you to not "see" the NULL date output.  It wont be the output you desire but might help you figure out another approach.

[Zane]

use an OUTER JOIN instead of an INNER JOIN

[gladideg]

Take out GROUP BY and see what output you get... this might be what's causing you to not "see" the NULL date output.  It wont be the output you desire but might help you figure out another approach.

 

I can't use the SUM or COUNT function without grouping the query against a column. So that won't work :-/ Right now I have to match two arrays against each other. (One with the complete FROM -> TO timespan, and the other one with the bandwidth results) And it works fine. A bit complicated though.

[gladideg]

use an OUTER JOIN instead of an INNER JOIN

 

That didn't work. It seems mysql doesn't SUM the columns that are empty in the BETWEEN span.

[fenway]

You'll need to use an integers table to generate a recordset that include one row for every date you want.