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

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.

  • 写回答

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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 如何使用chatgpt完成文本分类任务?
  • ¥15 已知速度v关于位置s的等式,怎么转化为已知位置求速度v的等式
  • ¥15 我有个餐饮系统,用wampserver把环境配置好了,但是后端的网页却进去,是为什么,能不能帮远程一下?
  • ¥15 R运行没有名称为"species"的插槽对于此对象类"SDMmodelCV"
  • ¥20 基于决策树的数字信号处理,2ask 2psk 2fsk的代码,检查下报错的原因
  • ¥20 python作业求过程
  • ¥15 wincc已组态的变量过多
  • ¥60 如图:直线与椭圆X轴平行,求直线与椭圆任意一点的相切坐标计算公式
  • ¥50 如何用python使用opencv里的cv::cudacodec::VideoWriter函数对视频进行GPU硬编码
  • ¥100 c#solidworks 二次开发 工程图自动标边线法兰 等折弯尺寸怎么标