如何使用PHP/MySQL从两个独立ID生成数组并提取重复元素数组

Hey there! Let's get your code working as intended—building two separate arrays from rows with ID 1 and ID 2 in your MySQL table, then extracting their duplicate elements into a standalone array. Here's a breakdown of the fixes and improved code:

Issues with Your Current Code #

• You're only querying the row where id=$var1 (ID 1), so $array2 is just filled with repeated values from that single row's a4 field instead of pulling data from ID 2.
• Directly inserting variables into your SQL query creates a SQL injection risk—we'll fix this with prepared statements for safety.

Improved Code #

<?php
include 'connect.php';

// Define the IDs we need
$id1 = 1;
$id2 = 2;

// Reusable function to fetch array from a given ID
function getArrayFromId($conn, $id) {
    $array = [];
    // Use prepared statement to prevent SQL injection
    $stmt = $conn->prepare("SELECT a1, a2, a3, a4 FROM data WHERE id = ?");
    $stmt->bind_param("i", $id); // "i" denotes integer type for the ID
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($row = $result->fetch_assoc()) {
        // Populate array with the field values from the row
        $array = [
            $row['a1'],
            $row['a2'],
            $row['a3'],
            $row['a4']
        ];
    }
    $stmt->close();
    return $array;
}

// Fetch the two target arrays
$array1 = getArrayFromId($conn, $id1);
$array2 = getArrayFromId($conn, $id2);

// Check if both arrays have valid data before proceeding
if (!empty($array1) && !empty($array2)) {
    // Find duplicate elements (intersection of the two arrays)
    $commonElements = array_intersect($array1, $array2);
    
    // Output results with clear labels
    echo "Array 1: " . implode(" ", $array1) . "<br>";
    echo "Array 2: " . implode(" ", $array2) . "<br>";
    echo "Common Elements: " . implode(" ", $commonElements);
} else {
    echo "One or both ID records were not found in the database.";
}

$conn->close();
?>

Key Improvements Explained #

• Prepared Statements: We use prepare() and bind_param() to safely pass ID values into the query, eliminating SQL injection vulnerabilities that come with directly embedding variables in SQL.
• Reusable Function: The getArrayFromId() function cuts down on repetitive code, making it easy to fetch data for any ID without rewriting the query logic.
• Better Error Handling: We check if both arrays are populated before calculating their intersection, so you get a clear message if a record is missing.
• Readable Output: Added labels to the echoed results so you can easily tell which output corresponds to which array.

内容的提问来源于stack exchange，提问作者Luciano F Castelfranchi