为什么此 SQL UPDATE 查询不适用于 WHERE 的变量?

2021-12-25 00:00:00 sql-update sql php mysqli

这是我在 Stack Overflow 上的第一篇文章.我知道这个问题之前已经被问过很多次了.我经历了很多答案,尝试了所有答案(显然正确的方法除外),但不知道该尝试什么了.

this is my first post here at Stack Overflow. I know the question has been asked many times before. I went through many answers, tried all of them (except the correct approach obviously) and don't know what to try anymore.

我有一个 SQL 表,其中每一行都有一个编辑"按钮.单击它时,我将所选行的 id 传递给 edit.php.在那里,我得到它并根据用户从表单输入的 id 更新给定的行.第一列是 id,设置为 AUTO_INCREMENT.

I have an SQL table where every row has an "edit" button. When clicking it, I pass over the id of the selected row to edit.php. There, I get it and update the given row based on the id with the user input from the form. The first column is id which is set to AUTO_INCREMENT.

顺便说一句,无论我使用 WHERE id=$id"; 还是 WHERE id='$id'";

On a side note, I get the same error, no matter if I use WHERE id=$id"; or WHERE id='$id'";

我认为最接近正确方法的代码如下,并在代码下方生成错误消息:

The code which I think is closest to the correct approach is as follows and generates the error message below the code:

<html>
    <title>
        Video Archiv - New
    </title>
    
    <body>
        <?php
            include("connect.php"); 
            $id=$_GET['id'];
            echo "Details von Video #$id editieren:<br /><br />";
            
            if(isset($_POST['update']))
            {
                $sql =  "UPDATE VideoArchiv             
                        SET ('".$_POST["titel"]."','".$_POST["schauspieler"]."')
                        WHERE id=$id";

                        $result = mysqli_query($connect,$sql);

                if (mysqli_query($connect,$sql) === TRUE) 
                {
                    echo "Record updated successfully";
                } 
                else 
                {
                    echo "Error updating record: " . $connect->error;
                }
            }
            ?>

        <form action="edit.php" method="post"> 
            
            <label> Titel:</label><br/>
            <input type="text" name="titel" required><br/>

            <label>Schauspieler</label><br/>
            <input type="text" name="schauspieler" required><br/>
            <br />
            <button type="submit" name="update">Speichern</button>
                
        </form>

        <?php
            include("back.php");
        ?>
    </body>
</html> 

错误信息:

错误更新记录:您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,了解在第 2 行的 '('a','d') WHERE id=9' 附近使用的正确语法

非常感谢您的帮助,对于重复的问题很抱歉,但我真的找不到解决方案并且非常绝望.

Thanks a lot for your help and sorry for the duplicate question, but I really can't find the solution and am pretty desperate.

以下代码给出了这个错误:

The following code gives this error:

致命错误:未捕获的错误:在/homepages/25/d72758610/htdocs/multimedia/edit.php:30 中的 bool 上调用成员函数 bind_param() 堆栈跟踪:#0 {main} throw in/homepages/25/d72758610/htdocs/multimedia/edit.php 第 30 行

Fatal error: Uncaught Error: Call to a member function bind_param() on bool in /homepages/25/d72758610/htdocs/multimedia/edit.php:30 Stack trace: #0 {main} thrown in /homepages/25/d72758610/htdocs/multimedia/edit.php on line 30

<html>
    <title>
        Video Archiv - New
    </title>
    
    <body>
        <?php
            include("connect.php"); 
            $id=$_GET['id'];
            $title = $_POST["titel"];
            $schauspieler = $_POST["schauspieler"];

            if(empty($title))
            {
                echo "error";
            }
            elseif(empty($schauspieler))
            {
                echo "error";
            }
            else
            {
                $sql = "UPDATE users SET title=?, schauspieler=? WHERE id=?";
                $stmt= $connect->prepare($sql);
                $stmt->bind_param("ssi", $title, $schauspieler, $id);
                if($stmt->execute())
                {
                      echo "Succes";
                }
                else
                {
                  echo "something went wromg";
                }
            }
            ?>

        <form action="edit.php" method="post"> 
            
            <label> Titel:</label><br/>
            <input type="text" name="titel" required><br/>

            <label>Schauspieler</label><br/>
            <input type="text" name="schauspieler" required><br/>
            <br />
            <button type="submit" name="update">Speichern</button>
                
        </form>

        <?php
            include("back.php");
        ?>
    </body>
</html> 

推荐答案

非常简单,可以避免 sql 注入并使用最新代码,并且您的 SQL 语法有错误.

Very simple to avoid sql injections and use up to date codes and You have an error in your SQL syntax.

这是一个例子:

   include("connect.php"); 
    $id=$_GET['id'];
    $title = $_POST["titel"];
    $schauspieler = $_POST["schauspieler"];

    if(empty($title)){
    echo "error";
    }elseif(empty($schauspieler)){
    echo "error";
    }else{

    $sql = "UPDATE VideoArchiv SET title=?, schauspieler=? WHERE id=?";
    $stmt= $connect->prepare($sql);
    $stmt->bind_param("ssi", $title, $schauspieler, $id);
    if($stmt->execute()){
      echo "Succes";
    }else{
      echo "something went wromg";
    }

    }

查看更多:https://phpdelusions.net/mysqli_examples/update

更新:第一个代码对你有用,但如果你仍然想使用程序化的方式,那么我们这个:

UPDATE : First code will work for you, but if you still want to use procedural way then us this :

include("connect.php");
if ($_SERVER["REQUEST_METHOD"] == "POST") {

//Check if we get id 
$Testid = $_GET['id'];
if(empty($Testid)){
    echo "id is empty";
}else{
    $id = $_GET['id'];
}


$title = $_POST["titel"];
$schauspieler = $_POST["schauspieler"];

    if(empty($title )){
        echo "error". $title; 
    }elseif(empty($schauspieler)){
        echo "error". $schauspieler;
    }else{
       $sql = "UPDATE VideoArchiv SET title=?, schauspieler=? WHERE id=?";
       $stmt = mysqli_prepare($connect, $sql);
       mysqli_stmt_bind_param($stmt, 'ssi', $title, $schauspieler, $id);
       mysqli_stmt_execute($stmt); 
    }
}

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">

	<label> Titel:</label><br/>
	<input type="text" name="titel" required><br/>

	<label>Schauspieler</label><br/>
	<input type="text" name="schauspieler" required><br/>
	<br />
	<button type="submit" name="update">Speichern</button>

</form>

相关文章