PHP MySQL UPDATE statement

Question

I am making a website where after you have logged in and added all your contacts in the database you can also edit them. The way to go is the MYSQL UPDATE statement. I have written the code but sosmething does not seem to work and has been torturing me for hours. Here is the code

<?php
session_start();
    $del_id = $_GET["id"];
    $_SESSION["id"] = $del_id;
    $del_name = $_GET["name"];
    $del_phone = $_GET["phone"];
    $del_address = $_GET["address"];
    $del_email = $_GET["email"];
    $name2 = $_POST["name"];
    $address2 = $_POST["address"];
    $number2 = $_POST["number"];
    $email2 = $_POST["email"];
    $query = "UPDATE `contacts` SET email = '$email2' AND phone = '$number2' AND address = '$address2' AND name = '$name2' WHERE id = '$del_id'";
    $conn  = mysqli_connect($servername,$username,$password,$dbname);
    if(!$conn){
        die("Connection failed: ".mysqli_connect_error());
    }else{
        echo "Connected successfully";
    }  

    if(mysqli_query($conn,$query)){
        echo "Contact edited";    
    }
?>
<html><head></head>
    <body>
   <form action="edit.php" method = "POST">
      Add text only to the ones you want changed:<br><br>
        NAME<input type="text" value="<?php echo $del_name?>" name="name"><br>        
        ADDRESS<input type="text" value="<?php echo $del_address?>" name="address"><br>  
        PHONE NUMBER <input type="text" value="<?php echo $del_phone ?>" name="number"><br>  
        EMAIL <input type="text" value="<?php echo $del_email ?>" name="email"><br>  
      <input type="submit" value="Submit">
    </form>
    </body>
</html>

What could be the problem because the contact in the database is not being updated after that?

That isn't how UPDATE works. See the manual https://dev.mysql.com/doc/refman/5.7/en/update.html - `mysqli_error($conn)` would have told you about it. — Funk Forty Niner, Feb 14 '18 at 20:31
once you've fixed that, get to learning about prepared statements; you're open to an injection. — Funk Forty Niner, Feb 14 '18 at 20:32
Not your answer, but very important: Your code is vulnerable to [SQL Injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). Please fix it by using [prepared statements](http://php.net/manual/pt_BR/mysqli.quickstart.prepared-statements.php) — Elias Soares, Feb 14 '18 at 20:46

John Zenith · Answer 1 · 2018-02-18T12:32:42.757

Your UPDATE statement is wrong:

    
    "UPDATE `contacts` SET email = '$email2' AND phone = '$number2' AND address = '$address2' AND name = '$name2' WHERE id = '$del_id'"

Try this instead

// Please sanitize the data
$email2 = filter_var( $email2, FILTER_SANITIZE_EMAIL );
$name2 = preg_replace( "#[^a-zA-Z ]#", '', $name2 );
$number2 = preg_replace( "#[^0-9 \-\+]#", '', $number2 );
$address = preg_replace( "[^\w \.\-\+]#", '', $address2 );

"UPDATE `contacts` SET email = '$email2', phone = '$number2', address = '$address2', name = '$name2' WHERE id = '$del_id' LIMIT 1"

Note

I added the limit clause LIMIT 1 to limit the number of rows that will be affected by the update statement. In this case, am setting it to 1 to make sure we're updating a single row. Am sure you would want that also.

* Please, consider using mysqli prepared query or PDO

score -1 · Answer 2 · answered Feb 14 '18 at 20:42

-1

Replace your $query line.

$query = "UPDATE `contacts` 
          SET email = '$email2', phone = '$number2', address = '$address2', name = '$name2' 
          WHERE id = '$del_id'";

AND can be used in WHERE clause.

answered Feb 14 '18 at 20:42

Naveed Ramzan

3,565
3
25
30

PHP MySQL UPDATE statement

2 Answers2

Try this instead

Note