PHP遍历数据库数组匹配ID并更新用户积分的技术问题

Hey there! Let's get this points update sorted out for you. Your current code does a good job finding the matching user ID, but we can streamline things and handle the update securely using WordPress's built-in database functions.

First: Simplify Finding the Matching User #

Instead of fetching every user from the table and looping through them, we can directly query only the user you need with a WHERE clause. This is way more efficient, especially as your user list grows.

Then: Access and Update the points Field #

WordPress's $wpdb class has safe, built-in methods for updating database rows—no need to write raw SQL (though we can do that too if you prefer). Here's a complete, optimized version of your code:

global $wpdb;

// Safely get the user ID from the URL parameter
$user = isset($_GET['user']) ? sanitize_text_field($_GET['user']) : '';

if (!empty($user)) {
    // Fetch only the target user's current points (we don't need all fields)
    $customer = $wpdb->get_row($wpdb->prepare("SELECT points FROM `wpjw_skmember` WHERE id = %s;", $user));
    
    if ($customer) {
        // Access the current points value
        echo "Current points for user $user: " . $customer->points;
        
        // Define your new points value—this could be a fixed number, or add to existing points
        $new_points = $customer->points + 50; // Example: Add 50 points
        // $new_points = 100; // Or set to a specific value
        
        // Option 1: Use WordPress's $wpdb->update() (recommended for simplicity)
        $update_success = $wpdb->update(
            'wpjw_skmember',          // Database table name
            array('points' => $new_points), // Fields to update
            array('id' => $user),      // WHERE condition (match user ID)
            array('%d'),               // Format for the updated field (points is integer)
            array('%s')                // Format for the WHERE value (ID is string)
        );
        
        // Option 2: Use a prepared UPDATE query (if you prefer writing SQL)
        // $query = $wpdb->prepare("UPDATE `wpjw_skmember` SET points = %d WHERE id = %s;", $new_points, $user);
        // $update_success = $wpdb->query($query);
        
        // Check if the update worked
        if ($update_success !== false) {
            echo "<br>Points updated successfully! New points: $new_points";
        } else {
            echo "<br>No changes made, or the update failed.";
        }
    } else {
        echo "User with ID $user not found.";
    }
} else {
    echo "No user ID provided in the URL.";
}

Key Notes for Security & Best Practices #

• Always sanitize input: We use sanitize_text_field() on the user parameter to clean up any malicious or unexpected input.
• Prevent SQL injection: $wpdb->prepare() ensures all values are properly escaped before being sent to the database—never skip this step!
• Efficiency: Querying only the specific user you need (instead of all users) reduces database load and speeds up your code.

If you want to adjust the logic (like setting points to a specific value instead of adding to them), just modify the $new_points line to fit your needs.

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