How to Split mysql Column Data using JSONSTRING

December 11, 2024

I have a Column in MYSQL table with the following values

"acc":"0","alarm":"02","batl":"6","bats":"1","cellid":"0","defense":"1","gpslev":"15","gsmlev":"3","lac":"0","mcc":"0"

I want to split the data using JSONSTRING, I have the following code thus far but I cannot populate the $jsonString with the data

// Query: Fetch the required data from MySQL table
$sql = "SELECT params FROM gs_objects WHERE imei = '863281047502101'";
$result = $connection->query($sql);

if ($result->num_rows > 0) {
// Fetch the row as an associative array
$row = $result->fetch_assoc();

// Populate JSON string using fetched data
$jsonData = [
'params' => $row['params'],
];

// Convert associative array to JSON string
$jsonString = json_encode($jsonData);

// Output JSON string
echo "Populated JSON String: " . PHP_EOL;
echo $jsonString;

} else {
echo "No data found for the given query.";
}
// The provided JSON string
$jsonString = ***I need to Populate this with the column Data***

// Decode the JSON string into an associative array
$data = json_decode($jsonString, true);

// Check if decoding was successful
if ($data === null) {
echo "Invalid JSON string.";
exit;
}

// Extract specific values
$acc = $data['acc'];
$alarm = $data['alarm'];                                        
$gpslev = $data['gpslev'];
$bats = $data['bats'];

In the echo $jsonString; I get the following value:

{\"acc\":\"0\",\"alarm\":\"02\",\"batl\":\"6\"...........}

I need the above value without the \ to be placed into

// The provided JSON string
$jsonString =

Section of my code

>Solution :

The issue lies in how the JSON string is encoded and stored in your database column.
When you retrieve it, it may already contain escaped characters, which can make the output look incorrect.
To resolve this and ensure you correctly populate $jsonString without unnecessary escape characters, you can use the following approach:

Fixed code ,

// Query: Fetch the required data from MySQL table
$sql = "SELECT params FROM gs_objects WHERE imei = '863281047502101'";
$result = $connection->query($sql);

if ($result->num_rows > 0) {
    // Fetch the row as an associative array
    $row = $result->fetch_assoc();

    // Retrieve the raw JSON string from the database column
    $jsonString = $row['params'];

    // Decode the JSON string into an associative array
    $data = json_decode($jsonString, true);

    // Check if decoding was successful
    if ($data === null) {
        echo "Invalid JSON string.";
        exit;
    }

    // Extract specific values
    $acc = $data['acc'];
    $alarm = $data['alarm'];
    $gpslev = $data['gpslev'];
    $bats = $data['bats'];

    // Output the extracted values
    echo "Extracted Values: \n";
    echo "ACC: $acc\n";
    echo "Alarm: $alarm\n";
    echo "GPS Level: $gpslev\n";
    echo "Battery Status: $bats\n";
} else {
    echo "No data found for the given query.";
}

Key Changes

Retrieve the params Column Directly:

nstead of re-encoding the JSON data (json_encode), use the string directly from the database.

Decode the JSON Properly:

Use json_decode to convert the JSON string into an associative array.

Avoid Escaped Characters:

The \ characters appear because the database column contains escaped JSON. Ensure the column stores raw JSON strings, not double-encoded JSON.

For example,

Correct JSON in the database

{
"acc":"0",
"alarm":"02",
"batl":"6",
"bats":"1",
"cellid":"0",
"defense":"1",
"gpslev":"15",
"gsmlev":"3",
"lac":"0",
"mcc":"0"
}