PHP MySQL六级佣金分配问题：需给新用户上溯6级活跃成员发佣

Got it, let's fix this problem step by step. The original code is trying to pull referrals downward from the root user, but what you actually need is to trace upward from the new user to their parent, grandparent, etc., up to 6 levels, and only pay commissions to those who are active. Here's how to adjust this:

Key Changes Needed #

• Switch from downward to upward traversal: Start with the new user's sponsorid, then recursively get each upline's sponsor in turn, up to 6 levels.
• Add an active user check: You'll need to define what "active" means (we'll assume your users table has an active boolean field for this example).
• Fix SQL injection risks: The original code uses unescaped string concatenation—we'll replace this with prepared statements for security.
• Stop early if no valid upline exists: Break the loop if we hit a user with no sponsor or an inactive user (optional: you can choose to skip inactive users and keep checking higher levels if needed).

Revised Code #

// Assume these variables are already defined:
// $userid = username of the new user who made the investment
// $invest_to_add = the investment amount triggering commissions
// $rlevels = 6 (your max commission level limit)
// $refpc = array holding commission percentages for levels 1-6 (e.g., $refpc[1] = 5, $refpc[2] = 3, etc.)

// Initialize tracking variables
$currentUpline = $userid;
$currentLevel = 1;

// Traverse upward up to 6 levels
while ($currentLevel <= $rlevels) {
    // Fetch the next upline's data with prepared statement
    $stmt = $conn->prepare("SELECT sponsorid, active FROM users WHERE username = ?");
    $stmt->bind_param("s", $currentUpline);
    $stmt->execute();
    $result = $stmt->get_result();
    $uplineData = $result->fetch_assoc();
    $stmt->close();

    // Check if we found a valid active upline
    if ($uplineData && $uplineData['active'] == 1 && !empty($uplineData['sponsorid'])) {
        $curref = $uplineData['sponsorid'];
        $curcomission = ($invest_to_add / 100) * $refpc[$currentLevel];
        
        // Update the upline's earned commission
        $updateStmt = $conn->prepare("UPDATE users SET earned = earned + ? WHERE username = ?");
        $updateStmt->bind_param("ds", $curcomission, $curref);
        $updateStmt->execute();
        $updateStmt->close();
        
        // Log the commission transaction
        $ma = date("Y-m-d");
        $reason = "Ref. comission from $userid";
        $insertStmt = $conn->prepare("INSERT INTO credits(s_date, s_reason, s_amount, s_user, s_type) VALUES(?, ?, ?, ?, 1)");
        $insertStmt->bind_param("ssds", $ma, $reason, $curcomission, $curref);
        $insertStmt->execute();
        $insertStmt->close();
        
        // Move to the next higher upline
        $currentUpline = $curref;
        $currentLevel++;
    } else {
        // No valid active upline found—break the loop
        // If you want to skip inactive users and keep checking higher levels, replace 'break' with:
        // $currentUpline = $uplineData['sponsorid'] ?? null;
        break;
    }
}

Important Notes #

1. Active User Definition: Adjust the active check to match your actual criteria (e.g., maybe last_login within 30 days, or a custom status field).
2. SQL Injection Protection: The original code was vulnerable to attacks—using prepared statements fixes this critical security issue.
3. Flexible Traversal: If you want to skip inactive uplines and continue checking higher levels (e.g., level 2 is inactive but level 3 is active), modify the else block to set currentUpline = $uplineData['sponsorid'] (if it exists) instead of breaking the loop.
4. Error Handling: Add additional error checks (e.g., verify execute() returns true) based on your application's error-handling needs.

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