Update form, then go to next SQL record

[glittergirl]

This is what I am trying to accomplish:

I have a SQL database of 25 records of first names, last names, and email addresses.

I want to go to index.php and see the first record in the pre-populated form with “First Name,” “Last Name,” and “Email.” The first and last name fields are disabled. However, I should be able to edit the email field, if necessary.

I should be able to click the “Submit” button and page refreshes.

I should get a message, such as, “Record updated!” and a pre-populated form with the second record.

I should be able to go to index.php any time and work on records that have not been updated before.

This is what I have so far:

<?php

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}  

$sql = "SELECT * FROM form WHERE id = '1000' ";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
  
mysqli_close(); // Closing Connection with Server
?>

<form action="index.php" method="post">

<h2>Form</h2>
<label>ID:</label>
<input class="input" name="id" type="text" value="<?php echo $row["id"]?>" disabled><br>
<label>First Name:</label>
<input class="input" name="first_name" type="text" value="<?php echo $row["first_name"]?>" disabled><br>
<label>Last Name:</label>
<input class="input" name="last_name" type="text" value="<?php echo $row["last_name"]?>" disabled><br>
<input class="input" name="Email" type="text" value="<?php echo $row["Email"]?>" style="width: 50%;"><br><br>
<input class="submit" name="submit" type="submit" value="Submit">
</form>

This is the part that I’m struggling with and believe is causing me problems:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

if(isset($_POST['submit']))
{ 
$id = $_POST['id'];
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$email = mysqli_real_escape_string($conn,$_POST['email']);

if($Transcription !=''){
        mysqli_query($conn, "UPDATE form SET first_name=$first_name, last_name=$last_name, email=$email WHERE id='$id'");
    }
    echo "Updated!";
}

mysqli_close(); // Closing Connection with Server
?>

[Wava66]

HTTP is a stateless protocol. So once the page is displayed in your browser PHP has forgotten all about your last retrieval. You need to update your SELECT so that it is using the next ID number. I guess for the next record you could select records from your table with an id > the last id #. Since you are using a MySQL database you could use the LIMIT 1 on the SELECT statement so that you are not retrieving multiple records.

I would change the SELECT statement to use a parameter for the actual id #. Then use the previous $id to do the selection. Using this logic, the first time the page loads the $id will be zero so that the above logic would still work. make sure that you cast it to an int so that it is actually a zero and not a blank.

If this is for not for a class I would also parameterize the SELECT and UPDATE statement to make it safer. if this is for class ignore what I said and do it like the teacher has taught you.

[astonecipher]

A few issues for you to plan out. How will you know what was and wasn’t updated? The way this would typically work is, you give an endpoint url that would direct you to a specific person. Something like,

/member/1

would display the first person. Right now, you will ALWAYS get the same person, if they exist. Now, you could go with your idea on how to do this, but your query would need to change and you would have to have a column that relates to the table to tell you if that record has been updated. And even then, once it was submitted, you wouldn’t be able to go back to it.