You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何用PHP将Shopify订单Webhook数据存入MySQL？报错求助

avatar
云原生阿岚
2026-8-13
Shopify订单Webhook存MySQL报错排查与修复建议

我是Webhook新手，希望通过Shopify订单Webhook将订单数据存储到MySQL数据库。参考Stack Overflow方案编写PHP代码后出现报错，不清楚原因及解决方法，寻求建议。

已执行步骤

  • 在Shopify后台创建Webhook
  • 将Webhook指向自有域名
  • 在目标链接中编写了如下PHP代码：
<?php
$webhook_content = NULL;

// Get webhook content from the POST
$webhook = fopen('php://input' , 'rb');
while (!feof($webhook)) {
    $webhook_content .= fread($webhook, 4096);
}

fclose($webhook);

// Decode Shopify POST
$webhook_content = json_decode($webhook_content, TRUE);

$servername = "localhost";
$database = "xxxxxxxx_xxxhalis"; 
$username = "xxxxxxxx_xxxhalis";
$password = "***********";
$sql = "mysql:host=$servername;dbname=$database;";

// Create a new connection to the MySQL database using PDO, $my_Db_Connection is an object
try { 
    $db = new PDO($sql, $username, $password);
    //echo "<p> DB Connect = Success.</p>";
} catch (PDOException $error) {
    echo 'Connection error: ' . $error->getMessage();
}
$order_num= $webhook_content['name'];
$order_date = $webhook_content['created_at'];
$order_mode = "Online";
$location= $webhook_content['default_address']['province'];
$cust_name = $webhook_content['billing_address']['name'];
$address = $webhook_content['default_address']['address1']['address2']['city']['province']['zip'];
$phone_num = $webhook_content['default_address']['phone'];

$special_note= $webhook_content['note'];
$total_mrp = $webhook_content['current_subtotal_price'];
$total_discount= $webhook_content['current_total_discounts'];
$sub_total = $webhook_content['current_subtotal_price'];
$delivery_charges = $webhook_content['presentment_money']['amount'];

$totalOrderValues= $webhook_content['total_price'];
$discount_approval = "NA";
$invoice_status= "NA";
$punching_status = "NA";
$order_source = "Shopify";

$payment_mode= $webhook_content['payment_gateway_names'];
$payement_status = "Done";
$payement_recieve_date= $webhook_content['processed_at'];
$reference_number = $webhook_content['reference'];
$cash_handover_status = "NA";

$my_Insert_Statement = $my_Db_Connection->prepare("INSERT INTO `customer_order_sab`( `order_num`, `order_datetime`, `modeOfOrder`, `location`, `customer_name`, `address`, `phone_number`, `special_note`, `total_mrp`, `total_discount`, `sub_total`, `delivery_charges`, `totalOrderValues`, `discount_approval`, `invoice_status`, `punching_status`, `order_source`, `payment_mode`, `payement_status`, `payement_recieve_date`, `reference_number`, `cash_handover_status`) VALUES (:order_num, :order_date, :order_mode, :location, :cust_name, :address, :phone_num, :special_note, :total_mrp, :total_discount, :sub_total, :delivery_charges, :totalOrderValues, :discount_approval, :invoice_status, :punching_status, :order_source, :payment_mode, :payement_status, :payement_recieve_date, :reference_number, :cash_handover_status)");

$my_Insert_Statement->bindParam(':order_num', $order_num);
$my_Insert_Statement->bindParam(':order_date', $order_date);
$my_Insert_Statement->bindParam(':order_mode', $order_mode);
$my_Insert_Statement->bindParam(':location', $location);
$my_Insert_Statement->bindParam(':cust_name', $cust_name);
$my_Insert_Statement->bindParam(':address', $address);
$my_Insert_Statement->bindParam(':phone_num', $phone_num);
$my_Insert_Statement->bindParam(':special_note', $special_note);
$my_Insert_Statement->bindParam(':total_mrp', $total_mrp);
$my_Insert_Statement->bindParam(':total_discount', $total_discount);
$my_Insert_Statement->bindParam(':sub_total', $sub_total);
$my_Insert_Statement->bindParam(':delivery_charges', $delivery_charges);
$my_Insert_Statement->bindParam(':totalOrderValues', $totalOrderValues);
$my_Insert_Statement->bindParam(':discount_approval', $discount_approval);
$my_Insert_Statement->bindParam(':invoice_status', $invoice_status);
$my_Insert_Statement->bindParam(':punching_status', $punching_status);
$my_Insert_Statement->bindParam(':order_source', $order_source);
$my_Insert_Statement->bindParam(':payment_mode', $payment_mode);
$my_Insert_Statement->bindParam(':payement_status', $payement_status);
$my_Insert_Statement->bindParam(':payement_recieve_date', $payement_recieve_date);
$my_Insert_Statement->bindParam(':reference_number', $reference_number);
$my_Insert_Statement->bindParam(':cash_handover_status', $cash_handover_status);
if ($my_Insert_Statement->execute()) {
    echo "New record created successfully";
} else {
    echo "Unable to create record";
}
?>

报错原因与修复方案

1. 数据库连接变量名不匹配

代码中用$db创建PDO连接，但后续调用prepare时用了未定义的$my_Db_Connection，这是核心错误。
修复：将$my_Insert_Statement = $my_Db_Connection->prepare(...)改为$my_Insert_Statement = $db->prepare(...)

2. 地址字段拼接错误

$address = $webhook_content['default_address']['address1']['address2']['city']['province']['zip']; 是错误的层级访问，这些字段都是default_address下的同级键，需要拼接字符串：
修复：

$address = isset($webhook_content['default_address']) ? 
    trim($webhook_content['default_address']['address1'] . ' ' . 
    ($webhook_content['default_address']['address2'] ?? '') . ' ' . 
    $webhook_content['default_address']['city'] . ' ' . 
    $webhook_content['default_address']['province'] . ' ' . 
    $webhook_content['default_address']['zip']) : '';

3. 字段访问错误与类型问题

  • presentment_money不是订单的直接字段，配送费应取total_shipping_price：
    $delivery_charges = $webhook_content['total_shipping_price'] ?? '0.00';
  • payment_gateway_names是数组，直接存入数据库会报错，需转为字符串：
    $payment_mode = isset($webhook_content['payment_gateway_names']) ? implode(', ', $webhook_content['payment_gateway_names']) : 'Unknown';
  • 所有字段添加非空判断，避免Undefined index错误，比如：
    $order_num = $webhook_content['name'] ?? '';
$order_date = $webhook_content['created_at'] ?? '';

4. 增加错误调试信息

执行失败时输出具体错误，方便排查：
修复：

if ($my_Insert_Statement->execute()) {
    echo "New record created successfully";
} else {
    echo "Unable to create record. Error: ";
    print_r($my_Insert_Statement->errorInfo());
}

5. 添加Webhook签名验证

缺少Shopify Webhook签名验证，容易被伪造请求。添加验证步骤（替换SHOPIFY_WEBHOOK_SECRET为你的Shopify后台密钥）：

$hmac_header = $_SERVER['HTTP_X_SHOPIFY_HMAC_SHA256'] ?? '';
$secret = 'SHOPIFY_WEBHOOK_SECRET';
$calculated_hmac = base64_encode(hash_hmac('sha256', $webhook_content, $secret, true));

if (!hash_equals($hmac_header, $calculated_hmac)) {
    http_response_code(403);
    die('Invalid webhook signature');
}

修复后的完整代码示例 

<?php
$webhook_content = NULL;

// Get webhook content from the POST
$webhook = fopen('php://input' , 'rb');
while (!feof($webhook)) {
    $webhook_content .= fread($webhook, 4096);
}
fclose($webhook);

// Shopify Webhook签名验证
$hmac_header = $_SERVER['HTTP_X_SHOPIFY_HMAC_SHA256'] ?? '';
$secret = 'SHOPIFY_WEBHOOK_SECRET'; // 替换为你的Webhook密钥
$calculated_hmac = base64_encode(hash_hmac('sha256', $webhook_content, $secret, true));

if (!hash_equals($hmac_header, $calculated_hmac)) {
    http_response_code(403);
    die('Invalid webhook signature');
}

// Decode Shopify POST
$webhook_content = json_decode($webhook_content, TRUE);

$servername = "localhost";
$database = "xxxxxxxx_xxxhalis"; 
$username = "xxxxxxxx_xxxhalis";
$password = "***********";
$sql = "mysql:host=$servername;dbname=$database;charset=utf8mb4;";

// Create PDO connection
try { 
    $db = new PDO($sql, $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $error) {
    echo 'Connection error: ' . $error->getMessage();
    exit;
}

// 提取订单数据，添加非空判断
$order_num = $webhook_content['name'] ?? '';
$order_date = $webhook_content['created_at'] ?? '';
$order_mode = "Online";
$location = $webhook_content['default_address']['province'] ?? '';
$cust_name = $webhook_content['billing_address']['name'] ?? '';
// 正确拼接地址
$address = isset($webhook_content['default_address']) ? 
    trim($webhook_content['default_address']['address1'] . ' ' . 
    ($webhook_content['default_address']['address2'] ?? '') . ' ' . 
    $webhook_content['default_address']['city'] . ' ' . 
    $webhook_content['default_address']['province'] . ' ' . 
    $webhook_content['default_address']['zip']) : '';
$phone_num = $webhook_content['default_address']['phone'] ?? '';

$special_note = $webhook_content['note'] ?? '';
$total_mrp = $webhook_content['current_subtotal_price'] ?? '0.00';
$total_discount = $webhook_content['current_total_discounts'] ?? '0.00';
$sub_total = $webhook_content['current_subtotal_price'] ?? '0.00';
$delivery_charges = $webhook_content['total_shipping_price'] ?? '0.00';

$totalOrderValues = $webhook_content['total_price'] ?? '0.00';
$discount_approval = "NA";
$invoice_status = "NA";
$punching_status = "NA";
$order_source = "Shopify";

// 处理支付网关数组
$payment_mode = isset($webhook_content['payment_gateway_names']) ? implode(', ', $webhook_content['payment_gateway_names']) : 'Unknown';
$payement_status = "Done";
$payement_recieve_date = $webhook_content['processed_at'] ?? '';
$reference_number = $webhook_content['reference'] ?? '';
$cash_handover_status = "NA";

try {
    $my_Insert_Statement = $db->prepare("INSERT INTO `customer_order_sab`( 
        `order_num`, `order_datetime`, `modeOfOrder`, `location`, `customer_name`, 
        `address`, `phone_number`, `special_note`, `total_mrp`, `total_discount`, 
        `sub_total`, `delivery_charges`, `totalOrderValues`, `discount_approval`, 
        `invoice_status`, `punching_status`, `order_source`, `payment_mode`, 
        `payement_status`, `payement_recieve_date`, `reference_number`, `cash_handover_status`
    ) VALUES (
        :order_num, :order_date, :order_mode, :location, :cust_name, 
        :address, :phone_num, :special_note, :total_mrp, :total_discount, 
        :sub_total, :delivery_charges, :totalOrderValues, :discount_approval, 
        :invoice_status, :punching_status, :order_source, :payment_mode, 
        :payement_status, :payement_recieve_date, :reference_number, :cash_handover_status
    )");

    $my_Insert_Statement->bindParam(':order_num', $order_num);
    $my_Insert_Statement->bindParam(':order_date', $order_date);
    $my_Insert_Statement->bindParam(':order_mode', $order_mode);
    $my_Insert_Statement->bindParam(':location', $location);
    $my_Insert_Statement->bindParam(':cust_name', $cust_name);
    $my_Insert_Statement->bindParam(':address', $address);
    $my_Insert_Statement->bindParam(':phone_num', $phone_num);
    $my_Insert_Statement->bindParam(':special_note', $special_note);
    $my_Insert_Statement->bindParam(':total_mrp', $total_mrp);
    $my_Insert_Statement->bindParam(':total_discount', $total_discount);
    $my_Insert_Statement->bindParam(':sub_total', $sub_total);
    $my_Insert_Statement->bindParam(':delivery_charges', $delivery_charges);
    $my_Insert_Statement->bindParam(':totalOrderValues', $totalOrderValues);
    $my_Insert_Statement->bindParam(':discount_approval', $discount_approval);
    $my_Insert_Statement->bindParam(':invoice_status', $invoice_status);
    $my_Insert_Statement->bindParam(':punching_status', $punching_status);
    $my_Insert_Statement->bindParam(':order_source', $order_source);
    $my_Insert_Statement->bindParam(':payment_mode', $payment_mode);
    $my_Insert_Statement->bindParam(':payement_status', $payement_status);
    $my_Insert_Statement->bindParam(':payement_recieve_date', $payement_recieve_date);
    $my_Insert_Statement->bindParam(':reference_number', $reference_number);
    $my_Insert_Statement->bindParam(':cash_handover_status', $cash_handover_status);

    if ($my_Insert_Statement->execute()) {
        echo "New record created successfully";
        http_response_code(200);
    } else {
        echo "Unable to create record. Error: ";
        print_r($my_Insert_Statement->errorInfo());
        http_response_code(500);
    }
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
    http_response_code(500);
}
?>

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.08.13 07:50:26