Access the database from the PHP programming language
In this last article in the series on the Using a web server to store data from devices connected to the Internet of Things, explains how to record in a database MySQL the information that IoT nodes send to the server making HTTP POST requests. Readers of this article, of the entire blog, may not be particularly familiar with programming in the PHP language but surely yes with the programming of microcontrollers in languages C o C++ so, except for some details, such as preceding the dollar sign ($) to the variables, you will be able to follow the examples without the need for further explanations since they have been done following a very neutral programming style, not specific to PHP.
Store information in the database
As explained in the article on the IoT data storage by making HTTP POST requests to a web server, at the end of the headers, the body of the POST request contains the data that is sent to the server. The most common way to send this information to the server is in plain text format because it is easier to analyze it even "manually." When the data sent to the server is complex, it will surely be convenient to structure it using, for example, the format XML o JSON. In the usual use of a web server to manage information from devices connected to the Internet of Things, it is not common to need a data structure, so it is normal to send them in plain text in the format variable=valor.
In the HTTP POST request of the following example, the resource (normally a web page) /iot/grabar_temperatura is requested from the polaridad.es server and three variables are sent: ne, tp and cr, which respectively contain the values "12", "10.26 » and «2.18» It is also important to remember that there is a blank line to separate the headers from the data.
|
1
2
3
4
|
POST /iot/grabar_temperatura HTTP/1.1
Host: polaridad.es
ne=12&tp=10.26&cr=2.18
|
The final goal of the following code PHP will send to database server MySQL the order SQL:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
INSERT
INTO `calefacciones`
(
numero_estancia,
temperatura,
corriente
)
VALUES
(
12,
10.26,
2.18
);
|
With which a new record will be created (INSERT)
in the heating database (INTO )calefacciones
assigning to fields (numero_estancia,temperatura,corriente)
the values corresponding to the HTTP POST request VALUES (12, 10.26, 2.18)
The connection to the database is done with the function mysqli_connect according to the format: mysqli_connect($servidor,$usuario,$clave,$base_datos) that returns a pointer to the connection-object and that uses the variables that define access (such as username, password...) and that have been previously assigned for future hypothetical uses in the script.
To detect if the connection has been successful, the function is used mysqli_connect_errno() which returns the error number that may have occurred or zero (false) if the connection was established correctly. To notify errors, the application that makes the HTTP request is responded with a value of zero, in this case the program that is executed in the µC of the IoT node.
|
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
|
$fin_linea=PHP_EOL;
$servidor=‘localhost’;
$usuario=‘pelaez’;
$clave=‘1234’;
$base_datos=‘base_datos_temperaturas’;
$conexion=mysqli_connect($servidor,$usuario,$clave,$base_datos);
if(mysqli_connect_errno())
{
echo 0; // Devolver el valor 0 para indicar que se ha producido un error
}
else
{
$numero_estancia=(int)$_POST[‘ne’];
$temperatura=(float)$_POST[‘tp’];
$corriente=(float)$_POST[‘cr’];
$consulta_sql =‘INSERT INTO calefacciones (numero_estancia,temperatura,corriente) ‘;
$consulta_sql.=‘VALUES (‘.$numero_estancia.‘,’.$temperatura.‘,’.$corriente.‘);’;
$resultado=mysqli_query($conexion,$consulta_sql);
if($resultado) // Si no se ha producido un error al realizar la consulta…
{
echo mysqli_insert_id($conexion); // Devolver el índice del nuevo registro (¡>0!)
}
else
{
echo 0; // Devolver el valor 0 para indicar que se ha producido un error
}
mysqli_close($conexion);
}
echo $fin_linea;
|
Before adding to text that is stored in $consulta_sql with which the order is composed SQL that is sent to the database server the information that has arrived in the POST request variables are preprocessed, at a minimum, to avoid an attack by SQL injection. In the previous example, the conversion to the corresponding data type is forced (int) (integer) or (float) (floating point decimal) which would be enough to eliminate possible malicious code added to the request data by Web server.
As can be seen, in the PHP language The period symbol (.) is used to concatenate the texts that form the order SQL or the dot and equal sign operator (.=) to add text to the right of the one that already stores a variable and the single quote (‘) is also used to enclose text constants, not just characters. Although in this case double quotes («) can also be used, in PHP language are used to process the content, for example, including variables within the text in the format $texto="Me llamo $nombre"; as an alternative to the format $texto='Me llamo '.$nombre; also allowing you to include quotes of one type in another without having to use escape signs whenever doubles alternate within singles or singles within doubles as in the assignment $texto='esto no hay que "escaparlo" en PHP';.
To execute the query to the server MySQL function is used mysqli_query with the format mysqli_query($conexion,$consulta_sql) which takes as parameters the object-connection to the database and the text with the order SQL that has been composed.
The function mysqli_query($conexion,$consulta_sql) returns a cursor-object that can be used to traverse the returned data if applicable or, as in the example above, to obtain information about the operation, specifically to know the index assigned to the new record that the operation has created in the table "heaters" with the function mysqli_insert_id($conexion)
The value returned by mysqli_query($conexion,$consulta_sql) can evaluate to false in a boolean operation to determine that an error has occurred. In the previous example it is used to return, as in the case of the connection error, a zero to the application that makes the POST request. In this way, the program will return a number greater than zero that represents the index of the new record if the operation is correct or a zero if the operation produces an error.
To free the resources that have been assigned to the database connection, it is "closed" using the function mysqli_close($conexion)
Read information from the database
Except in architectures for fog computing Most IoT nodes limit themselves to sending the information captured by their sensors to the server, that is, the Web server It only communicates with them to store the information, so with the previous example a good part of the cases that will arise in this application have already been resolved. The next step could be to create a website that shows the data monitored by devices connected to the Internet of Things, a work of frontend which is outside of what is being discussed in this series of tutorials.
What can happen is that an IoT node has a certain interactivity and behaves differently based on a historical data or foresees the possibility of altering its behavior according to a configuration that comes to it from a server or even the node is a screen that shows a graph with the data that has been recently monitored compared to that acquired on previous dates. For all these situations it is also interesting to be able to read data from the server MySQL by Web server as illustrated in the following example which simulates obtaining a list of the dates of the alarm states determined by the moments in which the temperature exceeded 40°C
|
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
|
$fin_linea=PHP_EOL; // Fin de línea. Usar <br> para HTML si se va a mostrar la salida en algún tipo de pantalla o consola no es necesario
$servidor=‘localhost’;
$usuario=‘pelaez’;
$clave=‘1234’;
$base_datos=‘base_datos_temperaturas’;
$conexion=mysqli_connect($servidor,$usuario,$clave,$base_datos);
if(mysqli_connect_errno())
{
echo ‘Error al conectar a la base de datos: ‘ . mysqli_connect_error();
}
else
{
$consulta_sql=‘SELECT ‘;
$consulta_sql.=‘estancia, temperatura, DAY(fecha), MONTH(fecha), YEAR(fecha), TIME(fecha) ‘;
$consulta_sql.=‘FROM valor_temperaturas ‘;
$consulta_sql.=‘WHERE temperatura>40.0 ‘;
$consulta_sql.=‘ORDER BY fecha DESC;’;
$resultado=mysqli_query($conexion,$consulta_sql); // El tercer parámetro de mysqli_query, no usado, es el modo MYSQLI_STORE_RESULT por defecto o MYSQLI_USE_RESULT para grandes cantidades de datos
if($resultado) // Si no se ha producido un error al realizar la consulta
{
$total_resultado=mysqli_num_rows($resultado);
if($total_resultado) // Si se ha encontrado algún resultado
{
echo ‘Se han encontrado ‘.$total_resultado.‘ estados de alarma:’.$fin_linea;
for($numero_resultado=0;$numero_resultado<$total_resultado) // Recorrer los resultados
{
mysqli_data_seek($resultado,$numero_resultado); // Mover el cursor al registro correspondiente (no tendría por qué ser consecutivo)
$registro=mysqli_fetch_row($resultado); // Almacenar en un vector los datos del registro
echo ‘El ‘.$registro[2]; // Tercer campo de la consulta (día)
echo ‘ del ‘.$registro[3]; // Cuarto campo de la consulta (mes)
echo ‘ de ‘.$registro[4]; // Quinto campo de la consulta (año)
echo ‘ a las ‘.$registro[5]; // Sexto campo de la consulta (hora)
echo ‘ en la estancia “‘.$registro[0].‘”‘; // Primer campo de la consulta (estancia)
echo ‘ la temperatura superó el máximo de 40°C (‘.$registro[1].‘)’; // Segundo campo de la consulta (temperatura)
echo $fin_linea;
}
}
else
{
echo ‘No se ha encontrado ningún estado de alarma’.$fin_linea;
}
mysqli_free_result($resultado);
}
mysqli_close($conexion);
}
|
In the example above, to query the database the command is used SQL SELECT according to the basic format SELECT campos FROM tabla WHERE condición ORDER BY campo DESC with the only peculiarity of adding the functions to the date field DAY, MONTH, YEAR y TIME in order to obtain the day, the number of the month, the year and the time separately. The condition imposed is that the temperature is greater than 40.0 and is ordered using the date field from highest (most current) to lowest (oldest) indicating it with the clause DESC
To loop through the values returned by the query from a loop for with a known dimension the function is used mysqli_num_rows($resultado) which indicates the number of records that have been found. With the function mysqli_data_seek($resultado,$numero_resultado) the results cursor can be moved to a specific position expressed by the loop counter for, $numero_resultado, in the example.
To store in a vector the fields of the record pointed to by the result cursor, the function is used mysqli_fetch_row($resultado) which is assigned to the variable $registro which will later be used to form a phrase with the different values, accessing them by their indexes.
Once all the values have been traversed, the resources assigned to the query result are released SQL with function mysqli_free_result($resultado)
Process information from the database. Compare values.
On some occasions it is convenient for the information processing to be centralized on the server even if it were possible to do it in the IoT nodes in the style fog computing. In the following example the reasons that are simulated for doing so are security; The node has information about its key (lock) and about a request (key) but does not know if it is appropriate to give way to the combination of both, so it must consult the server, which is the one that makes the decision and informs the node by responding zero (to indicate a failed comparison) or one (to indicate that the comparison was successful) based on the result of a query to your database.
With this excuse you can see an example in which data is received from the device connected to the Internet of Things (a key code and a lock code), a result is returned (one or zero depending on the result being true or false) and a small processing of the information is carried out consisting of comparing the results obtained when consulting the database with those sent by the IoT node.
|
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
|
$fin_linea=PHP_EOL;
$servidor=‘localhost’;
$usuario=‘pelaez’;
$clave=‘1234’;
$base_datos=‘base_datos_puertas’;
$conexion=mysqli_connect($servidor,$usuario,$clave,$base_datos);
if(mysqli_connect_errno())
{
echo 0; // Devolver el valor 0 para indicar que la comparación no es válida o que se ha producido un error (en este caso es un error)
}
else
{
$cerradura=hexdec($_POST[“c”]);
$llave=hexdec($_POST[“l”]);
$consulta_sql=‘SELECT llave FROM cerraduras WHERE cerradura=”.$cerradura.“;’;
$resultado=mysqli_query($conexion,$consulta_sql);
if($resultado) // Si no se ha producido un error al realizar la consulta
{
$total_resultado=mysqli_num_rows($resultado);
if($total_resultado) // Si se ha encontrado algún resultado
{
$numero_resultado=0;
$buscando_cerradura=TRUE;
while($buscando_cerradura&&$numero_resultado<$total_resultado)
{
mysqli_data_seek($resultado,$numero_resultado);
$registro=mysqli_fetch_row($resultado);
if($registro[0]==$llave)
{
$buscando_cerradura=FALSE;
}
else
{
$numero_resultado++;
}
}
// echo !$buscando_cerradura;
if($buscando_cerradura)
{
echo 0; // Devolver el valor 0 para indicar que la comparación no es válida (o que se ha producido un error; y aquuí no es el caso)
}
else
{
echo 1; // Devolver el valor 1 para indicar que la comparación SÍ es válida
}
}
else
{
echo 0; // Devolver el valor 0 para indicar que la comparación no es válida o que se ha producido un error
}
}
else
{
echo 0; // Devolver el valor 0 para indicar que la comparación no es válida o que se ha producido un error
}
mysqli_close($conexion);
}
echo $fin_linea;
|
In the previous example, the hexdec function is used to obtain a decimal number from a text that represents a hexadecimal number and is the one sent by the IoT device. The added advantage of using this function is to avoid, as explained before, an attack by adding code SQL malicious to the data of the POST request.
Post Comment