Calculate average speed of the last MYSQL table records

Question

I’m developing a project that calculates the evolution of the water rise. Made with Arduino and Ultrasonic sensor it feeds the data into a mysql table. every 60 seconds it inserts the value of the height of the water.So far all right.

How could I extract the last two records and report the average speed in a php output ?

Thank you very much

Answer 1

As I do not know the progress of the project, I will assume that you have not yet developed the connecting part of the database.

To use the Mysql with the PHP, it is necessary to use the extension PDO or Mysqli. In this example I will use Mysqli.

// Aqui abrimos uma conexão com o banco de dados
$db = new mysqli("localhost", "root", "123456", "teste");

// Aqui nós utilizamos a função AVG para obter a média dos valores
$result = $db->query("SELECT AVG(`nivel`) AS media FROM (SELECT m.`nivel` FROM nivel_agua m ORDER BY m.id DESC LIMIT 2) nivel_agua;");

// Exibimos o resultado
echo "Media: " . $result->fetch_assoc()["media"];

// Fechamos a conexão
$result->close();
$db->close();

Structure of the table I used for testing

+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int(11)   | NO   | PRI | NULL    | auto_increment |
| nivel | float     | NO   |     | NULL    |                |
| data  | timestamp | NO   |     | NULL    |                |
+-------+-----------+------+-----+---------+----------------+