点击提交时如何将第一张表的ID存入第二张表的position_id字段？

你的需求核心是在提交表单时，先插入article表获取自增ID，再将这个ID批量填入data表所有新增记录的position_id字段中。下面是修改后的完整代码，以及关键修改点说明：

修改后的完整代码 #

数据库部分（无需修改） #

CREATE TABLE IF NOT EXISTS `article` ( 
    `id` int(10) NOT NULL AUTO_INCREMENT, 
    `title` varchar(100) NOT NULL, 
    `description` text NOT NULL, 
    `text` text NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

CREATE TABLE IF NOT EXISTS `data` ( 
    `id` int(10) NOT NULL AUTO_INCREMENT, 
    `position_id` int(10) NOT NULL, 
    `name` varchar(100) NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

index.php（无需修改） #

<html>
<head>
<title>Dynamically add input field using jquery</title>
<style>
.container1 input[type=text] { padding:5px 0px; margin:5px 5px 5px 0px; }
.add_form_field { background-color: #1c97f3; border: none; color: white; padding: 8px 32px; text-align: center; text-decoration: none; display: inline-block; font-size: 16px; margin: 4px 2px; cursor: pointer; border:1px solid #186dad; }
input{ border: 1px solid #1c97f3; width: 260px; height: 40px; margin-bottom:14px; }
.delete{ background-color: #fd1200; border: none; color: white; padding: 5px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 4px 2px; cursor: pointer; }
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script>
$(document).ready(function() {
var max_fields = 10;
var wrapper = $(".container1");
var add_button = $(".add_form_field");
var x = 1;
$(add_button).click(function(e){
e.preventDefault();
if(x < max_fields){
x++;
$(wrapper).append('<div><input type="text" name="mytext[]"/><a href="#" class="delete">Delete</a></div>'); //add input box
} else {
alert('You Reached the limits')
}
});
$(wrapper).on("click",".delete", function(e){
e.preventDefault();
$(this).parent('div').remove();
x--;
})
$('#submit').click(function(){
$.ajax({
url:"name.php",
method:"POST",
data:$('#add_name').serialize(),
success:function(data) {
$('#mess').html(data);
$("#content").hide();
}
});
});
});
</script>
</head>
<body>
<span id="mess"></span>
<div id="content">
<form name="add_name" id="add_name">
Title:<br>
<input type="text" name="title">
<div class="container1">
<button class="add_form_field">Add New Field &nbsp; <span style="font-size:16px; font-weight:bold;">+ </span></button>
<div><input type="text" name="mytext[]"></div>
</div>
Description:<br>
<textarea cols="50px" rows="15px" name="description"></textarea><br>
Text:<br>
<textarea cols="50px" rows="15px" name="text"></textarea><br>
<input type="button" name="submit" id="submit" class="btn btn-info" value="Submit" />
</form>
</div>
</body>
</html>

修改后的name.php #

<?php
$number = count($_POST["mytext"]);
if($number > 0 && !empty(trim($_POST["title"])) && !empty(trim($_POST["description"])) && !empty(trim($_POST["text"]))) {
    $title = $_POST["title"];
    $desc = $_POST["description"];
    $text = $_POST["text"];
    $connect = new PDO('mysql:dbname=test_db;host=localhost','root','', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION));
    
    try{
        // 1. 插入article表并获取自增ID
        $query = "INSERT INTO article (title, description, text) VALUES (:title, :description, :text)";
        $statement = $connect->prepare($query);
        $statement->execute( array( ':title' => $title, ':description' => $desc, ':text' => $text ) );
        
        // 获取刚插入的article的ID
        $articleId = $connect->lastInsertId();
        
        // 2. 批量插入data表，每条记录绑定position_id为article的ID
        for($i=0; $i<$number; $i++) {
            if(trim($_POST["mytext"][$i]) != '') {
                $queryData = "INSERT INTO data (position_id, name) VALUES (:position_id, :name)";
                $data = array(
                    ':position_id' => $articleId,
                    ':name' => $_POST["mytext"][$i]
                );
                $statementData = $connect->prepare($queryData);
                $statementData->execute($data);
            }
        }
        
        echo "Article and related data added successfully!";
    } catch(PDOException $e){
        $errorMsg = $e->getMessage() . " " . __CLASS__.' -> '.__METHOD__;
        file_put_contents("exceptionlog.txt", $errorMsg . PHP_EOL, FILE_APPEND);
        echo "Error: " . $errorMsg;
    }
} else {
    echo "Please fill in all required fields and add at least one data entry!";
}
?>

关键修改点说明 #

• 获取article自增ID：在插入article成功后，使用PDO的lastInsertId()方法获取刚生成的id，这个ID就是我们需要的position_id。
• 修改data插入SQL：更新data表的插入语句，新增position_id字段的绑定，确保每条data记录都关联到对应的article。
• 优化逻辑判断：将article插入和data插入放到同一个try块中，确保如果article插入失败，data也不会被插入；同时增强了表单字段的非空判断，避免无效提交。
• 异常处理优化：简化了异常抛出逻辑，确保错误信息能正确写入日志并反馈给前端。

这样修改后，点击提交按钮时，系统会先插入article记录，拿到ID后再批量插入所有data记录，每条data的position_id都会自动填充为对应的article ID，完全满足你的需求。

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