Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – How to Split mysql Column Data using Jason String

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

Tags:

2

Answers


  1. 0

    I understood the request/question as to just literally remove the slashes ” on the $jsonString.

    You may do so with str_replace().

    $test = '{"acc":"0","alarm":"02","batl":"6"...........}';
$removed = str_replace('\', '', $test);

    Checking the result

    var_dump($removed);
string(46) "{"acc":"0","alarm":"02","batl":"6"...........}" // the output

    The output is still a string.

    Login or Signup to reply.
  2. 0

    • 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: $accn";
    echo "Alarm: $alarmn";
    echo "GPS Level: $gpslevn";
    echo "Battery Status: $batsn";
} else {
    echo "No data found for the given query.";
}
    • Key Changes
    1. Retrieve the params Column Directly:
    • nstead of re-encoding the JSON data (json_encode), use the string directly from the database.
    1. Decode the JSON Properly:
    • Use json_decode to convert the JSON string into an associative array.
    1. 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"
      }

    1. Validate Decoding:
    • Use json_last_error() to debug if decoding fails.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search