如何根据多变量值更新MySQL的AllVariables字段？求更优实现

Hey there! Let's break this down clearly for you.

First off, your current code isn't feasible for the complete logic you described. Right now, it only handles the case where Variable1 = 1—it completely ignores Variable2 and the scenario where both variables are 1. So it won't update AllVariables correctly in those other cases.

Better, More Efficient Approaches #

There are two clean ways to implement your full logic, and we'll also fix the critical security issue in your original code (more on that in a second).

1. Handle Logic in PHP First, Then Run a Prepared Query

You can first determine what value AllVariables should be using PHP's logical checks, then execute a prepared SQL statement (never concatenate user input directly into SQL—this prevents SQL injection attacks):

// Determine the value to set
$allVariablesValue = 0; // Default if neither is 1
if ($Variable1 == 1 && $Variable2 == 1) {
    $allVariablesValue = 2;
} elseif ($Variable1 == 1 || $Variable2 == 1) {
    $allVariablesValue = 1;
}

// Use prepared statements to avoid SQL injection
// Example with PDO
$pdo = new PDO('mysql:host=your_host;dbname=your_db', 'user', 'pass');
$stmt = $pdo->prepare("UPDATE users set AllVariables = ? WHERE id = ?");
$stmt->execute([$allVariablesValue, $username]);

// Or with mysqli
$mysqli = new mysqli('your_host', 'user', 'pass', 'your_db');
$stmt = $mysqli->prepare("UPDATE users set AllVariables = ? WHERE id = ?");
$stmt->bind_param("ii", $allVariablesValue, $username);
$stmt->execute();

2. Let SQL Handle the Logic Directly

This is even more efficient because you can do all the checks in a single SQL query, cutting down on PHP logic:

// Again, use prepared statements!
// PDO example
$pdo = new PDO('mysql:host=your_host;dbname=your_db', 'user', 'pass');
$stmt = $pdo->prepare("
    UPDATE users 
    set AllVariables = CASE
        WHEN ? = 1 AND ? = 1 THEN 2
        WHEN ? = 1 OR ? = 1 THEN 1
        ELSE AllVariables -- Keep existing value if neither is 1
    END
    WHERE id = ?
");
$stmt->execute([$Variable1, $Variable2, $Variable1, $Variable2, $username]);

// mysqli example
$mysqli = new mysqli('your_host', 'user', 'pass', 'your_db');
$stmt = $mysqli->prepare("
    UPDATE users 
    set AllVariables = CASE
        WHEN ? = 1 AND ? = 1 THEN 2
        WHEN ? = 1 OR ? = 1 THEN 1
        ELSE AllVariables
    END
    WHERE id = ?
");
$stmt->bind_param("iiii", $Variable1, $Variable2, $Variable1, $Variable2, $username);
$stmt->execute();

Critical Security Note #

Your original code uses $username directly in the SQL string: WHERE id = $username. This is a huge SQL injection vulnerability—if an attacker controls the $username value, they can run arbitrary SQL on your database. Always use prepared statements with parameter binding like the examples above to fix this.

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