如何编写关联三张表的MySQLi更新查询语句？

Got it, let's walk through how to solve this. To update records in webpage_table based on a specific user_email, you need to link all three tables together since the email lives only in user_table, and the user-webpage relationship is stored in user_data_table.

Step 1: First, Set Up Your MySQLi Connection #

Before running any queries, make sure you have a valid connection to your database:

// Replace with your actual database credentials
$conn = mysqli_connect("localhost", "your_username", "your_password", "your_database");

// Check if the connection failed
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

Step 2: Use Parameterized Prepared Statements (Recommended) #

This is the safest approach to avoid SQL injection attacks—even if you're using a hardcoded email, it's a best practice to stick with this method. Here's how to structure it:

$targetEmail = "johndoe@xyz.com";
// Define the new values you want to set in webpage_table
$newFirstPage = "https://updated-first-page.com";
$newSecondPage = "https://updated-second-page.com";

// The UPDATE query with JOINs to link all three tables
$sql = "UPDATE webpage_table wp
        JOIN user_data_table ud ON wp.webpage_id = ud.webpage_id
        JOIN user_table u ON ud.user_id = u.user_id
        SET wp.first_webpage = ?, wp.second_webpage = ?
        WHERE u.user_email = ?";

// Prepare the statement
$stmt = mysqli_prepare($conn, $sql);

// Bind parameters: "sss" means 3 string values (adjust if your fields use other data types)
mysqli_stmt_bind_param($stmt, "sss", $newFirstPage, $newSecondPage, $targetEmail);

// Execute the update
mysqli_stmt_execute($stmt);

// Check if any rows were actually updated
if (mysqli_stmt_affected_rows($stmt) > 0) {
    echo "Success! Webpage records updated for the target user.";
} else {
    echo "No records updated. Either the email doesn't exist, or no changes were needed.";
}

// Clean up resources
mysqli_stmt_close($stmt);
mysqli_close($conn);

Alternative: Using mysqli_query Directly (Not Recommended) #

If you absolutely need to use mysqli_query directly (this is riskier for dynamic user input), you must escape the email to reduce injection risks:

$targetEmail = "johndoe@xyz.com";
$newFirstPage = "https://updated-first-page.com";
$newSecondPage = "https://updated-second-page.com";

// Escape the email to mitigate injection risks
$escapedEmail = mysqli_real_escape_string($conn, $targetEmail);

// Construct the UPDATE query
$sql = "UPDATE webpage_table wp
        JOIN user_data_table ud ON wp.webpage_id = ud.webpage_id
        JOIN user_table u ON ud.user_id = u.user_id
        SET wp.first_webpage = '$newFirstPage', wp.second_webpage = '$newSecondPage'
        WHERE u.user_email = '$escapedEmail'";

// Run the query and check results
if (mysqli_query($conn, $sql)) {
    if (mysqli_affected_rows($conn) > 0) {
        echo "Webpage data updated successfully!";
    } else {
        echo "No records matched the email or no changes were made.";
    }
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);

Key Notes #

• Why JOINs? We use JOIN to traverse from user_table (where we have the email) to user_data_table (the bridge table) to webpage_table (the table we need to update).
• Always Prefer Prepared Statements: Even if your email is hardcoded, using parameterized queries prevents future security issues if you ever make the email a dynamic user input.
• Check Affected Rows: mysqli_query returns true even if no rows were updated (as long as the SQL is valid), so checking affected_rows tells you if any actual changes were made.

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