Ajax动态表单输入数据无法插入MySQL数据库问题求解

云上老周

2026-8-29

动态球员表单提交后数据库写入失败问题排查

业务背景

开发体育数据库系统，共设计2张关联数据表：

team表：存储球队信息，字段为teamId（主键、自增）、teamName、teamCoach、teamCity
player表：存储球员信息，字段为playerId（主键、自增）、playerName、playerSkill、playerPosition、teamId（外键关联team表主键）

原有写入逻辑：先通过球队表单录入球队信息，查询max(teamId)获取新生成的球队ID，跳转到球员表单页面，保证录入的球员关联到对应球队。后续基于Ajax实现了支持动态增删行的球员录入表单，前端控制台可正常捕获数组格式的提交内容，但提交后数据始终无法写入数据库。

现有代码

数据库连接文件（connection.php）

<?php
$username = "root";
$password = "";
$server = 'localhost';
$db = "hockey_db";

$con = mysqli_connect($server, $username, $password, $db);

if ($con) {
?>
    <script>
        var alerted = localStorage.getItem('alerted') || '';
        if (alerted != 'yes') {
            alert("Connection Successful");
            localStorage.setItem('alerted', 'yes');
        }
    </script>
<?php
} else {
    die("Connection Unsuccessful" . mysqli_connect_error());
}
?>

球队录入表单（teamform.php）

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="..\css\styles.css">
    <title>Team Form</title>
</head>
<body>
    <h1>Team Form</h1>
    <div class="myForm">
        <form action="" method="POST">
            <label for="teamName">Team name:</label>
            <input type="text" id="teamName" name="teamName" required>
            <label for="teamCity">City name:</label>
            <input type="text" id="teamCity" name="teamCity" required>
            <label for="teamCoach">Coach name:</label>
            <input type="text" id="teamCoach" name="teamCoach" required>
            <input type="submit" name="Submit" value="Submit">
        </form>
        <br>
        <a href="teamList.php"><button>Team List</button></a>
    </div>
    <script>
        if (window.history.replaceState) {
            window.history.replaceState(null, null, window.location.href);
        }
    </script>
</body>
</html>

<?php
include "connection.php";
if (isset($_POST["Submit"])) {
    $teamName = $_POST["teamName"];
    $teamCity = $_POST["teamCity"];
    $teamCoach = $_POST["teamCoach"];

    $insertQuery = "insert into team(teamName,teamCity,teamCoach) values('$teamName','$teamCity','$teamCoach')";
    $res = mysqli_query($con, $insertQuery) or die("problem inserting data into database");
    if ($res) {
?>
        <script>
            alert("Data Inserted Successfully");
        </script>
    <?php
    $playerFormId="SELECT MAX(teamId) AS teamId FROM team;";
    $playerFormRes=mysqli_query($con,$playerFormId);
    $resId=mysqli_fetch_array($playerFormRes);
    header("location:playerform.php?id={$resId["teamId"]}");
    } else {
    ?>
        <script>
            alert("Data Not Inserted");
        </script>
<?php
    }
}
?>

球员动态表单（playerform.php）

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <title>Player Form</title>
    <style>
        #submit_btn {margin: 10px;}
        table,td {border: none;border-collapse: collapse;padding: 10px;text-align: center;}
        td {border: none;padding: 10px;}
        input {margin-top: 15px;}
    </style>
</head>
<body>
    <h1>Player Form</h1>
    <div class="myForm">
        <form action="" method="POST" id="playerForm">
            <div id="show_item">
                <div class="row">
                    <table class="formtable">
                        <tr>
                            <td>
                                <div><input type="text" id="playerName" name="playerName[]" placeholder="Player name" required></div>
                                <div><input type="text" id="playerSkill" name="playerSkill[]" placeholder="Player skill" required></div>
                                <div><input type="text" id="playerPosition" name="playerPosition[]" placeholder="Player position" required></div>
                            </td>
                            <td><input type="submit" name="Add More" value="Add More" class="add_more_btn"></td>
                        </tr>
                    </table>
                </div>
            </div>
            <input type="submit" name="Submit" value="Submit" id="submit_btn">
        </form>
    </div>
    <script>
        $(document).ready(function() {
            $(".add_more_btn").click(function(e) {
                e.preventDefault();
                $("#show_item").append(`<div class="row_append_items">
                    <table class="formtable">
                        <tr>
                            <td>
                                <div><input type="text" id="playerName" name="playerName[]" placeholder="Player name" required></div>
                                <div><input type="text" id="playerSkill" name="playerSkill[]" placeholder="Player skill" required></div>
                                <div><input type="text" id="playerPosition" name="playerPosition[]" placeholder="Player position" required></div>
                            </td>
                            <td><input type="submit" name="Remove" value="Remove" class="remove_btn"></td>
                        </tr>
                    </table>
                </div>`);
            });
            $(document).on('click', '.remove_btn', function(e) {
                e.preventDefault();
                let row_item = $(this).parent().parent();
                $(row_item).remove();
            });

            $("#playerForm").submit(function(e){
                e.preventDefault();
                $("#submit_btn").val("Submitting...");
                $.ajax({
                    url:"playerformAction.php",
                    method:"POST",
                    data:$(this).serialize(),
                    success:function(response){
                        $("#submit_btn").val("Submit");
                        $("#playerForm")[0].reset();
                        $('.row_append_items').remove();
                        alert("data inserted successfully");
                    }
                });
            });
        });
    </script>
</body>
</html>

球员写入处理接口（playerformAction.php）

<?php
$ids = $_GET["id"];
echo $ids;

$playerFormId = "SELECT MAX(teamId) AS teamId FROM team;";
$playerFormRes = mysqli_query($con, $playerFormId);
$res = mysqli_fetch_array($playerFormRes);

$con = new PDO(' mysql : host = localhost ; dbname = hockey_db ', ' root ', ' ');

foreach ($_POST['playerName'] as $key => $value) {
    $sql = "INSERT INTO player (playerName,playerSkill,playerPosition,teamId) VALUES (:playerName,:playerSkill,:playerPosition,:teamId)";
    $stmt = $con->prepare($sql);
    $stmt->execute([
        'playerName' => $value,
        'playerSkill' => $_POST['playerSkill'][$key],
        'playerPosition' => $_POST['playerPosition'][$key],
        'teamId' => $ids,
    ]);
}
echo "Items Inserted successfully";

故障原因

参数传递断裂：跳转到球员表单时，teamId是拼在页面URL的GET参数里，但Ajax提交表单时，只序列化了表单内的球员字段，既没有把URL里的id拼到提交地址上，也没有在表单里存teamId字段，后端$_GET["id"]取到的是空值，外键关联字段为空导致插入违反约束失败。
数据库连接错误：处理接口里没有引入connection.php文件，直接调用mysqli_query时$con变量不存在；后续新建PDO连接的DSN字符串带大量多余空格，格式完全不符合规范，数据库连接直接失败。
响应头冲突：球队表单插入成功后，先输出了弹窗的JS代码，再调用header()做跳转，会触发headers already sent报错，可能导致跳转失败、teamId传参异常。
调试逻辑缺失：Ajax请求只写了成功回调，没有配置错误回调，后端抛出的致命错误前端完全无感知，无法快速定位问题。
SQL注入风险：球队插入逻辑直接拼接用户输入到SQL语句，没有做转义或预处理，存在注入漏洞。

修复方案

球员表单内增加隐藏域存储teamId，提交时自动随表单传给后端：
在playerform.php的form标签内加入：
```
<input type="hidden" name="teamId" value="<?php echo intval($_GET['id']); ?>">
```

重写playerformAction.php逻辑，修正数据库连接，从POST参数取teamId，开启错误提示：

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
// 统一PDO连接，修正DSN格式
$con = new PDO('mysql:host=localhost;dbname=hockey_db', 'root', '');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$teamId = intval($_POST['teamId']);
if (!$teamId) {
    die("无效的球队ID");
}

try {
    $con->beginTransaction();
    $sql = "INSERT INTO player (playerName,playerSkill,playerPosition,teamId) VALUES (:playerName,:playerSkill,:playerPosition,:teamId)";
    $stmt = $con->prepare($sql);
    foreach ($_POST['playerName'] as $key => $value) {
        $stmt->execute([
            'playerName' => trim($value),
            'playerSkill' => trim($_POST['playerSkill'][$key]),
            'playerPosition' => trim($_POST['playerPosition'][$key]),
            'teamId' => $teamId,
        ]);
    }
    $con->commit();
    echo "Items Inserted successfully";
} catch (Exception $e) {
    $con->rollBack();
    die("插入失败：" . $e->getMessage());
}

修复球队表单的跳转问题，把header跳转放到所有输出之前，同时改成预处理SQL避免注入。
Ajax请求增加error回调，打印后端返回的错误信息，方便后续调试。

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

Ajax动态表单输入数据无法插入MySQL数据库问题求解

业务背景 #

现有代码 #

数据库连接文件（connection.php） #

球队录入表单（teamform.php） #

球员动态表单（playerform.php） #

球员写入处理接口（playerformAction.php） #

故障原因 #

修复方案 #