如何用PHP和MySQL输入字段提交表行数据至另一表？代码排查

Hey there! Let's walk through how to copy rows from one MySQL table to another using PHP and HTML input fields, and troubleshoot the common pitfalls that might be tripping up your code.

First, let's make sure we're on the same page about the process:

1. Create an HTML form to capture the identifier of the row you want to copy (usually the primary key like id).
2. Use PHP to handle the form submission:
   • Connect to your MySQL database securely.
   • Validate and sanitize the input from the form.
   • Fetch the targeted row from the source table.
   • Insert that row's data into the target table.
   • Provide feedback to the user about success or failure.

Here's a simple, functional form that collects the source row ID:

<form method="POST" action="post.php">
    <label for="source_id">Enter ID of the row to copy:</label>
    <input type="number" id="source_id" name="source_id" required>
    <button type="submit">Copy Row</button>
</form>

Key things to check here:

• The method="POST" ensures data is sent securely (never use GET for this kind of operation).
• The action attribute points directly to your post.php file (double-check the file path!).
• The input has a name="source_id" attribute—this is how PHP will access the value via $_POST['source_id'].

This example uses PDO (PHP Data Objects) for secure database interactions (avoid outdated mysql_* functions at all costs!). It includes error handling and input validation:

<?php
// Enable error reporting for development (turn off in production!)
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Database connection credentials (replace with your own)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_username';
$password = 'your_db_password';

try {
    // Initialize PDO connection
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Check if the source ID was submitted and is valid
    if (isset($_POST['source_id']) && is_numeric($_POST['source_id'])) {
        $sourceId = (int)$_POST['source_id'];

        // Step 1: Fetch the row from the source table
        $fetchStmt = $pdo->prepare("SELECT column1, column2, column3 FROM source_table WHERE id = ?");
        $fetchStmt->execute([$sourceId]);
        $sourceRow = $fetchStmt->fetch(PDO::FETCH_ASSOC);

        if ($sourceRow) {
            // Step 2: Insert the data into the target table
            // Match the columns to your actual table structure!
            $insertStmt = $pdo->prepare("
                INSERT INTO target_table (column1, column2, column3)
                VALUES (?, ?, ?)
            ");
            $insertStmt->execute([
                $sourceRow['column1'],
                $sourceRow['column2'],
                $sourceRow['column3']
            ]);

            echo "<p>Success! Row copied to target table.</p>";
        } else {
            echo "<p>Error: No row found with ID $sourceId in the source table.</p>";
        }
    } else {
        echo "<p>Error: Please enter a valid numeric ID.</p>";
    }
} catch(PDOException $e) {
    echo "<p>Database Error: " . $e->getMessage() . "</p>";
}

// Close the connection
$pdo = null;
?>

If your current code isn't working, check for these common mistakes:

• Missing Form Attributes: Did you forget method="POST" or set the wrong action path? Is your input field missing a name attribute? PHP can't access values without a name!
• Unsanitized Input & SQL Injection: Never directly insert $_POST values into SQL queries. Use prepared statements (like the PDO example above) to prevent attacks and syntax errors.
• Database Connection Problems: Double-check your host, username, password, and database name. Make sure your PHP server has the PDO/mysqli extension enabled.
• Column Mismatches: Are the column names and data types in your target table identical (or compatible) with the source table? A mismatch (e.g., trying to insert a string into an INT column) will cause errors.
• Database Permissions: Does your database user have SELECT access to the source table and INSERT access to the target table? Ask your hosting provider or check MySQL user permissions if unsure.
• Disabled Error Reporting: If you're seeing a blank page, enable error reporting (as shown in the PHP example) to see what's going wrong.
• Empty Input: Did you add validation to ensure the user actually enters an ID? The required attribute in HTML helps, but server-side validation (like is_numeric()) is critical.

• First, run a manual SQL query in phpMyAdmin or MySQL CLI to confirm the source row exists and you can insert into the target table.
• Add var_dump($_POST); at the top of post.php to verify the form data is being received correctly.
• For development, print the executed SQL (carefully!) to spot syntax issues.

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