2019-04-18 19:15
浏览 103

PHP - 使用2个外键将数据插入MySQL表

I'm doing a school project - a website with students performances in various sports. I have three tables:

TABLE1 - "students"

  • id (primary key)
  • class
  • firstname
  • lastname

TABLE2 - "sports"

  • sport_id (primary key)
  • sportname

TABLE3 - "performances"

  • performance_id (primary key)
  • sport_id (foreign key - sports.sport_id)
  • student_id (foreign key - students.id)
  • value

I want to make a form that adds data into the third table. That form should include:

  • class
  • firstname
  • lastname
  • sportname
  • value

...but I have no idea how to achieve this.

I could just create a form where user user adds value and then copy-pastes sport_id and student_id from tables below it, but that's unpractical.

I've been searching the internet for a while, but I haven't found any solution to this and if I did, it was only for one foreign key.

Does anyone know how to do this? If so, I would highly appreciate it! :)

EDIT: I should've mentioned that tables "students" and "sports" already have all the data in them, I just need to insert new performances using that data.

图片转代码服务由CSDN问答提供 功能建议

我正在做一个学校项目 - 一个学生在各种体育项目中表演的网站。 我有三个表:

TABLE1 - “学生”

  • id(主键)< / LI>
  • 姓名
  • 名字

    <强> TABLE2 - “运动”

    • sport_id(主键)
    • sportname \ n

      TABLE3 - “表演”

      • performance_id(主键)
      • sport_id( 外键 - sports.sport_id)
      • student_id(外键 - students.id)
      • < p>我想创建一个将数据添加到第三个表中的表单。 该表格应包括:
        • class
        • firstname
        • lastname < li> sportname
        • value



          我已经在互联网上搜索了一段时间,但我没有找到任何解决方案,如果我这样做,那只是一个外键。 \ n

          有谁知道怎么做? 如果是这样,我会非常感激! :)


  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • dtgr6303 2019-04-19 00:08

    Since the data is already in the tables for students and sports, this information can be queried with some select statements in order to populate some HTML dropdowns. The advantage of using the select queries and the dropdowns is that value of the options can be set to the database ID while showing the user the human-readable text. Then, the page just needs to monitor for the form's submission and insert the IDs from the dropdowns along with the performance metric. I have not tested the code below, but here is a quicky example of how that might work.

    Note: I like the PDO interface for preparing SQL queries in order to prevent injection attacks.

    $user = 'user';
    $password = 'password';
    $con = new PDO('mysql:dbname=dbname;host=;chartset=urf8', $user, $password);
    $student_stmt = $con->prepare('select * from students');
    $sport_stmt = $con->prepare('select * from sports');
    if (isset($_GET['student']) && isset($_GET['sport']) && isset($_GET['value'])) {
        $student = $_GET['student'];
        $sport = $_GET['sport'];
        $value = $_GET['value'];
        $insert_stmt = $con->prepare('insert into preformances (sport_id, student_id, value) values (:sport_id, :student_id, :value)');
        $insert_stmt->bindParam(':sport_id', $sport);
        $insert_stmt->bindParam(':student_id', $student);
        $insert_stmt->bindParam(':value', $value);
            <form action="self.php" method="get">
                <select name="student">
        <?php while ($row = $student_stmt->fetch(PDO::FETCH_ASSOC)) { ?>
                    <option value="<?php echo $row['id']; ?>"><?php echo $row['firstname'] . " " . $row['lastname']; ?></option>
        <?php } ?>
                <select name="sport">
        <?php while ($row = $sport_stmt->fetch(PDO::FETCH_ASSOC)) { ?>
                    <option value="<?php echo $row['sport_id']; ?>"><?php echo "$row['sportname']"; ?></option>
        <?php } ?>
                Performance: <input name="value" type="text" />
                <button type="submit">Submit</button>

    Edit: Made the changes in the code in the suggested comment.

    解决 无用
    打赏 举报

相关推荐 更多相似问题