PHP实现向MySQL双表插入键值对数据的问题求助

Hey there! Let's fix this attributes table insertion issue for you. First, let's break down what went wrong with your initial attempt, then walk through the correct ways to insert those key-value pairs.

1. Why your initial attributes insert failed #

Your problematic query had two critical issues:

• key is a reserved word in MySQL: You need to wrap it in backticks (`) to avoid syntax errors—MySQL will otherwise interpret it as a keyword instead of your column name.
• The ON items.SKU = attributes.SKU clause doesn't belong in an INSERT statement. You only need to pass the correct SKU value to link the attribute to the item; no join condition is required here.

2. Correct way to insert a single attribute #

For just the size attribute, the working query would be:

$sql = "INSERT INTO attributes (SKU, `key`, value) VALUES ('$SKU', 'size', '$size')";

The backticks around key are non-negotiable here to avoid MySQL throwing a syntax error.

3. Insert multiple attributes efficiently #

Since you need to add 5 attributes (size, height, width, length, weight), you have two solid options:

Option A: Batch INSERT (single query)

This is more efficient than running 5 separate queries, as it reduces the number of round-trips to the database:

$sql = "INSERT INTO attributes (SKU, `key`, value)
VALUES 
('$SKU', 'size', '$size'),
('$SKU', 'height', '$height'),
('$SKU', 'width', '$width'),
('$SKU', 'length', '$length'),
('$SKU', 'weight', '$weight');";

Execute this single query, and all attributes will be added to the table at once.

Option B: Loop through attributes (cleaner for dynamic data)

If you might add/remove attributes later, looping through an array of key-value pairs is more maintainable:

// Collect form input into an associative array
$itemAttributes = [
    'size' => $_POST['size'],
    'height' => $_POST['height'],
    'width' => $_POST['width'],
    'length' => $_POST['length'],
    'weight' => $_POST['weight']
];

// Loop and insert each attribute
foreach ($itemAttributes as $attrKey => $attrValue) {
    // Skip empty values if you don't want to store blank attributes
    if (!empty($attrValue)) {
        $sql = "INSERT INTO attributes (SKU, `key`, value) VALUES ('$SKU', '$attrKey', '$attrValue')";
        // Execute the query using your existing database connection method
    }
}

4. Critical: Protect against SQL Injection #

Never directly insert user input (like form data) into SQL queries—this leaves you wide open to SQL injection attacks. Instead, use prepared statements (either with mysqli or PDO). Here's how to do it with mysqli:

Step 1: Insert into items table safely

// Assume $conn is your existing mysqli connection
$stmt = $conn->prepare("INSERT INTO items (SKU, name, price) VALUES (?, ?, ?)");
// Bind parameters: "ssd" = string, string, decimal (adjust based on your actual data types)
$stmt->bind_param("ssd", $SKU, $name, $price);
$stmt->execute();
$stmt->close();

Step 2: Insert attributes safely with prepared statements

$stmt = $conn->prepare("INSERT INTO attributes (SKU, `key`, value) VALUES (?, ?, ?)");
// Bind parameters: "ssi" = string, string, integer (since your attribute values are integers)
$stmt->bind_param("ssi", $SKU, $attrKey, $attrValue);

$itemAttributes = [
    'size' => $_POST['size'],
    'height' => $_POST['height'],
    'width' => $_POST['width'],
    'length' => $_POST['length'],
    'weight' => $_POST['weight']
];

foreach ($itemAttributes as $attrKey => $attrValue) {
    if (!empty($attrValue)) {
        $stmt->execute();
    }
}

$stmt->close();
$conn->close();

This method ensures user input is properly escaped, keeping your database secure from malicious attacks.

Final Notes #

• Double-check that the SKU you're inserting into attributes matches exactly the SKU you just inserted into items—this is the foreign key that links the two tables.
• If you're using PDO instead of mysqli, the prepared statement logic is similar—just adjust the syntax to fit PDO's methods.

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