MySQL COUNT with Node-Red

[PhilLu1]

Thank you anka001, but not beter..

[jbudd]

@jbudd

Try running this flow.

Did you try the flow I posted above?

[PhilLu1]

Finaly, even a simple 'COUNT' never works

[PhilLu1]

How to integrate that MySQL perfectly working Query into Node-Red???

SELECT COUNT(*) FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM`LIKE 'Sang' AND `CLASS` LIKE 'Chimie' AND `SCALE_TYP` LIKE 'Quantitatif' AND `TIME_ASPCT`LIKE 'Temps ponctuel'

[jbudd]

I'm not offering any more assistance here.

[PhilLu1]

I just ask a simple solution...

[Colin]

@PhilLu1

I just ask a simple solution.

@jbudd asked if you have tried the flow he posted. You have not answered that.

[isabellatipa]

@PhilLu1

Hello,
I try to use COUNT with Node-Red on a MySQL DB.
Query is perfectly working on MySQL PHP My Admin...

SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE 'Isolat' AND `CLASS` LIKE 'Microbiologie' AND `SCALE_TYP` LIKE 'Nominal' AND `TIME_ASPCT` LIKE 'Temps ponctuel';

return:
nombre 96

... but not with Node-Red

This is the code:

var value1 = msg.payload[0].SYSTEM; 
var value2 = msg.payload[1].CLASS; 
var value3 = msg.payload[2].SCALE_TYP; 
var value4 = msg.payload[3].TIME_ASPCT; 
msg.topic = 'SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE "' + value1 + '" AND `CLASS` LIKE "' + value2 + '"AND `SCALE_TYP` LIKE "' + value3 + '"AND `TIME_ASPCT` LIKE "' + value4 + '"; '; 
return msg;

Returned message is an Array[4] and not a number of records !
Thanks for your help !
Phil.

It looks like you are constructing a SQL query dynamically in Node-RED and trying to count the number of records based on certain conditions. The issue you're facing might be related to the way you're constructing the SQL query or processing the result in Node-RED.

Here's a revised version of your Node-RED function:
// Extract values from the incoming payload
var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;

// Construct the SQL query
var sqlQuery = 'SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';

// Set the topic to the SQL query for reference
msg.topic = sqlQuery;

// Return the modified message object
return msg;

[PhilLu1]

Thank you isabelletipa
This return in debug:

17/11/2023 11:39:38node: debug 40
SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

... but nothing about the COUNT
... and the UI_text is empty...

[Colin]

What happens if you remove the AS nombre clause?
What happens if you also remove the WHERE clause?

[PhilLu1]

Same...

[Colin]

It can't possibly be the same when you take out the where clause.

[PhilLu1]

With full code:

var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;
var sqlQuery = 'SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';
msg.topic = sqlQuery;
return msg;

debug return:

SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

... without 'AS nombre:

var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;
var sqlQuery = 'SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant WHERE ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';
msg.topic = sqlQuery;
return msg;

debug return:

SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

... without WHERE:

var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;
var sqlQuery = 'SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';
msg.topic = sqlQuery;
return msg;

... debug return:

SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

???

[PhilLu1]

I found the error
A block was not attached to another...
Many thanks to all