How to write two SELECT queries to different tables in the same database

psbert · February 19, 2024, 7:36pm

Hi,
How to write two SELECT queries to different tables in the same database.
In the case of INSERT and UPDATE there are no problems. In the case of SELECT, only the one which is first in order is executed. On the second one the program expires, but after commenting out the first query the second query executes correctly and the system works.

I am using NodeMCU v.3, MySQL Connector/Arduino

I added checkpoints in the code so that you can catch where the problem occurs.
The second query should be executed on a table whose part of the name is the result of the first query, so for the test in the second case, I wrote the name of the table rigidly.

Here is my code.

    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    char query[100];
    sprintf(query, "SELECT rok FROM rok_pomiarow ORDER BY id_rok_pomiarow DESC LIMIT 1");
    cur_mem->execute(query);
    Serial.println("Control point: 3");

    column_names *cols = cur_mem->get_columns();
    Serial.println("Control point: 3A");

    for (int f = 0; f < cols->num_fields; f++) {
      Serial.print(cols->fields[f]->name);
      Serial.println("----------------");
      if (f < cols->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 3B");

    row_values *row = NULL;
    Serial.println("Control point: 3C");

    row = cur_mem->get_next_row();
    Serial.println("Control point: 3D");

    int year = atoi(row->values[0]);
    Serial.println("Control point: 3E");

    Serial.println("--------------------------------------------");

    char table_name[20];
    sprintf(table_name, "pomiary_%d", year);
    Serial.println("Control point: 3F");

    Serial.println("Nazwa tabeli z pomiarami: " + String(table_name));
    Serial.println("Control point: 3G");

    delete cur_mem;
    Serial.println("Control point: 3H");

    Serial.println("--------------------------------------------");

    MySQL_Cursor *cur_mem2 = new MySQL_Cursor(&conn);
    Serial.println("Control point: 4A");

    char query2[100];
    //sprintf(query2, "SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM %s ORDER BY id DESC LIMIT 1", table_name);
    sprintf(query2, "SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM pomiary_2024 ORDER BY id DESC LIMIT 1");
    cur_mem2->execute(query2);
    Serial.println("Control point: 4B");

    column_names *cols2 = cur_mem2->get_columns();
    Serial.println("Control point: 4C");

    for (int f = 0; f < cols2->num_fields; f++) {
      Serial.print(cols2->fields[f]->name);
      Serial.println("----------------");
      if (f < cols2->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 4D");

    row_values *row2 = NULL;
    Serial.println("Control point: 4E");

    row2 = cur_mem2->get_next_row();
    Serial.println("Control point: 4F");

    Serial.println("ot = " + String(row2->values[0]));
    Serial.println("ow = " + String(row2->values[1]));
    Serial.println("oc = " + String(row2->values[2]));
    Serial.println("oo = " + String(row2->values[3]));
    Serial.println("Control point: 4G");

    float old_temperatura = atof(row2->values[0]);
    long old_wilgotnosc = atol(row2->values[1]);
    long old_cisnienie = atol(row2->values[2]);
    int old_oswietlenie = atoi(row2->values[3]);

    Serial.println("Control point: 4H");

    delete cur_mem2;
    Serial.println("Control point: 4I");

The result of the operation

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve
Połaczono z bazą MySQL
Control point: 1
Control point: 2
Control point: 3
Control point: 3A
rok----------------
Control point: 3B
Control point: 3C
Control point: 3D
Control point: 3E

Control point: 3F
Nazwa tabeli z pomiarami: pomiary_2024
Control point: 3G
Control point: 3H

Control point: 4A
Control point: 4B

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (29):
epc1=0x4021ab7d epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000000 depc=0x00000000

After commenting out the first query, the result of the operation looks like this

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve
Połaczono z bazą MySQL
Control point: 1
Control point: 2
Control point: 4A
Control point: 4B
Control point: 4C
temperatura----------------
,wilgotnosc----------------
,cisnienie----------------
,oswietlenie----------------
Control point: 4D
Control point: 4E
Control point: 4F
ot = 10.3
ow = 88
oc = 1000
oo = 0
Control point: 4G
Control point: 4H
Control point: 4I
Disconnected.

I've tried various ways, but it didn't work so please help me with this issue.

guix · February 19, 2024, 9:37pm

Welcome

What does the Exception Decoder says ? I suppose it will say something about corrupted stack or other memory error

MySQL_Cursor *cur_mem2 = new MySQL_Cursor(&conn);
...
char query2[100];

Why not reuse cur_mem and query ? And when using new, always make sure the pointer is valid, before using it.

oldcurmudgeon · February 19, 2024, 9:55pm

Error 29 is a store to an invalid address. Your exception address (excvaddr) is zero, so you probably tried to use a null pointer.

psbert · February 20, 2024, 12:17pm

Hi,
Decoding stack results

0x4020ef18: is in Print::println(char const*) (C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\Print.cpp:238).
0x4020ea10: is in HardwareSerial::write(unsigned char const*, unsigned int) (C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266/HardwareSerial.h:193).
0x4020331c: zapiszDaneNaSerwerze() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\WString.h:115
0x40204e5c: is in Adafruit_I2CDevice::read(unsigned char*, unsigned int, bool) (d:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\libraries\Adafruit_BusIO\Adafruit_I2CDevice.cpp:186).
0x40206490: fs::FS::_defaultTimeCB() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\FS.h:260
0x402042f9: loop() at D:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\Czujnik_z_radiem_do_kuchni_firmware_v7.ino:536
0x4021057c: loop_wrapper() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\core_esp8266_main.cpp:258

Using the same variables in both SELECT queries does not help.

My code after changes.

    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    char query[100];
    sprintf(query, "SELECT rok FROM rok_pomiarow ORDER BY id_rok_pomiarow DESC LIMIT 1");
    cur_mem->execute(query);
    Serial.println("Control point: 3");

    column_names *cols = cur_mem->get_columns();
    Serial.println("Control point: 3A");

    for (int f = 0; f < cols->num_fields; f++) {
      Serial.print(cols->fields[f]->name);
      Serial.println("----------------");
      if (f < cols->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 3B");

    row_values *row = NULL;
    Serial.println("Control point: 3C");

    row = cur_mem->get_next_row();
    Serial.println("Control point: 3D");

    int year = atoi(row->values[0]);
    Serial.println("Control point: 3E");

    Serial.println("--------------------------------------------");

    char table_name[20];
    sprintf(table_name, "pomiary_%d", year);
    Serial.println("Control point: 3F");

    Serial.println("Nazwa tabeli z pomiarami: " + String(table_name));
    Serial.println("Control point: 3G");


    Serial.println("Control point: 3H");

    Serial.println("--------------------------------------------");


    Serial.println("Control point: 4A");

    //sprintf(query2, "SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM %s ORDER BY id DESC LIMIT 1", table_name);
    sprintf(query, "SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM pomiary_2024 ORDER BY id DESC LIMIT 1");
    cur_mem->execute(query);
    Serial.println("Control point: 4B");

    cols = cur_mem->get_columns();
    Serial.println("Control point: 4C");

    for (int f = 0; f < cols->num_fields; f++) {
      Serial.print(cols->fields[f]->name);
      Serial.println("----------------");
      if (f < cols->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 4D");

    row = NULL;
    Serial.println("Control point: 4E");

    row = cur_mem->get_next_row();
    Serial.println("Control point: 4F");

    Serial.println("ot = " + String(row->values[0]));
    Serial.println("ow = " + String(row->values[1]));
    Serial.println("oc = " + String(row->values[2]));
    Serial.println("oo = " + String(row->values[3]));
    Serial.println("Control point: 4G");

    float old_temperatura = atof(row->values[0]);
    long old_wilgotnosc = atol(row->values[1]);
    long old_cisnienie = atol(row->values[2]);
    int old_oswietlenie = atoi(row->values[3]);

    Serial.println("Control point: 4H");

    delete cur_mem;
    Serial.println("Control point: 4I");

Returns

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve
Połaczono z bazą MySQL
Control point: 1
Control point: 2
Control point: 3
Control point: 3A
rok----------------
Control point: 3B
Control point: 3C
Control point: 3D
Control point: 3E

Control point: 3F
Nazwa tabeli z pomiarami: pomiary_2024
Control point: 3G
Control point: 3H

Control point: 4A
Control point: 4B

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (29):
epc1=0x4021aaa5 epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000000 depc=0x00000000

Exception 29: StoreProhibited: A store referenced a page mapped with an attribute that does not permit stores
PC: 0x4021aaa5
EXCVADDR: 0x00000000

Decoding stack results
0x4020b7bd: MySQL_Cursor::read_string(int*) at d:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\libraries\MySQL_Connector_Arduino\src\MySQL_Cursor.cpp:394
0x4020b858: MySQL_Cursor::get_field(field_struct*) at d:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\libraries\MySQL_Connector_Arduino\src\MySQL_Cursor.cpp:446
0x4020b8e5: MySQL_Cursor::get_fields() at d:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\libraries\MySQL_Connector_Arduino\src\MySQL_Cursor.cpp:500
0x4020b93d: MySQL_Cursor::get_columns() at d:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\libraries\MySQL_Connector_Arduino\src\MySQL_Cursor.cpp:210
0x40202524: is in sendDataToMySQL(String, String, unsigned char, unsigned char, float, unsigned char, int, int) (D:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7/Czujnik_z_radiem_do_kuchni_firmware_v7.ino:1637).
0x402032e8: zapiszDaneNaSerwerze() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\WString.h:115
0x40204e28: is in Adafruit_I2CDevice::read(unsigned char*, unsigned int, bool) (d:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\libraries\Adafruit_BusIO\Adafruit_I2CDevice.cpp:186).
0x4020645c: fs::FS::_defaultTimeCB() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\FS.h:260
0x402042c5: loop() at D:\Piotr\Czujnik_z_radiem_do_kuchni_firmware_v7\Czujnik_z_radiem_do_kuchni_firmware_v7.ino:536
0x40210548: loop_wrapper() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\core_esp8266_main.cpp:258

psbert · February 22, 2024, 3:23pm

Doesn't anyone know how to create two SELECT queries to different tables so that both queries execute correctly. I've tried different variations after the first query completed. I tested using:
cur_mem->close();
delete cur_mem;
This doesn't do anything, the problem still occurs.

oldcurmudgeon · February 22, 2024, 4:51pm

You need to decode the exception stack to see where things went wrong. The exception is at the top, each step lower gives the call path to the error. The top part of the two cases are different but lower down has the following in common. The bad address problem started somewhere in here.

The lowest one is the start up code. So it is probably not the problem.

Since you didn't include the code it is impossible to say where the problem starts.

I would suggest looking at what you are doing at
Czujnik_z_radiem_do_kuchni_firmware_v7.ino line 536

psbert · February 22, 2024, 6:44pm

I trimmed the code to a minimum. It now looks like this.

#include <Wire.h>
#include <FlexWire.h>
FlexWire W1;  
FlexWire W2; 

// Dane do połączenia z siecią WiFi
char ssid[] = "*****";
char pass[] = "*****";
byte probaLaczeniaZWiFi = 0;
byte maxProbLaczeniaZWiFi = 30;

#include <ESP8266WiFi.h>
// Koniec WiFi

//Dane do połączenia z bazą MySQL
IPAddress server_addr(*, *, *, *);  
char user[] = "****";
char password[] = "****";
char db_name[] = "****";

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
WiFiClient client; 
MySQL_Connection conn(&client);
MySQL_Cursor *cursor;


//////////////////////////////////////////////////////////////////////////

void setup() {

  polaczZWiFi();
  polaczZMySQL();
}


void loop() {
  Serial.begin(9600);

  if (conn.connect(server_addr, 3306, user, password, db_name)) {

    Serial.println("Połaczono z bazą MySQL");

    Serial.println("Control point: 1");

    int yearX;

    Serial.println("Control point: 2");

    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    char query[100];
    //sprintf(query, "SELECT rok FROM rok_pomiarow ORDER BY id_rok_pomiarow DESC LIMIT 1");
    cur_mem->execute("SELECT rok FROM rok_pomiarow ORDER BY id_rok_pomiarow DESC LIMIT 1");
    Serial.println("Control point: 3");

    column_names *cols = cur_mem->get_columns();
    Serial.println("Control point: 3A");

    for (int f = 0; f < cols->num_fields; f++) {
      Serial.print(cols->fields[f]->name);
      Serial.println("----------------");
      if (f < cols->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 3B");

    row_values *row = NULL;
    Serial.println("Control point: 3C");

    row = cur_mem->get_next_row();
    Serial.println("Control point: 3D");

    int year = atoi(row->values[0]);
    Serial.println("Control point: 3E");

    Serial.println("--------------------------------------------");

    char table_name[20];
    sprintf(table_name, "pomiary_%d", year);
    Serial.println("Control point: 3F");

    Serial.println("Nazwa tabeli z pomiarami: " + String(table_name));
    Serial.println("Control point: 3G");


    Serial.println("Control point: 3H");

    Serial.println("--------------------------------------------");


    Serial.println("Control point: 4A");

    cur_mem->close();
    //delete cur_mem;

    //cur_mem = new MySQL_Cursor(&conn);
    //sprintf(query2, "SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM %s ORDER BY id DESC LIMIT 1", table_name);
    //sprintf(query, "SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM pomiary_2024 ORDER BY id DESC LIMIT 1");
    cur_mem->execute("SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM pomiary_2024 ORDER BY id DESC LIMIT 1");
    Serial.println("Control point: 4B");

    cols = NULL;
    cols = cur_mem->get_columns();
    //cols = cur_mem->get_columns();
    Serial.println("Control point: 4C");

    for (int f = 0; f < cols->num_fields; f++) {
      Serial.print(cols->fields[f]->name);
      Serial.println("----------------");
      if (f < cols->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 4D");

    row = NULL;
    Serial.println("Control point: 4E");

    row = cur_mem->get_next_row();
    Serial.println("Control point: 4F");

    Serial.println("ot = " + String(row->values[0]));
    Serial.println("ow = " + String(row->values[1]));
    Serial.println("oc = " + String(row->values[2]));
    Serial.println("oo = " + String(row->values[3]));
    Serial.println("Control point: 4G");

    float old_temperatura = atof(row->values[0]);
    long old_wilgotnosc = atol(row->values[1]);
    long old_cisnienie = atol(row->values[2]);
    int old_oswietlenie = atoi(row->values[3]);

    Serial.println("Control point: 4H");

    cur_mem->close();
    delete cur_mem;
    Serial.println("Control point: 4I");

    delay(10000);

    conn.close();  //zamykamy połaczenie
  }
}

The program returns.

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve

Połaczono z bazą MySQL

Control point: 1

Control point: 2

Control point: 3

Control point: 3A

rok----------------

Control point: 3B

Control point: 3C

Control point: 3D

Control point: 3E

Control point: 3F

Nazwa tabeli z pomiarami: pomiary_2024

Control point: 3G

Control point: 3H

Control point: 4A

Control point: 4B

Bad mojo. EOF found reading column header.

Control point: 4C

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (28):

epc1=0x40201252 epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000000 depc=0x00000000

stack>>>

ctx: cont
sp: 3ffffdf0 end: 3fffffc0 offset: 0150
3fffff40: 40203814 3ffe8b62 3ffeebfc 00000000
3fffff50: 3fffdad0 00000000 40207d38 1928ffb9
3fffff60: 00000000 0026002f 00000000 00000000
3fffff70: 0026002f 00000000 696d6f70 5f797261
3fffff80: 34323032 feefef00 40207d38 8205ca93
3fffff90: 3fffdad0 00000000 3ffeec74 3ffeeca0
3fffffa0: 3fffdad0 00000000 3ffeec74 40204aac
3fffffb0: feefeffe feefeffe 3fffdab0 40100d2d
<<<stack<<<

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

########################################################
ESP Exception Decoder
Sketch: Only_SQL_SELECT FQBN: esp8266:esp8266:nodemcuv2

Exception (28):
epc1=0x40201252 epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000000 depc=0x00000000

stack>>>

ctx: cont
sp: 3ffffdf0 end: 3fffffc0 offset: 0150
3fffff40: 40203814 3ffe8b62 3ffeebfc 00000000
3fffff50: 3fffdad0 00000000 40207d38 1928ffb9
3fffff60: 00000000 0026002f 00000000 00000000
3fffff70: 0026002f 00000000 696d6f70 5f797261
3fffff80: 34323032 feefef00 40207d38 8205ca93
3fffff90: 3fffdad0 00000000 3ffeec74 3ffeeca0
3fffffa0: 3fffdad0 00000000 3ffeec74 40204aac
3fffffb0: feefeffe feefeffe 3fffdab0 40100d2d
<<<stack<<<

Exception 28: LoadProhibited: A load referenced a page mapped with an attribute that does not permit loads
PC: 0x40201252: loop() at D:\Only_SQL_SELECT\Only_SQL_SELECT.ino:110
EXCVADDR: 0x00000000

Decoding stack results
0x40203814: is in HardwareSerial::write(unsigned char const*, unsigned int) (C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266/HardwareSerial.h:193).
0x40204aac: loop_wrapper() at C:\Users\Piotr.Home\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\cores\esp8266\core_esp8266_main.cpp:258

Paste exception to decode...

###############################################

I have no idea why two SELECT queries to a database cannot execute properly.

Does anyone have any idea how to deal with this.

oldcurmudgeon · February 22, 2024, 8:38pm

psbert:

    cols = NULL;
    cols = cur_mem->get_columns();
    //cols = cur_mem->get_columns();
    Serial.println("Control point: 4C");

    for (int f = 0; f < cols->num_fields; f++) {
      Serial.print(cols->fields[f]->name);
      Serial.println("----------------");
      if (f < cols->num_fields - 1) {
        Serial.print(",");
      }
    }
    Serial.println("Control point: 4D");

Check that cols is not null after

cols = cur_mem->get_columns();

since it appears that the print routine is being sent a null pointer.

psbert · February 23, 2024, 10:36pm

I proceeded as you suggested. Just as you guessed, the pointer has a value of NULL. The question is what to do to make this SELECT return the correct result.

    cur_mem->execute("SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM pomiary_2024 ORDER BY id DESC LIMIT 1");
    Serial.println("Control point: 4B");

    cols = NULL;
    cols = cur_mem->get_columns();

    if(cols == NULL)
    {
      Serial.println("cols == NULL");
    }

    Serial.println("Control point: 4C");

The code returns.

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve

Połaczono z bazą MySQL

Control point: 1

Control point: 2

Control point: 3

Control point: 3A

rok----------------

Control point: 3B

Control point: 3C

Control point: 3D

Control point: 3E

Control point: 3F

Nazwa tabeli z pomiarami: pomiary_2024

Control point: 3G

Control point: 3H

Control point: 4A

Control point: 4B

Bad mojo. EOF found reading column header.

cols == NULL

Control point: 4C

Despite trimming the code to a minimum, there are still problems.
I'm stuck at this stage and I'm out of ideas on how to deal with it.

psbert · February 26, 2024, 5:17pm

Welcome,
I tested the simplest solution, although very impractical due to the risk of blocking access to the database due to the large number of connections to the database.
I tried executing both joins in separate database connection sessions and both queries executed correctly.

I tested using both the same set of global variables and independent variables for both queries. In both cases, the correct results were returned from the database.

Apparently, the problem is related to some lock that is imposed on SELECT queries and is not later pulled down before executing the next query. However, this lock is pulled down when the connection to the MYSQL database is terminated.
Interestingly, for INSERT and UPDATE operations, there is no such problem.

I'm not an expert and can't handle this myself.
Therefore, I would appreciate any help.

underhilll · March 3, 2024, 12:00am

You are passing db_name here, and only ever assigning one db_name in the code. This will not allow you to connect to two databases.

In void loop, you are starting a cursor which normally is exited by breaking the loop.

Abstract your SQL server calls into fully wrapped functions which enter, instantiate a cursor, do something, and exit

or

instantiate two cursors (outside main, like in setup), one for each db, and do what you want in main as you need, with each connection

... in summary you are trying to do everything in loop without abstraction and that is much harder or just doesn't work that way because you are re-initializing the connections on a ms interval

psbert · March 3, 2024, 9:40am

I'm not talking about connecting to two databases, but about querying two tables in one database.
Currently, I have implemented it as I described in the previous message, i.e. each SELECT query is executed during a separate database connection session.
For the moment this solution works and I am not blocked on the server at the hosting provider
If a solution could be found on how to execute multiple SELECT queries during one database connection session it would be more convenient than the current solution.

anon27210439 · March 3, 2024, 10:29am

You could request all data in a single query. This is kind of dirty workaround but would work. Something like this (not tested):

select * from 
    (SELECT rok FROM rok_pomiarow ORDER BY id_rok_pomiarow DESC LIMIT 1),
    (SELECT temperatura, wilgotnosc, cisnienie, oswietlenie FROM pomiary_2024 ORDER BY id DESC LIMIT 1)

This creates a join with the first query (just one row) against the second query with the actual data. So you get the year in the first column of each measurement row.

I don't know if you can change the tables structure, it is a poor design that produces this kind of problems. In the measures table you should have the time-stamp in each row.
If you think that the table could grow too much, once per year you move the data to a 'history-backup' table, or export to CSV. That's better than having the year hard-coded in the table name.

If it's not possible to change the tables, then maybe you could create a view in the database to arrange the results and make your life easier in the MCU side. To get the needed data cleaned up and easily in a single shot.

system · August 30, 2024, 10:29am

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
my sql connector select Home Automation	6	9974	May 6, 2021
MySQL auslesen Deutsch	35	7461	May 6, 2021
MySQL Connector/Arduino Français	16	10014	May 6, 2021
NODEMCU - SQL-Database Programming	13	1440	May 6, 2021
arduino due <=> mysql Software	37	13957	May 7, 2021

How to write two SELECT queries to different tables in the same database

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve Połaczono z bazą MySQL Control point: 1 Control point: 2 Control point: 3 Control point: 3A rok---------------- Control point: 3B Control point: 3C Control point: 3D Control point: 3E

Control point: 3F Nazwa tabeli z pomiarami: pomiary_2024 Control point: 3G Control point: 3H

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve Połaczono z bazą MySQL Control point: 1 Control point: 2 Control point: 3 Control point: 3A rok---------------- Control point: 3B Control point: 3C Control point: 3D Control point: 3E

Control point: 3F Nazwa tabeli z pomiarami: pomiary_2024 Control point: 3G Control point: 3H

Related topics

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve
Połaczono z bazą MySQL
Control point: 1
Control point: 2
Control point: 3
Control point: 3A
rok----------------
Control point: 3B
Control point: 3C
Control point: 3D
Control point: 3E

Control point: 3F
Nazwa tabeli z pomiarami: pomiary_2024
Control point: 3G
Control point: 3H

Connected to server version 5.5.5-10.4.24-MariaDB-cll-lve
Połaczono z bazą MySQL
Control point: 1
Control point: 2
Control point: 3
Control point: 3A
rok----------------
Control point: 3B
Control point: 3C
Control point: 3D
Control point: 3E

Control point: 3F
Nazwa tabeli z pomiarami: pomiary_2024
Control point: 3G
Control point: 3H