在PHP中将JSON数组高效存储到MySQL数据库的教程

{
  "us_id":"1",
  "stu_id":"6",
  "class_id":"3",
  "req_x":[
    { "u_id":"1", "u_details":"testing user", "charges":"12.50"},
    { "u_id":"2", "u_details":"testing user 2", "charges":"10.50" },
    { "u_id":"3", "u_details":"testing user 3", "charges":"9.50" }
  ]
}

<?php

// 原始的完整JSON字符串
$fullJsonString = '{
    "us_id":"1",
    "stu_id":"6",
    "class_id":"3",
    "req_x":[
        { "u_id":"1", "u_details":"testing user", "charges":"12.50"},
        { "u_id":"2", "u_details":"testing user 2", "charges":"10.50" },
        { "u_id":"3", "u_details":"testing user 3", "charges":"9.50" }
    ]
}';

// 1. 解析完整的JSON字符串为PHP对象
// 第二个参数为true时，会解析为关联数组；默认为false，解析为对象
$dataObject = json_decode($fullJsonString);

// 检查解析是否成功
if (json_last_error() !== JSON_ERROR_NONE) {
    die("JSON解析错误: " . json_last_error_msg());
}

// 2. 访问目标JSON数组（req_x）
// 如果解析为对象，使用箭头操作符访问属性
$req_x_array = $dataObject->req_x;

// 3. 将提取出的PHP数组重新编码为JSON字符串
$json_req_x_to_save = json_encode($req_x_array);

// 输出结果，验证其是否为期望的JSON字符串
echo "待存储的JSON数组字符串:\n";
echo $json_req_x_to_save;

// 预期输出:
// [
//     {"u_id":"1","u_details":"testing user","charges":"12.50"},
//     {"u_id":"2","u_details":"testing user 2","charges":"10.50"},
//     {"u_id":"3","u_details":"testing user 3","charges":"9.50"}
// ]

// 4. 将 $json_req_x_to_save 存储到MySQL数据库
// 以下是概念性代码，实际应用中请使用PDO或MySQLi预处理语句
/*
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = 'password';
$dbName = 'your_database';

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("INSERT INTO your_table (req_x_column) VALUES (:req_x_data)");
    $stmt->bindParam(':req_x_data', $json_req_x_to_save, PDO::PARAM_STR);
    $stmt->execute();

    echo "\n数据已成功存储到数据库。";
} catch (PDOException $e) {
    die("\n数据库操作失败: " . $e->getMessage());
}
*/
?>

<?php
// 假设从数据库中检索到以下JSON字符串
$retrievedJsonString = '[
    {"u_id":"1","u_details":"testing user","charges":"12.50"},
    {"u_id":"2","u_details":"testing user 2","charges":"10.50"},
    {"u_id":"3","u_details":"testing user 3","charges":"9.50"}
]';

// 将JSON字符串解码为PHP数组
$retrievedArray = json_decode($retrievedJsonString, true); // true表示解码为关联数组

if (json_last_error() !== JSON_ERROR_NONE) {
    die("JSON解码错误: " . json_last_error_msg());
}

echo "\n从数据库检索并解码后的PHP数组:\n";
print_r($retrievedArray);

// 现在你可以像操作任何PHP数组一样操作 $retrievedArray
foreach ($retrievedArray as $item) {
    echo "用户ID: " . $item['u_id'] . ", 详情: " . $item['u_details'] . ", 费用: " . $item['charges'] . "\n";
}
?>