Return every second row of the SQL query

Question

I am working with an application that uses phpActiveRecord and mySQL to output data from a network of sensors and build it on several flot.js graphs on the client.

There are several timeframes that the user can choose between to influence the range of data displayed by the graphs. 2hrs, 24hrs, 3 days and 1 week.

Sensors are sent to the database every 60 seconds, so when plotting, the query pulls in all lines between now and DATE_SUB(CUR_DATE - INTERVAL ? DAY) where? either 1, 3 or 7, etc.

However, this leads to a massive number of returned rows (60,000 + for the full week!) And causes huge server delays and errors.

I know that I can simply increase the maximum memory available for requests in the php.ini , but this is hardly a good solution and does not solve the speed problem.

My question is: is there a way that I can easily select only every second or third row from the required date range depending on the length of the interval that the user wants to view?

In C or Java, I would do something like modulo select to return alternative strings, but I can't think of a way to do this in the current structure.

Any ideas would be appreciated. Thanks.

Answer 1

 <? $row = 1; WHILE QUERY { if ($row % 2 == 0) { echo "Yourstuff"; } else { //Nothing } $row++; } ?> 

This should help you think about a solution. This may not be ideal for you, but I hope this helps ... For every third, use $ row% 3 and so on ...

Answer 2

Try:

 $i = 0; foreach($row as $data){ if($i == 1){ //Do whatever with your 2n record $i++; } if($i > 1){$i = 0;} //Reset the counter } 

Answer 3

Returning every second line is also not an ideal solution. You can use the cache.

The easiest way is to cache the table in another table. Set up automatic cron jobs periodically.

 datas datas_cache ( Only Results ) 

If you need a more professional solution, you need to cache the result in files, you can use JSON

Answer 4

In the end, I managed to control the lines above.

However, this was not an acceptable solution to the problem that I encountered, so I decided to try and create the server side of my server in order to try to completely avoid the problem. It seems logical that creating diagrams with so many data points should be done on the server and just push the image down on the html5 canvas or some of them. To do this, try pChart. Thanks to all respondents anyway.