PHP and MYSQL: unable to update records in database.

[Cameron_12]

Hey guys, so I've been trying to create a website that allows users to update and delete records through the database I've set up in SQLyog. In my update confirmation file I've added location theme description to be updated and changed for all users. but for some reason its unable to add new data to that database and does not give me an error so I've got no idea what's wrong with my update php file. Any help will be greatly appreciated

<?php
$title = "Update Confirmation";
include('includes/header.inc');
include('includes/nav.inc');
require_once('includes/config.inc');
require_once('includes/DbController.inc');

echo "<div id='message'>";
$error = false;
if (!empty($_POST['id'])) {
    $db = new DbController(HOST, USER, PASS, DB);

    foreach ($_POST as $key => $val) {
        $$key = trim($val);
    }

    $sql = "UPDATE places SET Location=?, theme=?, description=? WHERE placeid=?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param("sssi", $location, $theme, $description, $id);

    if ($stmt->execute()) {
        echo "<p>Record $id updated</p>";
    } else {
        echo "<p>Error updating record: " . $stmt->error . "</p>";
        $error = true;
    }
} else {
    $error = true;
}

if ($error) {
    echo "<p>Record is NOT updated.</p>";
}

echo "</div>";
include('includes/footer.inc');
?>

[Dani]

Can you try adding a bunch of debug code to this?

Firstly, after line 10, try echo'ing out something like:

echo "ID found: " . $_POST['id'];

Perhaps the entire block is being skipped entirely.

The second thing is on line 11, we set $db = new DbController();. Are we sure that $db is now set to a valid MySQL connection? Where are you setting HOST, UESR, PASS, and DB? I'm not familiar with DbController() as I use PHP's MySQLi library to connect.

The third thing is on line 19, we are passing in $location, $theme, $description, and $id, but where are those variables being created? On line 10 we are passing in an 'id' as a $_POST request, but I don't see anywhere anything like:

// Set the $id variable to the value of $_POST['id'], or to 0 if 'id' was not sent in the HTTP request
$id = $_POST['id'] ?? 0;

[cored0mp]

I agree with what Dani said. Debug code is so easy to write if you're not used to doing that. I used to think it was only for experts, but it can about double your productivity really.

Also, if you try to run the database statements by hand in the sql interpreter, what happens?

One other thing that I noticed is that your code segment here doesn't appear to contain session controls? That should be something you'll want to have early on.

[Dani]

I suspect it's failing because of a 500 error.

Line 14 has $$key instead of $key. That's probably a typo. Also, that loop doesn't do anything since you're not passing the value into the loop by reference. You would want to do foreach ($_POST AS $key => &$val) instead if your goal is to overwrite the values in the $_POST array.

Line 19 is trying to pass variables into the MySQL query that are undefined. What is the value of $location, $theme, etc. meant to be?

[cored0mp]

Also, check if you need to set autocommit. It's been a minute since I've PHP'd.

[AndreRet]

Apart from the other comments and advice above, you are not executing your statement which is why it does not add any new data, you are using an if statement to say the record update was successful or failed but you did not execute it hence no error and of course no data -

$sql = "UPDATE places SET Location=?, theme=?, description=? WHERE placeid=?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param("sssi", $location, $theme, $description, $id);
//No execution of the record...
$stmt->execute();

    if ($stmt->execute()) {
        echo "<p>Record $id updated</p>";
    } else {
        echo "<p>Error updating record: " . $stmt->error . "</p>";
        $error = true;
    }

PHP PDO Statement Execute