为什么此SQL UPDATE查询不能与WHERE变量一起使用? [英] Why does this SQL UPDATE query not work with a variable for WHERE?
问题描述
这是我在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更新了给定的行.第一列是设置为AUTO_INCREMENT的ID.
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语法有错误；在第2行的'('a'，'d')WHERE id = 9'附近使用正确的语法检查与您的MySQL服务器版本相对应的手册
非常感谢您的帮助，对于重复的问题深表歉意，但我确实找不到解决方案，而且非常绝望.
Thanks a lot for your help and sorry for the duplicate question, but I really can't find the solution and am pretty desperate.
以下代码给出此错误:
致命错误:未捕获错误:在/homepages/25/d72758610/htdocs/multimedia/edit.php:30中对bool的成员函数bind_param()进行调用:30堆栈跟踪:/homepages/25中抛出了#0 {main} /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>
这篇关于为什么此SQL UPDATE查询不能与WHERE变量一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
