dqly83915
2019-04-18 19:15
浏览 87
已采纳

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

          ...但我不知道如何实现这一目标。

          我可以创建一个用户用户添加值的表单,然后从下面的表中复制粘贴sport_id和student_id,但这是不切实际的。

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

    <?php
    $user = 'user';
    $password = 'password';
    $con = new PDO('mysql:dbname=dbname;host=127.0.0.1;chartset=urf8', $user, $password);
    
    $student_stmt = $con->prepare('select * from students');
    $student_stmt->execute();
    
    $sport_stmt = $con->prepare('select * from sports');
    $sport_stmt->execute();
    
    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);
        $insert_stmt->execute();
    }
    ?>
    
    <html>
        <head>
            <title>Form</title>
        </head>
        <body>
            <form action="self.php" method="get">
                Student:
                <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>
    
                Sport:
                <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 } ?>
                </select>
                Performance: <input name="value" type="text" />
                <button type="submit">Submit</button>
            </form>
        </body>
    </html>
    

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

    打赏 评论
  • dqm4977 2019-04-18 19:38

    I think all you need to do is to get input values from your form ($variable = $_GET["classinput"];) and then connect to database and write mysqli query with input query for every table.

    like this:

    $query = mysqli_query($connection, "INSERT INTO STUDENTS(id,class,firstname,lastname) VALUES (null,\"$class\",\"$firstname\",\"$lastname\")");
    

    And do this for all your tables.

    打赏 评论

相关推荐 更多相似问题