从下拉菜单将MYSQL数据插入到与字段关联的数据库表中

Question

问题:我需要将额外的数据插入到与使用下拉菜单选择的记录相关联的数据库表中。

我正在从一个名为Player的MySQL表中填充一个带有网球运动员名字的下拉菜单。DB表的行包含3列--唯一的ID、玩家的姓氏和名字。下拉列表显示姓和名。

目前，用户可以选择一个播放器，在按下submit按钮后，它的姓氏将正确地插入到另一个DB表，即暂存表中。但是我也需要插入玩家ID和名字，但是我不能获得正确的ID和名字也要插入到暂存表中.

我似乎无法找到将其他数据与从下拉菜单中进行的用户选择相关联的方法。

社区中的任何人是否可以建议对下面的代码进行修改，以便将玩家ID和名字与姓氏同时插入到暂存表中。

任何社会人士所能给予的协助，我们都会十分感激。守则如下：

<?php 
    $link = mysqli_connect("localhost", "root","","Tennis") or die("Could not connect: ".mysql_error());

    if (isset($_POST['submitted'])) {

        $surname=$_POST['winner'];

        $id = $_POST['ID'];
        $firstname = $_POST['firstname'];

        // Insert into database what the user selected.
        $qresult = mysqli_query($link,"insert into staging (ID,surname,firstname) values ('$id','$surname','$firstname')"); 

    } // end of the main submit condition.


?>
<h1>Tennis Tournament</H1>

    <?php 
    // Fetch player data to populate drop down menu.
    $selectSQL = "SELECT * FROM PLAYER ORDER BY SURNAME";
    $selectSQL = mysqli_query($link, $selectSQL );

    echo '<form method="post" action="Tennis2.php"><br>';

    // Drop down menu.
    echo '<select name="winner">';

        while ($row=mysqli_fetch_array($selectSQL,MYSQLI_BOTH)) {
            $id = $row['ID'];
            $firstname = $row['FIRSTNAME'];
            echo '<option value="'.$row['SURNAME'].'">'.$row['SURNAME'].' '.$row['FIRSTNAME'].'</option>';
        }
    echo '</select>';

    echo '<input type="submit" name="submit" />';
    echo '<input type="hidden" name="ID" value="'.$id.'" />';
    echo '<input type="hidden" name="submitted" value="1" />';
    echo '<input type="hidden" name="firstname" value="'.$firstname.'" />';
    echo '</form>';

    mysqli_free_result($selectSQL);

    ?>

Answer 1

Page.php

在选项值中传递ID。不需要隐藏的领域。

<?php 
// Fetch player data to populate drop down menu.
$selectSQL = "SELECT * FROM PLAYER ORDER BY SURNAME";
$selectSQL = mysqli_query($link, $selectSQL );

echo '<form method="post" action="Tennis2.php"><br>';

  // Drop down menu.
  echo '<select name="winner">';
    while ($row=mysqli_fetch_array($selectSQL,MYSQLI_BOTH)) {
      echo '<option value="'.$row['ID'].'">'.$row['SURNAME'].' '.$row['FIRSTNAME'].'</option>';
    }
  echo '</select>';

  echo '<input type="submit" name="submit" />';
echo '</form>';

mysqli_free_result($selectSQL);

?>

Tennis2.php

捕获从下拉列表中传递的ID并获取它的相关数据，如姓氏、名和插入到暂存表中。

<?php 
$link = mysqli_connect("localhost", "root","","Tennis") or die("Could not connect: ".mysql_error());

if (isset($_POST['submit'])) {

  $id_selected = $_POST['winner'];

  $selectSQLquery = "SELECT * FROM PLAYER WHERE ID = $id_selected LIMIT 0,1";
  $selectSQL = mysqli_query($link, $selectSQLquery );

  $row = mysqli_fetch_array($selectSQL,MYSQLI_BOTH);
  $surname = $row['SURNAME'];
  $firstname = $row['FIRSTNAME'];

  // Insert into database what the user selected.
  $qresult = mysqli_query($link,"INSERT INTO staging (ID,surname,firstname) values ('$id','$surname','$firstname')"); 

} // end of the main submit condition.
?>

Answer 2

试试这段代码

<?php 
    $link = mysqli_connect("localhost", "root","","Tennis") or die("Could not connect: ".mysql_error());

        $ID=$_POST['winner'];

       if($ID){

        // Insert into database what the user selected.
        $qresult = mysqli_query($link,"insert into staging (select * from PLAYER where ID = $ID)"); 
  }

?>
<h1>Tennis Tournament</H1>

    <?php 
    // Fetch player data to populate drop down menu.
    $selectSQL = "SELECT * FROM PLAYER ORDER BY SURNAME";
    $selectSQL = mysqli_query($link, $selectSQL );

    echo '<form method="post" action="Tennis2.php"><br>';

    // Drop down menu.
    echo '<select name="winner">';

        while ($row=mysqli_fetch_array($selectSQL,MYSQLI_BOTH)) {
            $id = $row['ID'];
            $firstname = $row['FIRSTNAME'];
            echo '<option value="'.$row['ID'].'">'.$row['SURNAME'].' '.$row['FIRSTNAME'].'</option>';
        }
    echo '</select>';

    echo '<input type="submit" name="submit" />';
    echo '</form>';

    mysqli_free_result($selectSQL);

    ?>