使用表单和PHP更新SQL。 提交时值重置为0？

Question

I am attempting to create a simple form that updates a row in a MYSQL database based on what ID the row is.

I have managed to get the form and updating values working, but for one of my variables I need its new value to be added to it, based on the values of two other variables. (So like $currPoints = $currPoints+$addPoints-$remPoints;).

The problem I am facing is that whenever the form is submitted, $currPoints is either resetting to 0, then adding and subtracting the other values, or the value of $cuurPoints isn't being found so that it cannot add to it's original value.

I am not sure where specifically in my code I am going wrong so I will paste the whole page if that is okay!

My form function. This get's called on page load:

// creates the form
function renderForm($name = '', $currPoints = '', $addPoints = '', $remPoints = '', $reason = '', $error = '', $id = '')
{ ?>

<title>
<?php if ($id != '') { echo "Edit Punk"; } else { echo "New Punk"; } ?>
</title>

<h1><?php if ($id != '') { echo "Edit Punk"; } else { echo "New Punk"; } ?></h1>
<?php if ($error != '') {
echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
. "</div>";
} ?>

<form name="pointsForm" action="" method="post" style="margin-top:50px;">
<?php if ($id != '') { ?>
<input type="hidden" name="id" value="<?php echo $id; ?>" />
<p>Name: <?php echo $name; ?> / <?php echo $currPoints; ?></p>
<?php } ?>
    <input type="number" name="addPoints" placeholder="Add Punk Points">
    <input type="number" name="remPoints" placeholder="Remove Punk Points">
    <input type="text" name="reason" placeholder="Reason">
    <input type="submit" name="submit" value="Update Punk Points">
</form>


</body>
</html>

<script>
$(function() {
    $('form[name="pointsForm"]').submit(function(e) {
        var reason = $('form[name="pointsForm"] input[name="reason"]').val();
        if ( reason == '') {
            e.preventDefault();
            window.alert("Enter a reason, fool!")
        }
    });
});
</script>


<?php
}

Then my PHP for editing a record:

Where I get the variables from the URL/form I have added $currPoints = $currPoints+$addPoints-$remPoints;

Then on my bind_param is just add $currPoints.

I believe I am going wrong somewhere around these lines... or where I SET currPoints = ? . should that be something else?

Forgive me I am just learning PHP.

/*

EDIT RECORD

*/
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$addPoints = htmlentities($_POST['addPoints'], ENT_QUOTES);
$remPoints = htmlentities($_POST['remPoints'], ENT_QUOTES);
$reason = htmlentities($_POST['reason'], ENT_QUOTES);
$currPoints = $currPoints+$addPoints-$remPoints;


// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE points SET currPoints = ? , addPoints = ?, remPoints = ?, reason = ?
WHERE id=?"))
{
$stmt->bind_param("iiisi", $currPoints, $addPoints, $remPoints, $reason, $id);
$stmt->execute();
$stmt->close();
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}

// redirect the user once the form is updated
header("Location: index.php");

}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];

// get the record from the database
if($stmt = $mysqli->prepare("SELECT * FROM points WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();

$stmt->bind_result($id, $name, $currPoints, $addPoints, $remPoints, $reason, $date);
$stmt->fetch();

// show the form
renderForm($name, $currPoints, $addPoints, $remPoints, $reason, NULL, $id);

$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: index.php");
}
}
}


?>

Sorry If I have been too vague. Please let me know if you need more information.

Thank you!

Answer 1

Oh found the error I think, you are never defining $currPoints before you try and use it, so you can't have $currPoints = $currPoints+.. because it isn't created yet. PHP more or less so will read line by line, so you have to query the SQL table and set $currPoints equal to the value from your database before you do $currPoints = $currPoints+$addPoints-$remPoints;

Ok, this probably won't work, but you should be able to figure out what I changed and adapt your code to work with it. I wouldn't say it's the 'proper' way, but it is a little easier to read and see what the code is doing when you have the if statements at the top to deal with what data is submitted vs not submitted.

if (!isset($_GET['id'] || !isset($_POST['submit'])))
{
    echo "No Data!"
    return;
}
if (!is_numeric($_POST['id']))
{
    echo "Invalid ID!";
    header("Location: index.php");
    return;
}

// get variables from the URL/form
$id = $_POST['id'];
$addPoints = htmlentities($_POST['addPoints'], ENT_QUOTES);
$remPoints = htmlentities($_POST['remPoints'], ENT_QUOTES);
$reason = htmlentities($_POST['reason'], ENT_QUOTES);
$currPoints = 0;

//Check what the current points are first
// make sure the 'id' value is valid also
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
    // get 'id' from URL
    $id = $_GET['id'];

    // get the record from the database
    if($stmt = $mysqli->prepare("SELECT * FROM points WHERE id=?"))
    {
        $stmt->bind_param("i", $id);
        $stmt->execute();

        $stmt->bind_result($id, $name, $currPoints, $addPoints, $remPoints, $reason, $date);
        $stmt->fetch();

        // show the form
        renderForm($name, $currPoints, $addPoints, $remPoints, $reason, NULL, $id);

        $stmt->close();
    }
    else
        echo "Error: could not prepare SQL statement";
}

//Now update currPoints
$currPoints += $addPoints-$remPoints;


// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE points SET currPoints = ? , addPoints = ?, remPoints = ?, reason = ?
WHERE id=?"))
{
    $stmt->bind_param("iiisi", $currPoints, $addPoints, $remPoints, $reason, $id);
    $stmt->execute();
    $stmt->close();
}
else
    echo "ERROR: could not prepare SQL statement.";

// redirect the user once the form is updated
header("Location: index.php");