last_id工作原理咨询及多表插入异常排查求助

Hey there! Let's break down your problem step by step, starting with how 571960 works, then troubleshooting your insert issues, and fixing the code.

First, let's clarify the core concept:

• This function returns the most recent auto-generated ID from an INSERT statement in your current database session.
• It's session-specific, so other database connections/queries won't interfere with its value.
• For your use case, after inserting a customer into customer_tbl, you need this ID to link the new customer to their transactions and orders (since those tables likely have a foreign key relationship to customer_no).

Looking at your code snippet, here are the key issues:

1. Incorrect POST array access: $_POST['productsku[$x]'] doesn't work in PHP—you can't parse array indexes inside a quoted string like that.
2. Missing auto-generated ID retrieval: You didn't capture the customer_no after inserting into customer_tbl, so your subsequent inserts can't associate with the new customer (this is almost certainly causing foreign key constraint failures if those fields are required).
3. No error checking: You aren't capturing SQL errors, so you can't see exactly why the inserts are failing (e.g., missing fields, invalid data types).
4. SQL injection risk: Directly concatenating POST values into SQL is extremely unsafe—we'll fix this too.

Let's rewrite your code to address all these issues:

Step 1: Fix the POST Data Handling #

First, correctly access the product SKU and quantity arrays from your form (assuming your form uses name="productsku[]" and name="productqty[]"):

// Safely get POST arrays, default to empty if they don't exist
$product_sku = $_POST['productsku'] ?? [];
$quantity = $_POST['productqty'] ?? [];

// Limit to 5 items as your original loop intended
$product_sku = array_slice($product_sku, 0, 5);
$quantity = array_slice($quantity, 0, 5);

Step 2: Insert Customer & Get Their Auto-Generated ID #

After inserting the customer, immediately fetch their customer_no using mysqli_insert_id():

// Replace $conn with your actual database connection variable
$sql_customer = "INSERT INTO customer_tbl(customer_name, fb_url, mobile_no, email_address, address) 
                 VALUES (?, ?, ?, ?, ?)";

// Use prepared statements to prevent SQL injection
$stmt_customer = mysqli_prepare($conn, $sql_customer);
mysqli_stmt_bind_param($stmt_customer, "sssss", $customer_name, $fb_url, $mobile_no, $email, $address);

if (mysqli_stmt_execute($stmt_customer)) {
    // Get the auto-generated customer_no
    $customer_no = mysqli_insert_id($conn);
    
    // Now proceed to insert transactions and orders
    insert_transactions_and_orders($conn, $customer_no, $product_sku, $quantity);
} else {
    echo "Customer insert failed: " . mysqli_error($conn);
}

Step 3: Insert Transactions & Orders with Linked IDs #

Create a helper function to handle the transaction and order inserts, making sure to link each record with the correct IDs:

function insert_transactions_and_orders($conn, $customer_no, $product_sku, $quantity) {
    // 1. Insert transaction (adjust fields to match your transaction_tbl structure)
    $sql_transaction = "INSERT INTO transaction_tbl(customer_no, transaction_date) 
                        VALUES (?, NOW())";
    
    $stmt_trans = mysqli_prepare($conn, $sql_transaction);
    mysqli_stmt_bind_param($stmt_trans, "i", $customer_no);
    
    if (mysqli_stmt_execute($stmt_trans)) {
        $transaction_no = mysqli_insert_id($conn);
        
        // 2. Insert each order item linked to the transaction
        $sql_order = "INSERT INTO order_tbl(transaction_no, product_sku, quantity) 
                      VALUES (?, ?, ?)";
        $stmt_order = mysqli_prepare($conn, $sql_order);
        
        foreach ($product_sku as $index => $sku) {
            $qty = $quantity[$index] ?? 0;
            // Only insert if we have a valid SKU and quantity
            if (!empty($sku) && $qty > 0) {
                mysqli_stmt_bind_param($stmt_order, "isi", $transaction_no, $sku, $qty);
                if (!mysqli_stmt_execute($stmt_order)) {
                    echo "Failed to insert order for SKU $sku: " . mysqli_error($conn);
                }
            }
        }
        echo "All records inserted successfully!";
    } else {
        echo "Transaction insert failed: " . mysqli_error($conn);
    }
}

• Always use mysqli_insert_id() immediately after an INSERT to capture auto-generated IDs for related tables.
• Use prepared statements instead of direct string concatenation to avoid SQL injection and syntax errors.
• Add error checking with mysqli_error() to debug failed queries quickly.

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