0

I need to make a googlechart print 4 lines that will represent the evolution of temperature of 4 sensors that store their readings in MySQL db.

My schema is:

id | datetime | node_id | temperature

if I run the query: 

SELECT * FROM sensor_readings ORDER BY id DESC LIMIT 4

the result is like:

4 | 21-5-2014 17:00 | 3 | 18.6
3 | 21-5-2014 17:01 | 1 | 18.5
2 | 21-5-2014 17:02 | 4 | 18.7
1 | 21-5-2014 17:04 | 2 | 18.2

For on node I could just query the results - WHERE node_id = 1 Now for four nodes how can I make my query in order to be able to echo a code like

[date, temperature (of node1), node1, temperature (of node2), node2 ....]

So that google chart is able to print my graph? I've made several attempts to load results in temporary arrays in php, without luck. Any idea how I could approach this issue? Thanks a lot!

Tomoyose
  • 21
  • 4
  • something like http://stackoverflow.com/questions/7674786/mysql-pivot-table ? – Marco Mariani May 21 '14 at 14:41
  • Please provide an example, with numbers, of your desired result set. Please pay attention to what you want done with the fact that your readings from different sensors are not at the same time. – O. Jones May 21 '14 at 14:59

1 Answers1

-1

try with adding group by

   SELECT * FROM sensor_readings ORDER BY id DESC GROUP BY datetime;

after that, you can use ajax to collect the result of the query as response. then put the response into a or

or what you want

angel
  • 322
  • 1
  • 7
  • I'm downvoting this because it shows a misunderstanding of `GROUP BY`. There's no obvious aggregate query here. – O. Jones May 21 '14 at 15:00
  • why : `[date, temperature (of node1), node1, temperature (of node2), node2 ....]` I saw date, tmp (node 1), node1, [Where is date 2] tmp (node2), node 2 ... that's why i add a group by. i'm sorry if i misunderstant....... – angel May 21 '14 at 15:05
  • `GROUP BY` only makes sense if aggregate functions like `COUNT(*)` or `AVG(*)` appear in the `SELECT` clause. http://beginner-sql-tutorial.com/sql-group-by-clause.htm – O. Jones May 21 '14 at 15:22