String to json array

General
1

hi , I need help again at node red , I have this string at msg.payload (receive from data base as json)

"{"id":"2","sensor":"DHT11","value1":"26.20","location":"Office"}{"id":"3","sensor":"DHT11","value1":"26.10","location":"Office"}{"id":"4","sensor":"DHT11","value1":"25.60","location":"Office"}{"id":"5","sensor":"DHT11","value1":"25.50","location":"Office"}{"id":"6","sensor":"DHT11","value1":"25.50","location":"Office"}{"id":"7","sensor":"DHT11","value1":"25.40","location":"Office"}{"id":"8","sensor":"DHT11","value1":"25.40","location":"Office"}{"id":"9","sensor":"DHT11","value1":"25.40","location":"Office"}{"id":"10","sensor":"DHT11","value1":"25.30","location":"Office"}{"id":"11","sensor":"DHT11","value1":"25.20","location":"Office"}{"id":"12","sensor":"DHT11","value1":"25.30","location":"Office"}{"id":"13","sensor":"DHT11","value1":"25.10","location":"Office"}{"id":"14","sensor":"DHT11","value1":"25.10","location":"Office"}{"id":"15","sensor":"DHT11","value1":"25.10","location":"Office"}{"id":"16","sensor":"DHT11","value1":"25.10","location":"Office"}"

now i want show them at dashboard with table , for that i tried to parse them to jason format
but i get : SyntaxError: Unexpected token { in JSON at position 64 node red
php code for fetch is

 $sql = mysqli_query($conn , "SELECT `id`,`sensor`, `value1`,`location` FROM `sensor_data`  WHERE `location`='Office'  ");  
        
                 while($data = mysqli_fetch_row($sql)){ 

                    $id = $data[0];
                    $sensor = $data[1];
                    $value1 = $data[2];
                    $location= $data[3];
                    

                    $data = array('id' => $id, 'sensor' => $sensor, 'value1' => $value1 ,'location' => $location );    
                    $json = json_encode( $data );
                    echo $json;    

           }
2

Your JSON string is not correct:
What you get is an array of objects, so it must be enclosed by square brackets ( [ ... ] ) and separated from each other with comma signs:

"[{"id":"2","sensor":"DHT11","value1":"26.20","location":"Office"},{"id":"3","sensor":"DHT11","value1":"26.10","location":"Office"},{"id":"4","sensor":"DHT11","value1":"25.60","location":"Office"},{"id":"5","sensor":"DHT11","value1":"25.50","location":"Office"},{"id":"6","sensor":"DHT11","value1":"25.50","location":"Office"},{"id":"7","sensor":"DHT11","value1":"25.40","location":"Office"},{"id":"8","sensor":"DHT11","value1":"25.40","location":"Office"},{"id":"9","sensor":"DHT11","value1":"25.40","location":"Office"},{"id":"10","sensor":"DHT11","value1":"25.30","location":"Office"},{"id":"11","sensor":"DHT11","value1":"25.20","location":"Office"},{"id":"12","sensor":"DHT11","value1":"25.30","location":"Office"},{"id":"13","sensor":"DHT11","value1":"25.10","location":"Office"},{"id":"14","sensor":"DHT11","value1":"25.10","location":"Office"},{"id":"15","sensor":"DHT11","value1":"25.10","location":"Office"},{"id":"16","sensor":"DHT11","value1":"25.10","location":"Office"}]"
1 Like
3

thank you , but how I can change it to that format ?

4

Is the string you posted exactly what you get from the database?

5

yes , it is the exact string

6

Aahh, I see...
The problem is in your PHP code:
You echo the single lines from your query instead of the whole result at once:

Try this (hope that is correct, I didn't write PHP code for many years :smiley: ):

$sql = mysqli_query($conn , "SELECT `id`,`sensor`, `value1`,`location` FROM `sensor_data`  WHERE `location`='Office'  ");  
        
                 $result = array();

                 while($data = mysqli_fetch_row($sql)){ 

                    $id = $data[0];
                    $sensor = $data[1];
                    $value1 = $data[2];
                    $location= $data[3];
                    

                    $data = array('id' => $id, 'sensor' => $sensor, 'value1' => $value1 ,'location' => $location );    
                    $json = json_encode( $data );
                    $result[] = $json;
                    

           }
           echo $result;
7

thank you a lot , but it send me this :

msg.payload : string[5]

"Array"
8 
echo json_encode($result);
9

Sorry, more errors in my code...

$data = array('id' => $id, 'sensor' => $sensor, 'value1' => $value1 ,'location' => $location );    
//                    $json = json_encode( $data );
//                    $result[] = $json;
                    $result[] = $data;
10

now i can see table at dashboard , thank you alot

11

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