Updating SQL with form and PHP. Values resetting to 0 on submit?

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!

on the PHP page, have you tried just echo'ing the $_POST data before updating the SQL table to make sure you are actually receiving the data first? — Xander Luciano
– Xander Luciano, Commented Jan 30, 2016 at 10:50
if I hardcode $currPoints = 5; it echos it out as 5. good. but $currPoints = $currPoints+$addPoints-$remPoints; it says undefined variable $cuurPoiints ... $currPoints = $addPoints-$remPoints; it can echo, but this does not add to $currPoints current value, just replaces it — Joey Thomas
– Joey Thomas, Commented Jan 30, 2016 at 10:54
When I define $cuurPoints I beleive I might have to pull the current data from the database for that variable specificily. But I am not so sure. and not 100% how to do that in conjuction with my current code... — Joey Thomas
– Joey Thomas, Commented Jan 30, 2016 at 11:00
Do you already have a value in column currPoints and you want to add value onto it? If so, perform 2 query, 1st is to get the db value, then do your math and update? — Mark Ng
– Mark Ng, Commented Jan 30, 2016 at 11:12
@MarkNg just does a quick readthrough of the php code it looks like he was getting the value later on in the code. — Xander Luciano
– Xander Luciano, Commented Jan 30, 2016 at 11:18

Xander Luciano · Accepted Answer · 2016-01-30 11:16:57Z

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");

edited Jan 30, 2016 at 11:16

answered Jan 30, 2016 at 11:01

Xander Luciano

3,9037 gold badges35 silver badges56 bronze badges

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

4 Comments

Joey Thomas Over a year ago

Thank you for the swift reply and sorry for my late reply. I have been trying to get this working for some time now... I have adapted your code into what I think should work with mine. I removed !isset($_POST['submit']))) because this seems to echo 'No Data' when the submit button hadn't been pressed? The data should load before the user clicks submit. submit is to update the data. Althought on lines: ' if (!isset($GET['id'])) { echo "No Data!"; return; } ' it is still echoing No Data. So it cannot get the ID anymore? Thank you for your help!

Xander Luciano Over a year ago

since the !isset($_POST['submit'] is returning false, it means you never use actually post a value for 'submit' and I don't think you need the get line either since that is only passed if you put it in the URL, but it looks like you are using all Post data. I think. I'll give it a look tomorrow also to see what you are actually doing. It's 4am here and a little hard to read through and make sense of all the code

Joey Thomas Over a year ago

No worries. Thank you so much for your help up to now. You have been really helpfull. I will keep trying to get this working and let you know. Thanks :)

Joey Thomas Over a year ago

UPDATE: It works! I think I may have misplaced some code when I tried to incorporate your code into mine. Your code works as is. couple of fixes needed tho. -- ' if (!isset($_GET['id'] || !isset($_POST['submit']))) ' should be ' if (!isset($_GET['id']) || !isset($_POST['submit']))' -- missing a ; after the echo directly after. -- Removed $currPoints = 0; as I want to keep the current value and add onto that. Thank you so much you have been a fantastic help. //// EDIT: Althought it still echos "No Data!" So I need to look at that one...

Collectives™ on Stack Overflow

Updating SQL with form and PHP. Values resetting to 0 on submit?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related