php, sql, html updating a unique record with corresponding button

Question

I am new to PHP and SQL and trying to figure out how I can make the HTML Approve (submit) button interact specifically with its corresponding record. Currently when the Approve button is clicked, each of the fields are updated, but the top (first) record available is always the one updated. I would like the user to be able to skip the first record and update a different record. Any and all suggestions/help are greatly appreciated.

$conn = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('there was a problem connecting to the database' . mysql_error());

$sql = "SELECT Part, Lot, Qty, AnodTemp, Amp, SealTemp, PerformedBy, DateTimePerformed, FinalAnodThickness, QtyPass, FinalSealCheck, CheckedBy, DateTimeChecked, id  FROM logs";

$result = $conn->query($sql);




if ($result->num_rows > 0)
{
    while($row = $result->fetch_assoc())
{
    $unapproved = $row['CheckedBy'];
    if($unapproved == null)
    {

        echo "<br><br><br> Part: " . $row['Part']. " / Lot: " . $row['Lot']. " / Qty: " . $row['Qty']. " / AnodTemp: " . $row['AnodTemp']. " / Amp: " . $row['Amp']. " / SealTemp: " . $row['SealTemp']. " / PerformedBy: " . $row['PerformedBy']. " / ID: " . $row['id']; ?>

        <form action="adminapproval.php" method="post">
        Final Anod Thickness:<br>
        <input type="text" name="FinalAnodThickness">
        <br><br>
        Qty Pass:<br>
        <input type="text" name="QtyPass">
        <br><br>
        Final Seal Check:<br>
        <input type="text" name="FinalSealCheck">
        <br><br>


        <input type="submit" id="submit" value="Approve" name="submit">
        <br><br>
        </form> 
        _____________________________________________________________________<br>
        <?php

        if (isset($_POST['submit']))
        {

            $FinalAnodThickness= $_POST['FinalAnodThickness'];
            $QtyPass= $_POST['QtyPass'];
            $FinalSealCheck= $_POST['FinalSealCheck'];
            $CheckedBy= $_SESSION['CheckedBy'];
            $id = $row['id'];





            $sql = "UPDATE logs SET FinalAnodThickness = '$FinalAnodThickness', QtyPass = '$QtyPass', FinalSealCheck = '$FinalSealCheck', CheckedBy = '$CheckedBy', DateTimeChecked = now() WHERE id = $id ";
            $conn->query($sql);
            break;
            $conn->close();
            echo "Record Updated.";
            header("Location: adminapproval.php");
         }

       }
   }
}



echo "<br><br> No further items need to be approved at this time.";


?>

You haven't referenced your record ID in your form so it's no wonder no records are being updated properly... What I suggest you do is re-write your code so that on each iteration of your loop you output a form but put the record ID to "submit" as either a hidden INPUT element or as a URL parameter (adminapproval.php?id=2). — Kinnectus
– Kinnectus, Commented Jan 2, 2015 at 15:08
Big Chris, could you provide me an example? I'm a novice when it comes to this stuff and trying to learn. I learn best by taking stuff apart :P when I am able to look at an example so I can see how it's supposed to work. — CoShark
– CoShark, Commented Jan 2, 2015 at 15:20
I'm trying to write an answer but I'm confused at your "CheckedBy" portion... If your DB query returns null for "CheckedBy" do you use the session stored "CheckedBy"? (i.e. hence why you're writing an "adminapproval" mechanism)... — Kinnectus
– Kinnectus, Commented Jan 2, 2015 at 15:32
Yes, the CheckedBy session stores the admin user that logged in. That updates the null value in the DB to the name of the admin user who was logged in, once the approve is clicked. — CoShark
– CoShark, Commented Jan 2, 2015 at 15:43

Kinnectus · Accepted Answer · 2015-01-02 15:46:28Z

1

TWO FILES

adminapproval.php

<?php
session_start();
$conn = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('there was a problem connecting to the database' . mysql_error());
$sql = "SELECT Part, Lot, Qty, AnodTemp, Amp, SealTemp, PerformedBy, DateTimePerformed, FinalAnodThickness, QtyPass, FinalSealCheck, CheckedBy, DateTimeChecked, id  FROM logs";
$result = $conn->query($sql);

if ($result->num_rows > 0){
while($row = $result->fetch_assoc()){
  $unapproved = $row['CheckedBy'];
        if($unapproved == null){
            echo "<br><br><br> Part: " . $row['Part']. " / Lot: " . $row['Lot']. " / Qty: " . $row['Qty']. " / AnodTemp: " . $row['AnodTemp']. " / Amp: " . $row['Amp']. " / SealTemp: " . $row['SealTemp']. " / PerformedBy: " . $row['PerformedBy']. " / ID: " . $row['id']; ?>

            <form action="adminapproval-exec.php?id=<?php echo $row['id']; ?>" method="post">
                <input type="hidden" name="id" value="<?php echo $row['id']; ?>" />
                <input type="hidden" name="checkedby" value="<?php echo $SESSION['CheckedBy']; ?>" />
                Final Anod Thickness:<br>
                <input type="text" name="FinalAnodThickness">
                <br><br>
                Qty Pass:<br>
                <input type="text" name="QtyPass">
                <br><br>
                Final Seal Check:<br>
                <input type="text" name="FinalSealCheck">
                <br><br>
                <input type="submit" id="submit" value="Approve" name="submit">
                <br><br>
            </form>
            <?php
        }
    }
} else {
    echo "<br><br> No further items need to be approved at this time.";
}
?>

adminapproval-exec.php

<?php
session_start();
if (isset($_POST['submit'])){
    $FinalAnodThickness= $_POST['FinalAnodThickness'];
    $QtyPass= $_POST['QtyPass'];
    $FinalSealCheck= $_POST['FinalSealCheck'];
    $CheckedBy= $_POST['CheckedBy'];
    $id = $_GET['id'];
    // OR
    // $id = $_POST['id'];

    $sql = "UPDATE logs SET FinalAnodThickness = '$FinalAnodThickness', QtyPass = '$QtyPass', FinalSealCheck = '$FinalSealCheck', CheckedBy = '$CheckedBy', DateTimeChecked = now() WHERE id = $id ";
    $conn->query($sql);
    $conn->close();
    // echo "Record Updated.";
    header("Location: adminapproval.php");
}
?>

answered Jan 2, 2015 at 15:46

Kinnectus

8997 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CoShark Over a year ago

Big Chris, thanks for the help. I thought I might have to write a separate php file to execute it properly, but I wasn't sure if it was needed, and how exactly I should go about it. Thank you for taking the time to show me. Two thumbs up here!

Kinnectus Over a year ago

You could write a single file as you originally did, but you're unnecessarily mixing your presentation and processing code... Think about MVC (Model, View, Controller) frameworks where all functions are separated into if the code is Model ("business process"), View (pages/forms/output) and Controller (processing between pages/forms/output and business process (database etc.).

magic-sudo · Accepted Answer · 2015-01-02 15:04:00Z

1

<?php
$server = "localhost";
$username = "username";
$password = "password";
$dbname = "db";

$con = mysqli_connect($server, $username, $password, $dbname);
if (!$con) {
    die("Faild: " . mysqli_connect_error());
}

$sql = "UPDATE xxx SET lastname='Jan' WHERE id=2"; // This is importat

if (mysqli_query($con, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($con);
}

mysqli_close($con);
?>

answered Jan 2, 2015 at 15:04

magic-sudo

1,2669 silver badges15 bronze badges

1 Comment

Kinnectus Over a year ago

That won't work because there is a loop involved outputting (potentially) many different "parts"...

DavisBenjaminA · Accepted Answer · 2015-01-02 15:07:46Z

0

        $CheckedBy= $_SESSION['CheckedBy'];
        $id = $row['id'];

Should the row id be coming out of the session as well? If not, then it will always be pointing to the first item in the row.

answered Jan 2, 2015 at 15:07

DavisBenjaminA

11 bronze badge

1 Comment

CoShark Over a year ago

Davis, the row id is not related to the session. To better explain it, a user logs in, sees possibly several records each with their own approve button. The goal is when that approved button is pressed the fields are updated, and the $CheckedBy= $_SESSION['CheckedBy'] is user logged in approving the form which is also added to the record. That session is specific to the page, but each record would have a unique ID so the record is updated. I hope that makes sense.

Collectives™ on Stack Overflow

php, sql, html updating a unique record with corresponding button

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related