Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
4.0k views
in Technique[技术] by (71.8m points)

php - Why does this SQL UPDATE query not work with a variable for WHERE?

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.

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.

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> 

Error message:

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('a','d') WHERE id=9' at line 2

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

UPDATE:

The following code gives this error:

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> 
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

Here is an example :

   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";
    }

    }

See more on : 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>

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...