PHP调用Stripe API时重复创建客户与订阅问题求助

我在使用Stripe API时，流程是先创建Customer再关联至Subscription，但while循环意外执行两次，导致Stripe后台生成两个重复客户，同时users_subscription数据库表也插入了两条重复数据。

问题截图 #

Stripe后台重复创建的客户 #

users_subscription表重复数据 #

相关代码 #

session_start();
if (!isset($_SESSION["username"])) {
    header("Location: ../login.php");
    exit();
}

require_once('../vendor/autoload.php');

\Stripe\Stripe::setApiKey('XXXXX');
// Get the token from the JS script

// Create connection
include '../db.php';

$sql = "SELECT * FROM users JOIN users_data ON users.id = users_data.member_id WHERE username = '" . $_SESSION["username"] . "'";

$result = $con->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while ($row = $result->fetch_assoc()) {
        $user_username = $row["username"];
        $user_email = $row["email"];
        $first_name = $row["first_name"];
        $last_name = $row["last_name"];
        $date_subscription_creation = date('Y-m-d H:i:s');

        try {
            
            $token = $_POST['stripeToken'];

            // Create a Customer
            $customer = \Stripe\Customer::create(array(
                "name" => $first_name . ' ' . $last_name,
                "email" => $user_email,
                "source" => $token,
            ));

            // Create a Subscription for that Customer
            $Subscription = \Stripe\Subscription::create([
                'customer' => $customer->id,
                'items' => [[
                    'price' => 'price_1MmiSbHB5OMGqmnSedfYDOol',
                ]]
            ]);


            // Add subscription information to database users_subscription
            $sql = "INSERT INTO users_subscription (username, first_name, last_name, email, stripe_subscription_id, stripe_customer_id, stripe_plan_id, created, status)
        VALUES ('$user_username', '$first_name', '$last_name', '$user_email', '', '$customer->id', 'price_1MmiSbHB5OMGqmnSedfYDOol', '$date_subscription_creation', 'active')";

            if ($con->query($sql) === TRUE) {
                // End Stripe things
                print_r($Subscription);
            } else { 
                echo "Error: " . $sql . "<br>" . $con->error;
            }
        } catch (\Stripe\Exception\CardException $e) {
            echo '<div class="container">';
            echo '<div class="card" style="width: 18rem;">
                <div class="card-body">
                <h5 class="card-title">ERROR:</h5>';
            echo '<p class="card-text">';

            // Since it's a decline, \Stripe\Exception\CardException will be caught
            echo 'Status is:' . $e->getHttpStatus() . '\n';
            echo 'Type is:' . $e->getError()->type . '\n';
            echo 'Code is:' . $e->getError()->code . '\n';
            // param is '' in this case
            echo 'Param is:' . $e->getError()->param . '\n';
            echo '<br><br>';
            echo 'Message is: ' . $e->getError()->message . '\n';
            
            echo '</p>
          
            </div>
            </div>
            </div>';
        } catch (\Stripe\Exception\RateLimitException $e) {
            // Too many requests made to the API too quickly
        } catch (\Stripe\Exception\InvalidRequestException $e) {
            // Invalid parameters were supplied to Stripe's API
        } catch (\Stripe\Exception\AuthenticationException $e) {
            // Authentication with Stripe's API failed
            // (maybe you changed API keys recently)
        } catch (\Stripe\Exception\ApiConnectionException $e) {
            // Network communication with Stripe failed
        } catch (\Stripe\Exception\ApiErrorException $e) {
            // Display a very generic error to the user, and maybe send
            // yourself an email
        } catch (Exception $e) {
            // Something else happened, completely unrelated to Stripe
        }

    }
} else {
    echo "0 results";
}

$con->close();

问题原因与解决方案 #

核心原因 #

你的SELECT查询关联了users和users_data表，但当前用户在users_data表中有两条匹配记录，导致查询结果返回2行数据，while循环因此执行两次，每次循环都会调用Stripe API创建客户并插入数据库。

解决步骤 #

1. 验证数据重复情况：执行以下SQL查询，确认当前用户在users_data表中的记录数：

SELECT * FROM users_data WHERE member_id = (SELECT id FROM users WHERE username = '你的测试用户名');

如果返回多条记录，需要清理重复数据，或给users_data.member_id添加唯一约束避免后续重复插入。

2. 修改代码逻辑：

   • 如果业务要求每个用户在users_data中只能有一条记录，直接取单条数据并移除while循环：

     if ($result->num_rows > 0) {
         $row = $result->fetch_assoc(); // 直接获取第一条匹配数据
         $user_username = $row["username"];
         $user_email = $row["email"];
         $first_name = $row["first_name"];
         $last_name = $row["last_name"];
         $date_subscription_creation = date('Y-m-d H:i:s');
     
         // 后续Stripe创建、数据库插入逻辑保持不变，移出while循环
     }
     

   • 如果业务允许users_data有多条记录，但订阅操作只需执行一次，在循环内添加终止逻辑：

     while ($row = $result->fetch_assoc()) {
         // 处理逻辑...
         break; // 仅执行一次循环
     }
     

3. 额外优化建议：

   • 修复SQL注入风险：当前查询直接拼接$_SESSION["username"]，改用预处理语句：

     $sql = "SELECT * FROM users JOIN users_data ON users.id = users_data.member_id WHERE username = ?";
     $stmt = $con->prepare($sql);
     $stmt->bind_param("s", $_SESSION["username"]);
     $stmt->execute();
     $result = $stmt->get_result();
     

   • 完善订阅记录存储：创建Subscription后，将$Subscription->id存入stripe_subscription_id字段，当前代码该字段为空，后续无法关联订阅记录。

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