-
Notifications
You must be signed in to change notification settings - Fork 10
/
esp-database.php
141 lines (120 loc) · 4.27 KB
/
esp-database.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
<!--
Rui Santos
Complete project details at https://RandomNerdTutorials.com/cloud-weather-station-esp32-esp8266/
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files.
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
-->
<?php
$servername = "localhost";
// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";
function insertReading($sensor, $location, $value1, $value2, $value3) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3)
VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";
if ($conn->query($sql) === TRUE) {
return "New record created successfully";
}
else {
return "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
function getAllReadings($limit) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, sensor, location, value1, value2, value3, reading_time FROM SensorData order by reading_time desc limit " . $limit;
if ($result = $conn->query($sql)) {
return $result;
}
else {
return false;
}
$conn->close();
}
function getLastReadings() {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, sensor, location, value1, value2, value3, reading_time FROM SensorData order by reading_time desc limit 1" ;
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
function minReading($limit, $value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT MIN(" . $value . ") AS min_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS min";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
function maxReading($limit, $value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT MAX(" . $value . ") AS max_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS max";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
function avgReading($limit, $value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS avg";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}
?>