MySQL 只更新名字而不是全名 [英] MySQL Updates only first name instead of full name

问题描述

我在 phpmyadmin 中有一个名为 assets 的表，它有一个责任列.创建资产后，在 $_SESSION["name"] 的帮助下，我可以将正确的值放入此列中.

I have a table called assets in phpmyadmin which has a Responsible column. When an asset is created, with the help of $_SESSION["name"] I can put the right value into this column.

if($_SERVER["REQUEST_METHOD"] == "POST"){

    $id = $_POST['id']; 
    $name=$_POST['Asset_name'];
    $classification=$_POST['Classification'];
    $tag=$_POST['Tag'];
    $department=$_POST['Department'];
    $reviewdate=$_POST['Review_date'];
    $responsible=$_POST['Responsible'];
    $createdby=$_POST['Created_by'];

    // Making sure name is typed in and contains only letters
    if (empty(trim($name))) {
       $nameErr = "Name is required";
    } else {
        if (!preg_match("/^[a-zA-Z ]*$/", $name)) {
            $nameErr = "Only letters and white space allowed";
        }   
    }
    // Making sure tag is typed in and contains only letters
    if (empty(trim($tag))) {
        $tagErr = "Tag is required";
    } else {
        if (!preg_match("/^[a-zA-Z ]*$/", $tag)) {
            $tagErr = "Only letters and white space allowed";
        }
    }

    if (!empty($_POST) && ($nameErr == "") && ($tagErr == "")){    
        // Updating the table
        $result = mysqli_query($conn, "UPDATE assets SET Asset_name='$name',Classification='$classification',Tag='$tag',Department='$department',Review_date='$reviewdate', Responsible='$responsible' WHERE id='$id'");

        // Redirecting to assets.php
        header("Location: assets.php");
    }

但是，这些资产也应该被编辑.我使用一个表单来执行此操作，在该表单中，我使用此脚本遍历用户表名称"列:

However, these assets should be edited as well. I do it with a form where I loop through the users table "Name" column with this script:

mysqli_query($conn, "UPDATE assets SET Responsible='$responsible' WHERE id='$id'");

这是脚本(pdo"是数据库规范，但有效):

This is the script ("pdo" is the database specification but that works"):

$sql = "SELECT id,name FROM users";

// Prepare the select statement.
$stmt = $pdo->prepare($sql);

// Execute the statement.
$stmt->execute();

// Retrieve the rows using fetchAll.
$users = $stmt->fetchAll();

在表格中这是我使用的:

In the form this is what I use:

<?php   if($_SESSION['user_type'] == "admin") {
                        echo "<div class='form-group form-inline'>";
                        echo "<p class='formLabel'>Responsible</p>";                        
                        echo "<select class = 'select' name = 'Responsible' class='form-control' value=".$responsible.">";
                                foreach($users as $user):
                                    echo "<option value=".$user['name']."> ".$user['name']."</option>";
                                endforeach;
                                echo "</select>";
                    } else { echo "<input type='hidden' name = 'Responsible' value=".$_SESSION['name'].">";
                    }
            ?>  

当我从这个脚本创建的列表中选择值时，它们是正确的，一切看起来都很好.但是，在单击更新后，负责列中只显示该人的名字，而不是全名(例如约翰而不是约翰威廉姆斯)，我不明白为什么.用户姓名"和资产负责人"列都是长度为 255 的 varchar.我怀疑我的问题出在查询上，但我不知道为什么.你能帮助我吗?谢谢！

When I choose the values from the list this script has created, they are the right ones, everything looks good. However, after clicking on Update, only the first name of that person is shown in the responsible column not the full name (e.g. John instead of John Williams) and I do not understand why. The users "Name" and the assets "Responsible" column are both varchar with 255 length. I suspect that my problem is with the query, but I dont know why. Can you help me? Thank you!

这是我的表:用户资产

推荐答案

您的问题是您没有引用 HTML 属性.这使得浏览器在第一个空格处破坏该值.将您的代码更新为:

Your issue is that you aren't quoting your HTML attributes. This makes browsers break that value at the first space. Update your code to:

echo '<option value="' . $user['name']. '"> . $user['name']. '</option>';

这应该可以解决您的问题.您还应该查看 XSS 和 SQL 注入.这似乎对两者都敏感.

This should resolve your issue. You also should look at XSS and SQL injections. This appears to be susceptible to both.

如何防止 PHP 中的 SQL 注入?

您的 select 也不应该有 value 属性.

Your select also shouldn't have a value attribute.

每个 元素都应该有一个 value 属性，其中包含在选择该选项时要提交给服务器的数据值；如果不包含 value 属性，则该值默认为元素内包含的文本.您可以在 元素上包含 selected 属性，使其在页面首次加载时默认选中.

Each <option> element should have a value attribute containing the data value to submit to the server when that option is selected; if no value attribute is included, the value defaults to the text contained inside the element. You can include a selected attribute on an <option> element to make it selected by default when the page first loads.

-https://developer.mozilla.org/en-US/docs/Web/HTML/Element/select

所以改变:

echo "<select class = 'select' name = 'Responsible' class='form-control' value=".$responsible.">";

进入:

echo '<select class="select" name="Responsible" class="form-control">
<option value="' . $responsible. '" selected="selected">Unchanged</option>';

这篇关于MySQL 只更新名字而不是全名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！