React+MySQL实现跨表关联：获取自增主键作为外键

1. 修改后端个人信息接口，返回自增ID #

首先修正controllers/info.js的SQL语法错误，并在插入成功后返回生成的自增主键id：

export const addInfo = (req,res)=>{
    // 修正SQL字段列表的语法错误（移除开头多余的逗号）
    const q = 'INSERT INTO survey_Info (`age`, `adress`, `phone`) VALUES (?)'

    const values = [
        req.body.age,
        req.body.adress,
        req.body.phone,
    ]

    db.query(q, [values], (err, data) =>{
        if (err) return res.status(500).json(err);
        // 返回插入记录的自增ID
        return res.status(200).json({ 
            message: "Info has been sent succesfully",
            infoId: data.insertId 
        });
    })
}

2. 前端第一页接收ID并传递到第二页 #

修改pages/Info.jsx，提交成功后获取后端返回的infoId，通过localStorage存储（无登录场景下更稳妥，避免页面刷新丢失）：

import React, { useState } from "react";
import { Link } from "react-router-dom";
import axios from "axios";
import { useNavigate } from "react-router-dom";

const Information = () => {
  const [answers, setAnswers] = useState({});
  const navigate = useNavigate(); // 初始化navigate

  const handleChange = (e) => {
    setAnswers((prev) => ({
      ...prev,
      [e.target.name]: e.target.value,
    }));
  };

  const saveAnswers = async () => {
    try {
      // 接收后端返回数据
      const response = await axios.post("http://localhost:8800/server/information", answers);
      // 存储infoId到localStorage
      localStorage.setItem("surveyInfoId", response.data.infoId);
      navigate("/question"); // 跳转到第二页问题页面
    } catch(err) {
      console.log(err);
    }
  };

  return (
    <div className="w-full py-[2rem] px-4 bg-white">
      <div className="max-w-[800px] mt-24 w-full mx-auto text-center flex flex-col justify-center">
        <h1 className="md:text-7xl text-[#009999] sm:text-6xl text-4xl font-bold md:py-6">
          Text
        </h1>
      </div>
      <div className="max-w-[800px] w-full mx-auto text-center flex flex-col">
        <form>
          {/* 你的表单代码 */}
        </form>

        <button
          className="bg-black text-[#e3e3e3] w-[200px] rounded-md font-medium my-6 mx-auto px-6 py-3"
          onClick={saveAnswers}
        >
          Continua
        </button>
      </div>
    </div>
  );
};

export default Information;

3. 前端第二页获取ID并和问题答案一起提交 #

修改第二页组件（修正组件名大驼峰规范），获取localStorage中的infoId并加入提交数据：

import React, { useState } from "react";
import Questions from "./question.json";
import { useNavigate } from "react-router-dom";
import axios from "axios"; // 补全axios导入

const Question = () => {
  const [answers, setAnswers] = useState({});
  const navigate = useNavigate();

  const handleChange = (e) => {
    setAnswers((prev) => ({
      ...prev,
      [e.target.name]: e.target.value,
    }));
  };

  const saveAnswers = async () => {
    try {
      // 从localStorage获取infoId
      const infoId = localStorage.getItem("surveyInfoId");
      // 合并infoId到提交数据
      const submitData = { ...answers, infoId };
      await axios.post("http://localhost:8800/server/question", submitData);
      // 可选：提交成功后清除localStorage中的ID
      localStorage.removeItem("surveyInfoId");
      navigate("/results");
    } catch(err) {
      console.log(err);
    }
  };

  return (
    <div className="w-full py-[2rem] px-4 bg-white">
      <div className="max-w-[800px] w-full mx-auto text-center flex flex-col">
        <form>
          {/* 你的表单代码 */}
        </form>

        <button
          className="bg-black text-[#e3e3e3]  w-[200px] rounded-md font-medium my-6 mx-auto px-6 py-3"
          onClick={saveAnswers} // 修正点击事件为saveAnswers
        >
          Continua
        </button>
      </div>
    </div>
  );
};

export default Question;

4. 修改后端问题接口，正确使用infoId #

修改controllers/question.js，将硬编码的占位符替换为前端传递的真实infoId：

import { db } from "../connect.js";

export const addAnswer = (req,res)=>{
    const q = 'INSERT INTO question (`infoId`, `question_1`, `question_2`,`question_3`,`question_4`) VALUES (?)'

    const values = [
        req.body.infoId, // 使用前端传递的真实ID
        req.body.question_1,
        req.body.question_2,
        req.body.question_3,
        req.body.question_4,
    ]

    db.query(q, [values], (err, data) =>{
        if (err) return res.status(500).json(err);
        return res.status(200).json("question has been sent succesfully");
    })
}

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