php - 将组合框中选定的值存储到数据库中

Question

我正在尝试创建一个带有文本输入的表单和一个加载了从数据库检索的值和数据的组合框。这个想法是，当您提交表单时，文本字段中的值将存储在数据库的字段之一中。我需要做的是将组合框的选定值(数字并从数据库的其他表中提取)(不是组合中显示的文本)存储到存储表单文本字段的同一个表中.

数据库结构由两个表组成，分别名为国家和名称。国家/地区有一个名为country_id 的字段和另一个名为country_name 的字段(这两个字段填充组合框。表Appellations 具有三个名为appellation_id (pk、nn、ai)、country_id 和appellation_name 的字段。appellation_name 字段必须填写在表单的文本字段，country_id 必须填写组合框的选定值，该值等于国家表的country_id 值。到目前为止，我已经能够创建表单并使用 db 值加载组合框中的所有国家/地区。

表单代码是

<form name="New Appellation" action="actions/register_appellation.php" method="POST"     onsubmit="return validate()">
Nombre denominacion de origen:
<input type="text" placeholder="Nombre DO" name="fappellation_name">
<br>
Pais denominacion de origen:
<?php
include_once 'includes/fast_conn.php';
mysqli_select_db($cn,$db_name) or die(mysql_error());
$sql = "SELECT country_name, country_id FROM countries";
$rs = mysqli_query($cn,$sql) or die(mysql_error());
echo "<select name='appellation_country'>";
while($row = mysqli_fetch_array($rs)){
echo "<option value='".$row["country_id"]."'>".$row["country_name"]."</option>";
}mysqli_free_result($rs);
echo "</select>";
?>
<input type="submit" value="Registrar">

</form>

处理 POST register_appellation.php 的 php 是:

<?php
include_once '../includes/fast_conn.php';
$fappellation_name=$_POST['fappellation_name'];
$fcountry_id=$_POST['country_id'];
$save="INSERT INTO $db_name($fcountry_id, $$fappellation_name)";
$result=mysqli_query($cn,$save);
if($result){
echo "<font size='+1' color='blue'> $fappellation_name, Successfully Registered.</font>";

}
else
{

echo "<font size='+1' color='red'>Registration Failed.</font>";

}
?>

任何帮助将不胜感激

Answer 1

我在下面添加了修改后的源代码，应该可以解决该问题。

我在源代码中做了一些事情。我相信这一切都应该被很好地记录下来。以下是我添加的内容的列表:

1. 代码现已更新，可以完全使用 MySQLi。
2. 两种形式的 MySQLi 都是 OOP 风格(我更喜欢 MySQLi)。
3. 通过错误检查来查看操作是否成功并相应地显示错误。
4. register_appellation.php 现在检查字段是否已填写。
5. register_appellation.php 现在对查询变量执行清理。 (有助于防止 SQL 注入(inject)攻击)
6. register_appellation.php 现在通过与 countries 表交叉引用来确认其收到的国家/地区 ID 是否有效。

<小时/>

这是表单代码:

<form name="New Appellation" action="actions/register_appellation.php" method="POST"    onsubmit="return validate()">
Nombre denominacion de origen:
<input type="text" placeholder="Nombre DO" name="fappellation_name">
<br>
Pais denominacion de origen:
<?php
include_once 'includes/fast_conn.php';
$boolSelectionSuccessful = $cn->select_db($db_name);
if(!$boolSelectionSuccessful){
    $deathMessage = "<br><div>" .
        "Oh no! An error occurred on our end!<br>" .
        "Here's what we know:<hr>" .
        $cn->error .
        "<hr>" .
        "If this error continues to occur, please contact the website administrator." .
        "</div>";
    die($deathMessage);
}
$sql = "SELECT `country_name`, `country_id` FROM `countries`";
$rs = $cn->query($sql);
if($rs === FALSE){
    $deathMessage = "<br><div>" .
        "Oh no! An error occurred on our end!<br>" .
        "Here's what we know:<hr>" .
        $cn->error .
        "<hr>" .
        "If this error continues to occur, please contact the website administrator." .
        "</div>";
    die($deathMessage);
}
echo "<select name='appellation_country'>";
while($row = $rs->fetch_assoc()){
    echo "<option value='".$row["country_id"]."'>".$row["country_name"]."</option>";
}
$rs->close();
echo "</select>";
?>
<input type="submit" value="Registrar">
</form>

这里是register_appellation.php

<?php
include_once '../includes/fast_conn.php';

function checkField($fieldName){
    if(isset($_POST[$fieldName])){
        if(!empty($_POST[$fieldName])){
            /* The request has the fieldName submitted && the fieldName was not left empty */
            return true;
        }else{
            return false;
        }
    }else{
        return false;
    }
}

$checkArray = array(checkField('fappellation_name'), checkField('appellation_country'));
if(in_array(false, $checkArray) || !is_numeric($_POST['appellation_country'])){
    /* the check assumes that 'appellation_country' (country id) should be numeric */
    die("<font size='+1' color='red'>Registration Failed. Please fill in all forms.</font>");
}

$boolSelectionSuccessful = $cn->select_db($db_name);
if(!$boolSelectionSuccessful){
    $deathMessage = "<br><div>" .
        "Oh no! An error occurred on our end!<br>" .
        "Here's what we know:<hr>" .
        $cn->error .
        "<hr>" .
        "If this error continues to occur, please contact the website administrator." .
        "</div>";
    die($deathMessage);
}
$sql = "SELECT `country_name`, `country_id` FROM `countries`";
$rs = $cn->query($sql);
if($rs === FALSE){
    $deathMessage = "<br><div>" .
        "Oh no! An error occurred on our end!<br>" .
        "Here's what we know:<hr>" .
        $cn->error .
        "<hr>" .
        "If this error continues to occur, please contact the website administrator." .
        "</div>";
    die($deathMessage);
}

$countryIdValid = False;
$countryName = "";
/* $countryName is unused, by the current code, but it will still be set in the while loop.
   If you want to store the country name in the `appeallations` table, this variable
   could simply be added to the insert statement. */

while($row = $rs->fetch_assoc()){
    if($row['country_id'] == $_POST['appellation_country']){
        $countryIdValid = true;
        $countryName = $row['country_name'];
        break;
    }
    /* else - continue looping to check the next country_id */
}

$rs->close();

/* now that the loop has been exited, we'll make sure the country_id was verified */
if(!$countryIdValid){
    die("<font size='+1' color='red'>Registration Failed. Unknown country.</font>");
}

$fappellation_name= $cn->real_escape_string($_POST['fappellation_name']);
$fcountry_id = $cn->real_escape_string($_POST['appellation_country']);
$save="INSERT INTO `appellations` (`country_id`, `appellation_name`) VALUES ('$fcountry_id', '$fappellation_name')";
$result = $cn->query($save);
if($result !== FALSE){
    echo "<font size='+1' color='blue'> " . $_POST['fappellation_name'] . ", Successfully Registered.</font>";
}else{
    $deathMessage = "<br><div>" .
        "Oh no! An error occurred on our end!<br>" .
        "Here's what we know:<hr>" .
        $cn->error .
        "<hr>" .
        "If this error continues to occur, please contact the website administrator." .
        "</div>";
    die($deathMessage);
}
?>

<小时/>

如果我遗漏了什么，请随时编辑和更新我的答案。我没有运行代码，因此可能存在我没有注意到的语法或逻辑错误。

希望这有帮助，

斯宾塞