title: Creating a weekly table with hour intervals
body: <p>I have a "orders" table in which I have a timestamp field so the data looks like this:</p>
  
  <pre><code>id | createdate          | customerid 
  1  | 2013-10-03 09:35:20 |   5
  2  | 2013-10-03 09:35:20 |   9
  3  | 2013-10-04 15:00:20 |   5
  4  | 2013-10-05 12:15:20 |   5
  </code></pre>
  
  <p>So from this table I need to create an html table that shows Mon-Saturday on top and hours on the side so like this:</p>
  
  <pre><code>Hours      |  Monday  | Tuesday  | Wednesday  |  Thursday  |  Friday  | Saturday
  9:30 - 10  |    0     |    0     |     0      |     2      |    0     |    0
  10-11      |    0     |    0     |     0      |     0      |    0     |    0
  11-12      |    0     |    0     |     0      |     0      |    1     |    0  
  </code></pre>
  
  <p>So right now what I have so far is this:</p>
  
  <pre><code>SELECT DATE_FORMAT( createdate,  '%m/%d/%Y' ) AS createdate
  , DATE_FORMAT( createdate,  '%H' ) AS createhour, COUNT( * ) AS totals
  FROM  orders
  WHERE createdate &gt;= CURDATE( ) - INTERVAL DAYOFWEEK( CURDATE( ) ) +6
  DAY AND createdate &lt; CURDATE( ) - INTERVAL DAYOFWEEK( CURDATE( ) ) -1
  DAY GROUP BY DATE_FORMAT( createdate,  '%m/%d/%Y' ) , DATE_FORMAT( createdate,  '%H' )
  </code></pre>
  
  <p>This returns the data but not in the format I need, is there a way to do it in mysql? or does it involve some work in php with arrays?</p>