如何将easypay接口返回数组的每个值初始化后存入MySQL表

• 第一步：请求API接口并解析返回的JSON数据

<?php
// easypay支付API接口地址
$url = 'https://www.easypay.co.ug/api/';
// 此处自行补充接口请求的业务逻辑，假设最终获取到的接口返回JSON字符串存放在$responseJson变量中
$responseData = json_decode($responseJson, true);
// 先校验接口请求是否成功
if ($responseData['success'] != 1) {
    die('接口调用失败：'.$responseData['data']);
}

• 第二步：提取返回字段单独赋值

// 提取details层级下的字段
$phone = $responseData['details']['phone'];
$reference = $responseData['details']['reference'];
$telecomId = $responseData['details']['telecomId'];
$transactionId = $responseData['details']['transactionId'];
$amount = $responseData['details']['amount'];
$reason = $responseData['details']['reason'];
$currencyCode = $responseData['details']['currencyCode'];
// 提取根层级字段
$success = $responseData['success'];
$fullResponse = $responseData['data'];

• 第三步：创建MySQL数据表并写入数据
  首先执行SQL创建对应存储表，可根据业务需求调整字段：

CREATE TABLE `easypay_pay_records` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `phone` varchar(20) NOT NULL COMMENT '支付手机号',
  `reference` varchar(64) NOT NULL COMMENT '业务参考号',
  `telecom_id` varchar(64) NOT NULL COMMENT '运营商交易ID',
  `transaction_id` varchar(64) NOT NULL COMMENT 'easypay平台交易ID',
  `amount` decimal(10,2) NOT NULL COMMENT '交易金额',
  `reason` text NOT NULL COMMENT '交易说明',
  `currency_code` varchar(10) NOT NULL COMMENT '货币类型',
  `full_response` text NOT NULL COMMENT '接口完整返回信息',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

再编写PHP数据写入逻辑，使用预处理语句避免SQL注入：

// 替换为自身数据库连接信息
$dbHost = '127.0.0.1';
$dbName = '你的数据库名';
$dbUser = '你的数据库账号';
$dbPass = '你的数据库密码';

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $insertSql = "INSERT INTO easypay_pay_records (phone, reference, telecom_id, transaction_id, amount, reason, currency_code, full_response) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    $stmt = $pdo->prepare($insertSql);
    $stmt->execute([
        $phone,
        $reference,
        $telecomId,
        $transactionId,
        $amount,
        $reason,
        $currencyCode,
        $fullResponse
    ]);
    echo "数据存储成功，记录ID为：".$pdo->lastInsertId();
} catch(PDOException $e) {
    die("数据存储失败：".$e->getMessage());
}
?>

内容的提问来源于stack exchange，提问作者Rosoldier