Problem updating MySQL record using PHP UPDATE, nothing updates

Question

I'm trying to use a form to update records in my database. I've tried using a prepared statement to UPDATE records. It seems like it works but nothing changes in the database. I'm convinced I've missed something.

I have a html table that selects data from a mysql table. The results are printed on that table.

If you click under the column heading 'name' you can see the link is routed to that item's ID.

https://example.com/details.php?id=21

Then, clicking the link the details page loads just fine, along with pulling dynamic data about that record based on it's ID in the database.

I am attempting to use the form found here to update the records in the database. I've tried a ton of different methods but I think the closest is the following... everytime I try to save the record, it seems like it works but nothing actually updates in the database. Here is the PHP code to handle updating:

<?php
require("../const.php");
check_loggedin($con);
$msg = '';

$stmt = $con->prepare('UPDATE sys_commits SET name = ?, description = ?, assignee = ?, length = ?, min_time = ? WHERE id = ?');
$stmt->bind_param('sssssi', $_POST['name'], $_POST['description'], $_POST['assignee'], $_POST['length'], $_POST['min_time'], $c_id);
$stmt->execute();
$stmt->close();

header('Location: ../commitments.php');
?>

Here is the details page:

<?php 
include("const.php");
check_loggedin($con);
$msg = '';
$c_id = $_GET['id'];

if (isset($_GET['id'])) {
    $id = mysqli_real_escape_string($con, $_GET['id']);

    $sql = "SELECT * FROM sys_commits WHERE id = '$id' ";
    $result = $con->query($sql);
    $row = mysqli_fetch_array($result);

    if (isset($_GET['post'])) {
        $name = mysqli_real_escape_string($dbc, $_GET['name']);
        $description = mysqli_real_escape_string($dbc, $_GET['description']);
        $assignee = mysqli_real_escape_string($dbc, $_GET['assignee']);
        $length = mysqli_real_escape_string($dbc, $_GET['length']);
        $min_time = mysqli_real_escape_string($dbc, $_GET['min_time']);
    }
} else {
    header('Location: commitments.php');
}

$title = "Commitments | AA Meeting App";
$description = "Commitment edit page for the AA Meeting App";
require("inc/head.inc");
echo "
<body class='loggedin'>";
include("inc/nav.inc");
echo "
<div id='page-title'>
    <div class='container'>
        <div class='eight columns'>
            <h2>" .$row['name']. " Commitment</h2>
        </div>

        <div class='eight columns'>
            <nav id='breadcrumbs'>
                <ul>
                    <li>Welcome back </li>
                    <li><b>" .$_SESSION['name']. "</b></li>
                </ul>
            </nav>
        </div>
    </div>
</div>";

echo "<div class='container'>
        <h2>Details</h2>
        <form action='actions/update-commit.php' id='commit_details' method='post'>
            <label for='name'>Commitment Name</label>
            <input type='text' name='name' value='" .$row['name']. "'>
            <br>
            
            <label for='name'>Commitment Description</label>    
            <input type='text' name='description' value='" .$row['description']. "'>
            <br>
            
            <label for='name'>Commitment Assignee</label>   
            <input type='text' name='assignee' value='" .$row['assignee']. "'>
            <br>
            
            <label for='name'>Commitment Length</label> 
            <input type='number' name='length' value='" .$row['length']. "'>
            <br>

            <label for='name'>Minimum Time Required</label> 
            <input type='number' name='min_time' value='" .$row['min_time']. "'>
            <br>
            
            <input type='submit' name='update' value='Update' id='update' class='button small yellow'>
        </form>";
echo "
    </div>
</body>
</html>";

?>

I tried to add error_reporting(E_ALL); and ini_set('display_errors', '1'); to the page and try again, but nothing seemed to display. Not sure if I'm doing it wrong? — Andrew Godby
– Andrew Godby, Commented Jul 4, 2021 at 2:15

NachoToast · Accepted Answer · 2022-04-07 23:15:30Z

1

Double check how you're binding parameters in the prepared statements, e.g. your first statement on the updating page has WHERE id = ? but no following $stmt->bind_param('i', $c_id)

Your other statements also have a different number of parameters specified in the statement than in the bind_param args, e.g in

    $stmt = $con->prepare('SELECT * FROM sys_commits WHERE id = ?');
    $stmt->bind_param('ssssss', $c_id, $_POST['name'], $_POST['description'], $_POST['assignee'], $_POST['length'], $_POST['min_time']);

(1 '?' in statement, 6 strings being prepared)

and

        $stmt = $con->prepare('UPDATE sys_commits SET name = ?, description = ?, assignee = ?, length = ?, min_time = ? WHERE id = ?');
        $stmt->bind_param('ssssssi', $_POST['name'], $_POST['description'], $_POST['assignee'], $_POST['length'], $_POST['min_time'], $c_id);

(6 '?' in statement, 6 strings and 1 integer being prepared)

edited Apr 7, 2022 at 23:15

answered Jul 4, 2021 at 2:24

NachoToast

363 bronze badges

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

6 Comments

Andrew Godby Over a year ago

I've updated my original code to try to simplify. The form action is now 'action/update-commit.php'. The code for update-commit.php also has been updated To make it easier to identify problems.

NachoToast Over a year ago

@AndrewGodby are length and min_time stored as strings in the database?

Andrew Godby Over a year ago

They are both stored as int(11)

NachoToast Over a year ago

@AndrewGodby Change the bind_param to bind $_POST['length'] and $_POST['min_time'] as integers instead of strings.

Andrew Godby Over a year ago

I tried ('ssssii') did not seem to have any effect.

$stmt = $con->prepare('UPDATE sys_commits SET name = ?, description = ?, assignee = ?, length = ?, min_time = ? WHERE id = ?'); $stmt->bind_param('ssssii', $_POST['name'], $_POST['description'], $_POST['assignee'], $_POST['length'], $_POST['min_time'], $c_id); $stmt->execute(); $stmt->close();

|

Collectives™ on Stack Overflow

Problem updating MySQL record using PHP UPDATE, nothing updates

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related