允许用户在mysql中编辑

I am having a problem with my code. This code is supposed to display a mysql database and let the user edit it so that their edits register in the mysql table. But for some reason the query is not working and I can't get it so that the user can edit into a mysql table.

我的代码有问题。该代码应该显示一个mysql数据库，并让用户编辑它，使其编辑器在mysql表中注册。但是由于某种原因，查询无法工作，我无法让用户编辑到mysql表中。

<!DOCTYPE HTML>
<html>
<head>
    <title><?php echo 'giggity'; ?></title>
</head>
<body>
<?php
$con = mysqli_connect('localhost', 'root', 'ankith12','Employees');
        if (mysqli_connect_errno())
    {
         echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

        $sql = "select * from Employ";
        $query = mysqli_query($con,$sql);
        echo "<table border ='1' style='height:90%;width:90%; position: absolute; top: 50; bottom:50; left: 0; right: 0;border:1px solid' align = 'center'>
            <tr>
            <th>Employee id</th>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Meetings Today</th>
            <th>Sales</th>
            <th>Comments</th> 
            </tr>";
            ?>
            <form method = 'Post'>
            <?php
$i = 1;
 while( $row = mysqli_fetch_array($query) )
{
    echo "<tr><td>". $row['employee_id'] . "<br><input type ='submit' name = 'Submit_$i' >". "</td>";
    echo "<td>". $row['Firstname']. "<input type = 'textfield' name = 'first' >"."</td>";
    echo "<td>". $row['Lastname']."<input type = 'textfield' name = 'second' >" . "</td>";
    echo "<td>". $row['Meetings']."<input type = 'textfield' name = 'third' >". "</td>";
    echo "<td>". $row['Sales']."<input type = 'textfield' name = 'fourth' >". "</td>";
    echo "<td>". $row['Comments']."<input type = 'textfield' name = 'fifth' >". "</td></tr>";
    $i++;
}
echo "</table>";
?>
<br>
<br> 
<!-- Submit<br><input type ='submit' name = 'Submit' > -->
</form>
<?php 

function alert($s){
    echo "<script type = 'text/javascript'>alert(\"$s\");</script>";
}

// $i = 1
$con = mysqli_connect('localhost', 'root', 'ankith12','Employees');
        if (mysqli_connect_errno())
    {
         echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
$query = "SELECT employee_id from Employ";
$qudey = mysqli_query($con,$query);
$rows= mysqli_fetch_assoc($qudey);
$dee = 1;
$easy = 0;
// $userfirst = $_POST['first'];
// $userlast =  $_POST['second'];
// $usermeetings =  $_POST['third'];
// $usersales =  $_POST['fourth'];
// $usercomments =  $_POST['fifth'];
foreach($rows as $i){
    //alert($_POST["Submit_$dee"]);
    if(isset($_POST["Submit_$dee"])) {
    //  alert("true");
        $i = 1;
        $userfirst = $_POST['first'];
        $userlast =  $_POST['second'];
        $usermeetings =  $_POST['third'];
        $usersales =  $_POST['fourth'];
        $usercomments =  $_POST['fifth'];
        alert($userfirst);
        if($userfirst !== ""){
            $QueryA = "UPDATE Employ SET Firstname = $userfirst WHERE employee_id = $i";
            mysqli_query($con,$QueryA);
            alert($QueryA);
        }
        if($userlast !== "")
        {
            $QueryB = "UPDATE Employ SET Lastname = $userlast WHERE employee_id = $i";
            mysqli_query($con,$QueryB);
        }
        if($usermeetings !== "")
        {
            $QueryC = "UPDATE Employ SET Meetings = $usermeetings WHERE employee_id = $i";
            mysqli_query($con,$QueryC);
        }
        if($usersales !== "")
        {
            $QueryD = "UPDATE Employ SET Sales = $usersales WHERE employee_id = $i";
            mysqli_query($con,$QueryD);
        }
        if($usersales !== "")
        {
            $QueryE = "UPDATE Employ SET Comments = $usercomments WHERE employee_id = $i";
            mysqli_query($con,$QueryE);
        }
        //echo 'done';
}
//  echo'done';
    $easy++;
    $dee = $dee + 1;
}
mysqli_close($con);
?>
</body>
</html>

4 个解决方案

#1

@user3152011 Do you have more than 1 employee, if so your inputs are coming back as all blank unless you're trying to update the last employee's information because you're defining multiple inputs with the same name. Try var_dump($_POST)and see.

@user3152011你是否有一个以上的员工，如果有的话，你的输入全部是空的，除非你想更新最后一个员工的信息，因为你定义了多个同名的输入。尝试var_dump($ _POST)看看。

for example right now if you have 2 employees, you'll have 2 inputs both with same name like in <input type = 'textfield' name = 'first' > so when you submit if you submit the first employee your $_POST['first'] will be blank.

例如，现在如果你有两个雇员，你将有两个输入都有相同的名字，比如所以当你提交第一个雇员时，你的$_POST['first']将是空的。

You can either put <form> inside your while loop so that each one is a separate form or look into using something like <input type = 'textfield' name = 'first[]' > so that they all come back as an array so you have $_POST['first'][0] or $_POST['first'][1] and so on.

你可以在while循环中放入

Also, if you want users to edit field names (instead of printing out the value and then having a blank input with echo "<td>". $row['Firstname']. "<input type = 'textfield' name = 'first' >"."</td>) put that value right into the textfield by using echo "<td><input type = 'textfield' name = 'first' value='". $row['Firstname']."'>"."</td>, it'll be much friendlier. And since the values will be populated with values from the database you don't have to check to see if it's blank, you can always just run the UPDATE if it's submitted, if nothing changes it'll just update it with existing data which is no change.

此外，如果您希望用户编辑字段名(而不是打印出值，然后使用echo“”进行空白输入)。行美元(“Firstname”)。" .")使用echo "”。"，会更友好的。由于这些值将由来自数据库的值填充，所以您不需要检查它是否为空，如果提交了更新，您总是可以运行更新，如果没有更改，它将使用现有数据更新更新，这是没有更改的。

And I am not sure why you're running the $query = "SELECT employee_id from Employ"; the second time.

我不确定为什么要运行$query = "从employee_id中选择employee_id "第二次。

Now, it looks like you're hardcoding to update WHERE employee_id = $i which is 1 in your case. You might want to pass the employee_id along with all your other fields using something like echo "<input type="hidden" name="employee_id" value = '".$row['employee_id']."'>"; This way when you submit the form, you'll have an employee_id available in $_POST['employee_id'] and just update that employee.

现在，看起来您正在硬编码以更新employee_id = $i的位置，在您的例子中是1。您可能希望使用echo "";这样，当您提交表单时，您将获得一个employee_id，在$_POST['employee_id']中，并只更新该雇员。

*** And don't forget to protect yourself from SQL Injections using http://ca1.php.net/mysqli_real_escape_string

不要忘记使用http://ca1.php.net/mysqli_real_escape_string保护自己不受SQL注入的影响

You can try out the code below:

你可以试试下面的代码:

<!DOCTYPE HTML>
<html>
<head>
    <title><?php echo 'giggity'; ?></title>
</head>
<body>
<?php
function alert($s){
    echo "<script type = 'text/javascript'>alert(\"$s\");</script>";
}
    $con = mysqli_connect('localhost', 'root', 'ankith12','Employees');
        if (mysqli_connect_errno())
    {
         echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    //We'll try to update data first so that the query to display Employ is shown with fresh data
    if(isset($_POST["employee_id"])) {
        $useremployeeid = mysqli_real_escape_string($con,$_POST['employee_id']);
        $userfirst = mysqli_real_escape_string($con,$_POST['first']);
        $userlast =  mysqli_real_escape_string($con,$_POST['second']);
        $usermeetings =  mysqli_real_escape_string($con,$_POST['third']);
        $usersales =  mysqli_real_escape_string($con,$_POST['fourth']);
        $usercomments =  mysqli_real_escape_string($con,$_POST['fifth']);

        alert($userfirst);

        $QueryA = "UPDATE Employ SET Firstname = '$userfirst',
                                     Lastname = '$userlast',
                                     Meetings = '$usermeetings',
                                     Sales = '$usersales',
                                     Comments = '$usercomments'
                    WHERE employee_id = $useremployeeid";
        $query = mysqli_query($con,$QueryA);
        if (!$query){
             printf("Error: %s\n%s\n", mysqli_sqlstate($con),mysqli_error($con));
        }
    }

        $sql = "select * from Employ";
        $query = mysqli_query($con,$sql);
        if (!$query){
             printf("Error: %s\n%s\n", mysqli_sqlstate($con),mysqli_error($con));
        }
        echo "<table border ='1' style='height:90%;width:90%; position: absolute; top: 50; bottom:50; left: 0; right: 0;border:1px solid' align = 'center'>
            <tr>
            <th>Employee id</th>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Meetings Today</th>
            <th>Sales</th>
            <th>Comments</th> 
            </tr>";
$i = 1;
 while( $row = mysqli_fetch_array($query) )
{
    echo "<form method = 'Post'>";
    echo "<input type='hidden' name='employee_id' value='".$row['employee_id']."'>";
    echo "<tr><td>". $row['employee_id'] . "<br><input type ='submit' name = 'Submit_$i' >". "</td>";
    echo "<td><input type = 'textfield' name = 'first' value='". $row['Firstname']. "'>"."</td>";
    echo "<td><input type = 'textfield' name = 'second' value='". $row['Lastname']."'>" . "</td>";
    echo "<td><input type = 'textfield' name = 'third' value='". $row['Meetings']."'>". "</td>";
    echo "<td><input type = 'textfield' name = 'fourth' value='". $row['Sales']."'>". "</td>";
    echo "<td><input type = 'textfield' name = 'fifth' value='". $row['Comments']."'>". "</td></tr>";
    echo "</form>";
    $i++;
}
echo "</table>";
mysqli_close($con);
?>
</body>
</html>

#2

You have update statements like this:

你有这样的更新语句:

UPDATE Employ SET Firstname = $userfirst WHERE employee_id = $i

Most people's names are strings of letters. Consider, for instance, if employee 1 had the name "Gordon":

大多数人的名字都是字母串。例如，考虑一下，如果雇员1的名字是“Gordon”:

UPDATE Employ SET Firstname = Gordon WHERE employee_id = 1

Can you see what is wrong with this query? Hint: SQL does not recognize "Gordon" as being anything. You need single quotes to delimit the value:

你能看出这个查询有什么问题吗?提示:SQL不承认“Gordon”是什么。您需要单引号来分隔值:

UPDATE Employ SET Firstname = '$userfirst' WHERE employee_id = $i

When you are writing code that does variable substituion and you have a problem, the first step should always be to print out the string after variable substitution.

当您正在编写进行变量替换的代码并且遇到问题时，第一步应该始终是在变量替换之后打印字符串。

#3

hi you can use the below code instead of repeating the same query for each field

hi，您可以使用下面的代码，而不是对每个字段重复相同的查询

$query=mysql_query("UPDATE Employ SET Firstname = '$userfirst',Lastname = '$userlast',Meetings = '$usermeetings',Sales = '$usersales',Comments = '$usercomments'WHERE employee_id ='$i'") or die(mysql_error)

hope this will work for you

希望这对你有用

#4

How to help yourself:

如何帮助自己:

There is way too much going on in your code to make a simple test, or frankly to have anyone here take the time to help you with it.

要做一个简单的测试，或者坦白地说，要让这里的任何人花时间来帮助您，代码中有太多的东西要做。

Make a copy of your code above and set it a side for now. Then take an ax to your code and take out everything that absolutely isn't needed. Take it down to about 3 lines of code.

将上面的代码复制一份，现在将其设置为侧边。然后把斧子放到你的代码中，取出所有绝对不需要的东西。把它减少到大约3行代码。

1) Just a connect and display any error message.  
2) Create a simple query string with one field and one table.
3) And then run that one line and get the error message back.

BTW, can you edit your MySQL using MySQL Query Browser? If not sort that out first.

顺便说一下，你能用MySQL查询浏览器编辑MySQL吗?如果不先解决这个问题。

You must learn to break problems in half and to simplify things when they aren't working. You can't really expect other people to do that work for you. It is your job as a programmer to learn to tease out details by testing little things at a time. Then as you get the little things working, you can add them together to build big things. I've taken programs that were 1000 lines of code long and reduced them to 10 lines to figure out a problem at times (not often, thankfully, but sometimes). For example, I had a bug many years ago where a random byte in memory was getting changed, intermittently. It was in a fairly big project and I at first didn't have any idea of where to look for it. But by a process of removing things to simplify the system I was able to little by little sort out where it was coming from. It turned out to be a register that was getting stomped on by a timer once a second in a random way.

你必须学会把问题分解成两部分，并在问题不起作用时简化问题。你不能指望别人为你做那件事。作为一名程序员，你的工作就是学会一次测试一些小事情来梳理细节。然后当你让这些小的东西工作起来的时候，你可以把它们加在一起来构建大的东西。我使用了1000行代码的程序，并将它们减少到10行，以便有时能解决问题(谢天谢地，不是经常这样，但有时会这样)。例如，许多年前我有一个bug，内存中的随机字节被间歇性地更改。这是一个相当大的项目，起初我不知道去哪里找它。但是通过去除一些东西来简化系统，我能够一点点地找出它的来源。结果是一个寄存器被一个定时器以随机的方式每秒钟踩一次。

Tip: I often use the undo/redo feature in Notepad++ for this cutting because then once I've found my problem it's easy to restore the text to what it was. First I mark and copy the fix. Then undo. Then paste the fix in at the end. It's easy to cut 1000 lines into 10 and then bring it back to 1000 when you are done. Sometimes I go forward or back as much as 30 or 100 edits. It's just easier than commenting things out to delete them, and later undo the delete.

提示:我经常在记事本++ +中使用撤销/重做功能，因为一旦我发现了问题，就很容易将文本还原为原来的样子。首先我标记并复制修复。然后撤销。然后在末尾粘贴补丁。很容易将1000行削减为10行，然后在完成后将其恢复到1000行。有时我向前或向后进行30或100次编辑。它比注释删除它们更容易，然后撤销删除。

#1