How to split records per hour to display them in a chart?

Question

I have an SQL table like this: sales(product,timestamp) I want to display a chart using the Open Flash Chart, but I don't know how to get the total sales per hour in the last 12 hours. (timestamp column - sale date)

As an example, I will have such an array: array(12,5,8,6,10,35,7,23,4,5,2,16)each number is the total sales for each hour.

Note. I want to use php or just mysql for this.

thank

Answer 1

SQL

SELECT HOUR(timestamp), COUNT(product)
FROM sales
ORDER BY HOUR(timestamp)

Flip the result to get it into an array.

EDIT: Apply requested condition to unix timestamp

SELECT HOUR(timestamp), COUNT(product)
FROM sales
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 12 HOUR))
ORDER BY HOUR(timestamp)

Answer 2

SELECT HOUR(timestamp),COUNT(*)
FROM sales
WHERE timestamp >= DATE_SUB(NOW(),INTERVAL 12 HOUR)
GROUP BY HOUR(timestamp)

Answer 3

-:

foreach timestamp
  use date('G',timestamp) to get hour
  increment array value using the hour as key

- . .