代码之家 › 专栏 › 技术社区 › James Wright

如何使用JSONSTRING拆分mysql列数据

json mysql php

James Wright · 技术社区 · 1 年前

我在MYSQL表中有一个列,其值如下

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

我想使用JSONSTRING分割数据,到目前为止,我有以下代码,但我无法用数据填充$JSONSTRING

// 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'];

在the echo$jsonString; 我得到以下值:

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

我需要上面没有\的值

// The provided JSON string
$jsonString =

我的代码部分

3 回复 | 直到 1 年前

K.D.Dilshan 1 年前

问题在于JSON字符串是如何编码并存储在数据库列中的。
当您检索它时,它可能已经包含转义字符,这可能会使输出看起来不正确。
要解决此问题并确保正确填充$jsonString而不使用不必要的转义符,可以使用以下方法:

固定代码,

// 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.";
}

关键变化

检索 params 直接列:

而不是重新编码JSON数据( json_encode ),直接使用数据库中的字符串。

正确解码JSON:

使用 json_decode 将JSON字符串转换为关联数组。

避免逃逸角色:

这个 \ 字符出现是因为数据库列包含转义的JSON。确保该列存储原始JSON字符串,而不是双重编码的JSON。

例如,

在数据库中更正JSON

{ “acc”:“0”, “报警”:“02”, “batl”:“6”, “蝙蝠”:“1”, “cellid”:“0”, “防御”:“1”, “gpslev”:“15”, “gsmlev”:“3”, “lac”:“0”, “mcc”:“0” }

验证解码:

使用 json_last_error() 如果解码失败,则进行调试。

lemon8de 1 年前

我理解这个请求/问题只是从字面上删除了 $jsonString .

你可以用 str_replace() .

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

检查结果

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

输出仍然是字符串。

Ed Bangga 1 年前

检查字符串输出 json_decode()

$strjson = '"acc":"0","alarm":"02","batl":"6","bats":"1","cellid":"0","defense":"1","gpslev":"15","gsmlev":"3","lac":"0","mcc":"0"';

$jsonString = json_encode($strjson);

echo  json_decode($jsonString);

结果

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