Update record via form, then go to next 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
?>

 

[requinix]

I thought you didn't want to edit the first or last name? Just the email. So don't do anything with them in the form (besides showing the values) or in the PHP code.

First a question: how do you know which records have been updated? Not counting you remembering what you've done and haven't, how do you look in the database and tell what still needs to be updated?

Because this problem will be easier to tackle if you think of it not as going to the next record but as going to the first record that hasn't been updated yet.

[glittergirl]

39 minutes ago, requinix said:

I thought you didn't want to edit the first or last name? Just the email. So don't do anything with them in the form (besides showing the values) or in the PHP code.

First a question: how do you know which records have been updated? Not counting you remembering what you've done and haven't, how do you look in the database and tell what still needs to be updated?

Because this problem will be easier to tackle if you think of it not as going to the next record but as going to the first record that hasn't been updated yet.

To answer your question, I suppose I can create a field called "Post" in the database that only gets populated when the form associated with the ID gets updated. It doesn't really matter what's in there, it's just a way for me to keep track what has and has not been updated. Is there a better way to do this?

<?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 post is NULL";
$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>Post:</label>
<input class="input" name="post" type="hidden" value="<?php echo(rand(1000,9999));?>" 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>

As for not updating the first and last name fields, I've edited the code below. I added the "post" field:

<?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'];
$post = $_POST['post'];
$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 post=$post, email=$email WHERE id='$id'");
    }
    echo "Updated!";
}

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

 

[Barand]

"disabled" inputs are not posted. If you must show the current values in input fields, and not just as plain text, use "readonly".

Why is the hidden "post" field in the form? That value can be set when you are doing the update. Personally, I would define the "post" column as DATETIME then

mysqli_query($conn, "UPDATE form SET post=NOW(), email='$email' WHERE id=$id');

Note that string valuess (like $email) should be inside quotes and numeric values (like $id) should not.

However, you not put user-provided data into queries - use prepared statements and pass the values as parameters.

$stmt = $conn->prepare("UPDATE form SET post=NOW(), email=? WHERE id=? ");
$stmt->bind_param('si', $email, $id);
$stmt->execute();

You should turn on PHP's error reporting.

Before connecting using mysqli, call

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

so that any mysql errors are reported

[requinix]

3 hours ago, glittergirl said:

Is there a better way to do this?

I would use a simple flag (TINYINT) column that indicates whether it's "updated" or not. Default 0. Your form picks the first one, sorted by whatever criteria you want (or even none at all), then the code sets the flag when it saves the data.

[glittergirl]

Thank you so much for the help! I really appreciate it.

I am currently stuck at how to indicate to the user that they have finished going through the entire table (there are no more records for them to view). I was hoping index.php can show "No more records" or something of that nature. I added a line that refreshes the page when the user clicks "submit," so the user can move on to the next record.

Based on your recommendations, here is my edited code:

<?php
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$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 post is NULL";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$date = date_create('2000-01-01');
  
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"]?>" readonly><br>
<label>Post:</label>
<input class="input" name="post" type="hidden" value="<?php echo date_format($date, 'Y-m-d H:i:s');?>" readonly><br>
<label>First Name:</label>
<input class="input" name="first_name" type="text" value="<?php echo $row["first_name"]?>" readonly><br>
<label>Last Name:</label>
<input class="input" name="last_name" type="text" value="<?php echo $row["last_name"]?>" readonly><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>

<?php
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$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'];
$post = $_POST['post'];
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$email = mysqli_real_escape_string($conn,$_POST['email']);
header("Location: index.php");

if($Transcription !=''){
        $stmt = $conn->prepare("UPDATE form SET post=NOW(), email=? WHERE id=? ");
$stmt->bind_param('si', $email, $id);
$stmt->execute();
    }
    echo "Updated!";
}

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

[requinix]

I would make the form post back to itself, not to a new file. Then it's easy.

One thing you should change: your UPDATE query so that it only updates the record if it still needs updating. Meaning WHERE id=? AND post IS NULL. It'll prevent someone from somehow accidentally updating a record that was already updated.

And another small thing: when you're SELECTing the next record to show, if you only want one record then your query should only get one record (with a LIMIT 1). Because retrieving everything available is wasteful.

[glittergirl]

8 minutes ago, requinix said:

I would make the form post back to itself, not to a new file. Then it's easy.

One thing you should change: your UPDATE query so that it only updates the record if it still needs updating. Meaning WHERE id=? AND post IS NULL. It'll prevent someone from somehow accidentally updating a record that was already updated.

And another small thing: when you're SELECTing the next record to show, if you only want one record then your query should only get one record (with a LIMIT 1). Because retrieving everything available is wasteful.

I'm still not sure how to post "No more records" when it's done. I'm thinking of adding another if statement, but I'm not sure what to put in it:

if($id =''){
//show record
    }
else{
//display "You're done!!"
}
}

I've edited the code according to your suggestions.

Added LIMIT 1 in SELECT:

<?php
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$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 post is NULL LIMIT 1";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$date = date_create('2000-01-01');
  
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"]?>" readonly><br>
<label>Post:</label>
<input class="input" name="post" type="hidden" value="<?php echo date_format($date, 'Y-m-d H:i:s');?>" readonly><br>
<label>First Name:</label>
<input class="input" name="first_name" type="text" value="<?php echo $row["first_name"]?>" readonly><br>
<label>Last Name:</label>
<input class="input" name="last_name" type="text" value="<?php echo $row["last_name"]?>" readonly><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>

Updated the UPDATE line:

 

<?php
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$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'];
$post = $_POST['post'];
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$email = mysqli_real_escape_string($conn,$_POST['email']);
header("Location: index.php");

if($Transcription !=''){
        $stmt = $conn->prepare("UPDATE form SET post=NOW(), email=? WHERE id=? AND post IS NULL");
$stmt->bind_param('si', $email, $id);
$stmt->execute();
    }
    echo "Updated!";
}

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

[mac_gyver]

On 9/25/2018 at 8:56 PM, glittergirl said:

I should be able to edit the email field, if necessary.

this sounds like a typical CRUD assignment and literally 'go to the next record' isn't exactly what you want because you would not  be frequently entering/editing all the records in turn. don't you really want to be able to enter/edit the email address for specific/selected record(s) and you want to be able to easily go to or select the records where an email address needs to be entered/edited?

if so, what you need to do is come up with a User Interface (UI) that supports what you are doing. you can do this at least four different ways -

1) display a page of the records inside a single form, where you would enter/edit any amount of email addresses at one time, then submit the form and UPDATE all the records. an enhancement to this would be to either have a check-box or use javascript to detect a change in the email field and set a value in a hidden field, for each record that indicates that the record has been changed. the form processing code in this case would use the check-box/hidden field and only UPDATE the records that have been changed.

2) display a page of the records with each as a separate form. you would enter/edit an email address and click the submit button for that record. the page would re-display after updating that record and you can enter/edit more email addresses.

3) display a page of the records with an edit link next to each one, containing the record id. when the edit link is clicked, you would retrieve the data for that record and populate a form with the existing data. when the form is submitted, you would UPDATE just the record for that id.

4) a mix of method #1 and #3. display a page of the records with an edit check-box next to each one, inside of a single form. check the boxes for the records you want to edit and submit the form. the next page would retrieve the data for all the records that were checked and dynamically output a single form with a field for each record. the single form would be submitted and processed as in method #1.

the method you choose depends on how many email addresses would typically be entered/edited at one time, to provide the simplest user interface (fewest number of clicks with the smallest chance of putting data into the wrong record), and per what your assignment is to use. method #2 is probably the best from a user standpoint - identify and click on a record you want to edit, enter/edit just one email address at a time, click the submit button next to the email form field, deal with any errors from that submission, repeat for the next email address to be entered/edited.