Updating records in database using a pre populated form

Question

Looks like I have 1 last issue that I can't solve due to the fact of being too unexperienced with this matter. This last issue that I can't get to work or basically I don't understand the order in how to do this.

Been able to do the following:

Form that writes records to database
Page that shows database records in a table
Added an edit button to the table that takes you to an edit.php page with a form that has all values pre filled.

What I'm trying to get to work now is to edit one of the inputs on the form so it get's updated in the database.

So far I have this on the edit.php page:

<?php

$servername = "localhost";
$username = "";
$password = "";
$dbname = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$sql = "SELECT id, name, email, age FROM members WHERE id =" .$_GET['id'];
$result = mysqli_query($conn,$sql);
if(mysqli_num_rows($result) > 0) {
  $row = mysqli_fetch_assoc($result);
  $id = $row['id'];
  $name = $row['name'];
  $email = $row['email'];
  $age = $row['age'];

?>

<form action=" <?=$_SERVER['PHP_SELF']?> " method="POST">
<div align="center">
<div class="container">

      <div class="row">
      <div class="col-25">
        <label for="#"></label>
      </div>
      <div class="col-75">
        <h2>ID: <?php echo $row['id']; ?></h2>
      </div>
    </div>
    <br>

    <div class="row">
      <div class="col-25">
        <label for="name">Name:</label>
      </div>
      <div class="col-75">
        <input type="text" name="name" value="<?php echo $row['name']; ?>" id="my-input" class="input-res">
      </div>
    </div>

        <div class="row">
      <div class="col-25">
        <label for="email">Email:</label>
      </div>
      <div class="col-75">
        <input type="text" name="email" value="<?php echo $row['email']; ?>" class="input-res">
      </div>
    </div>

    <div class="row">
      <div class="col-25">
        <label for="age">Age:</label>
      </div>
      <div class="col-75">
        <input type="text" name="age" value="<?php echo $row['age']; ?>" class="input-res">
      </div>
    </div>

    <div class="row"><br>
    <input type="submit" name="submit" value="Save updates" class="button">
    </div>


</div> 

</div>   

</form>

</body>

</html>

Have tried adding this code below the form:


<?php
    if(isset($_POST['Submit'])){//if the submit button is clicked


    $sql="UPDATE name, email, age SET name, email, age WHERE name = ".$name.", email = ".$email.", age = ".$age.";
    $conn->query($sql) or die("Cannot update");//update or error
    }
?>

But the the page doesn't work anymore, tried changing from single quotes to double qoutes etc. but no success and a few other solutions (that unfortunatelly didn't work).

Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, May 04 '20 at 11:10
There are many things wrong here. 1. The string with the query isn't closed correctly. 2. The query isn't valid (invalid format) since you never actually set any values. You're also missing quotes around the values in the query. 3. You're injecting variables instead of using prepared statements. 4. Using data that are subject to change in the `WHERE` part. Ex: if the user updates the name, email or age, the query won't find the old record. Just use the `id` instead (which should be persistent and never updated) — M. Eriksson, May 04 '20 at 11:28

score 0 · Answer 1 · edited May 04 '20 at 15:35

0

Need $_POST to get posted value
Use prepare for security

note: die is a wrong idea here

Correct code will be:

<?php
if (isset($_POST['Submit'],$_POST['name'],$_POST['email'],$_POST['age'],$_GET['id'])) { //if the submit button is clicked

    $stmt = $conn->prepare('UPDATE name, email, age SET name = ?, email = ?, age = ? WHERE id=?');
    $stmt->bind_param('ssii', $_POST['name'], $_POST['email'], $_POST['age'], $_GET['id']);
    $stmt->execute();
    echo "Updated successfully"; // Updated Successfully
}

edited May 04 '20 at 15:35

Dharman

30,962
25
85
135

answered May 04 '20 at 15:16

MILAN SAHANA

76
11

1

I removed the try-catch because it was pointless and it would never work. – Dharman May 04 '20 at 15:36

Updating records in database using a pre populated form

1 Answers1