Php pdo Updating a record

Hello ppl, I’m trying to make and update for the first time but I just can’t get this right. I get my variables from a json(userId, firstname, lastname, email) and I put them in my database, with two more tabs that are darktheme, and agreement and some default values.
I made a small profile on the site where you can see your data.
I’ve added two small checkboxes in that profile so you can change the color of the website, and and something to agree or not.

I just want to update the values of the checkboxes, So been watching some videos, reading and some copy pasting but I just can’t get it to work.

include_once 'connection.php';

if(isset($_POST['update']))
{
	$userId = $_POST['userId'];
	$useremail = $_POST['useremail'];
	$userfirstname = $_POST['userfirstname'];
	$userlastname = $_POST['userlastname'];
    
    if(empty($_POST['darktheme'])){
        $darktheme = "unchecked";
        } else {
        $darktheme = "checked";
        }
    if(empty($_POST['agreement'])){
        $agreement = "unchecked";
        } else {
        $agreement = "checked";
    }

$query = "UPDATE users SET useremail=:useremail, userfirstname=:userfirstname, userlastname=:userlastname, darktheme=:darktheme, agreement=:agreement WHERE userId=:userId";
$query_run = $pdo->prepare($query);
$query_exec = $query_run->execute(array(":useremail"=>$useremail, ":userfirstname"=>$userfirstname, ":userlastname"=>$userlastname, ":darktheme"=>$darktheme, ":agreement"=>$agreement, ":userId"=>$userId));
        echo "Updated";
    } else {
        echo "Error";
    }

I added those “if()” under darktheme and agreement, thinking that later on when you go to your profile it will show the tilt on the checkbox or not, so you can change them or what ever.

Define that. It must have done something that you observed that leads you to believe it didn’t work? Are you getting php errors? Are you getting Sql errors? Do you have php’s error_reporting set to E_ALL and display_errors set to ON, so that php would help you by reporting and displaying all the errors it detects? Have you set the PDO error mode to use exceptions so that sql errors will get displayed/logged the same as php errors?

What is the actual markup for the checkboxes? It matters, since even when checked, the value attribute could cause them to be considered to be empty(). You should actually only care if checkboxes are set or not set, using isset().

If the form uses checkboxes to turn the dark theme on or off, checkboxes are a little different to other types of input and therefore have to be treated a little differently.

For most other types of input, once you have established that the request is a post request using the standard check…

if($_SERVER['REQUEST_METHOD'] === 'POST'){
 // Parse form data...
}

…you can assume that all inputs in the form are set; except for checkboxes.
Checkboxes are only set in $_POST when they are checked, if unchecked they are absent.
This means that if(empty($_POST['darktheme'])){} on an unchecked box may result in an undefined key error.
So for checkboxes it may be better to use isset() which is largely redundant for other input types, because they likely will be set, even if it’s an empty string.

I gave everyone default value for those tabs darktheme = "unchecked " and agreement = "unchecked "

I did that before they enter to this small interface, when they enter if they are in database it just reads the values on those tabs. If your not in database then it adds you.

include_once "connection.php";

$query = $pdo->prepare("SELECT * FROM users WHERE userId = ?");
$query->execute([$userId]);
$userData = $query->fetchAll();
$result = $query->rowCount();

if($result > 0){
  //echo '<pre>'; print_r($userData[0]['agreement']); echo '</pre>';
  $darktheme = $userData[0]['darktheme'];
  $agreement = $userData[0]['agreement'];
  $_SESSION['darktheme'] = $darktheme;
  $_SESSION['agreement'] = $agreement;
} else {
  $darktheme = "unchecked";
  $agreement = "unchecked";
  $_SESSION['darktheme'] = $darktheme;
  $_SESSION['agreement'] = $agreement;

  $sql_add = 'INSERT INTO users (userId,useremail,firstname,lastname,darktheme,agreement) VALUES (?,?,?,?,?,?)';
  $stmt_add = $pdo->prepare($sql_add);
  $stmt_add->execute(array($userId,$useremail,$userfirstname,$userlastname,$darktheme,$agreement));
}

So now you got like a small profile build up, and if you go to yours you can see some really silly info of yours and change the colors with the checkboxes.

All turn on but don’t quite know where do I check for errors?, I just get blank screen with the echo “Error” from the else in the UPDATE, and if I go to database I see values still says “unchecked” even if I tilt the boxes and save them;

They unchecked since I’ve added <?php echo $darktheme ?> and <?php echo $agreement ?> in the html inputs.

And if I print those variables it shows the value from database “unchecked”.

Ok I think I understand what you say, its checking for empty but they are not empty since I already gave values before in the database. I’m assuming that.

Be sure that you enable exceptions for PDO


$pdo = new PDO('mysql:host=localhost;dbname=someDatabase', 'username', 'password', array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

And cover your PDO commands be a

Try
{
     …. All your PDO code here …
}
catch(PDOException $e)
{
     print($e->getMessage());
}

This will exclude any errors on the database queries

1 Like

That would depend on whether you are logging or displaying errors.
Logging is usually done on a live server where users (the public) should not see the errors, but the dev can check the log file.
Displaying errors is usually done during development (on a local dev environment), so the dev can instantly see an error as it occurs.

No, an unchecked checkbox will be absent from the $_POST array. I know it’s quite confusing as most inputs don’t work that way, but that’s how it is for checkboxes.

1 Like

That means that if(isset($_POST[‘update’])) was false. Your update php code isn’t even being executed, because your form isn’t setting the $_POST[‘update’].

You can examine what the post data is by adding the following near the top of your php code-

echo '<pre>'; print_r($_POST); echo '</pre>';

If you want us to help determine why that value doesn’t exist in the form data, you will need to post your form code.

Your post method form processing code should -

  1. Detect if a post method form has been submitted. Do not attempt to detect if the submit button is set because there are cases where it won’t be.
  2. Keep the form data as a set in an array variable. Don’t copy variables to other variables for nothing.
  3. Trim all the input data at once. After you do item #2 on this list, you can accomplish this using one single php statement.
  4. Validate the input data, storing user/validation errors in an array using the field name as the array index.
  5. After the end of the validation logic, if there are no errors (the array will be empty), use the submitted form data.
  6. Because this update query could result in duplicate data (the useremail) that column must be defined as a unique in index and you must have error handling for the execution of this query that detects if a duplicate index error occurs.
2 Likes
Array
(
    [darktheme] => on
    [agreement] => on
    [update] => Save settings
)

and the form:

<form class="forms" action="#" method="post" enctype="multipart/form-data">
    <h5 class="mt-2 fw-bold">General Settings</h5>
    <h6 class="mt-4 fw-bold">Darktheme: <input name="darktheme" type="checkbox" name="" class="form-check-input" <?php echo $darktheme; ?>></h6>
    <hr />
    <h5 class="fw-bold">User Access Request:</h5>
    <h6 class="mt-4 fw-bold">Multiple user role: <input name="agreement" type="checkbox" name="" class="form-check-input" <?php echo $agreement; ?>></h6>
    <div class="d-flex justify-content-end"><input class="btn btn-success btnrad fw-bold" type="submit" name="update" value="Save settings"/></div>
</form>

I will read what you posted of post methods, some of them I don’t quite understand but will search and try to reorder my code. Thanks

If the logic in the first posted code is accurate and you were getting the “Error” output and are also getting that print_r() output, it’s likely that you are seeing the result of two requests to the page. Are you using ajax to make the request and/or doing a redirect to the same page?

At this point, due to the conflicting results, it would require having all the code needed to reproduce the problem in order to help.

In your first post, your code is using several form fields - userId, useremail, userfirstname, userlastname - which do not appear in your update form. Or did you truncate the update form in post #10 just to make things clearer?

Hello, because I don’t want user to change those values. Just the last two values. So i made the form smaller so they can only change those two.

Why not make the inputs readonly?

1 Like

As you use the UserId in the query, though, you’ll have to provide that somehow. I’d use a hidden field myself.

1 Like

ok, so I changed the code a bit and also added these fields to the form but hidden. And now it works ok. Thanks.

This code is allowing a user to edit THEIR profile information. The user id should come from your code’s login session variable, not though the form, since this will allow anyone to edit anyone else’s profile simply by manipulating the data that is submitted to your site. External data submitted to your site can come from anywhere, can be anything, and cannot be trusted.

2 Likes

Thank you, will take this in note and do it so no one can play around with others ppl profiles.