Adding data to MySQL DB with PDO not working

Question

I am trying to re-learn my web development skills for a current application project I am working on. I have verified my PHP back-end is getting the data passed to it from the HTML/JS form on the front-end. But when I submit the form the webpage does not mention any errors. Everything looks fine until I look at the database and see nothing was added. I do not see the echo at the end either that data was successfully added, or that it failed to add data.

register.php

<?php
    include "conn.php";

    if(empty($_POST['firstName']) || empty($_POST['lastName']) || empty($_POST['email']) || empty($_POST['password']) || empty($_POST['pin'])) {
        echo "<br>Error: Please fill out all required fields.";
    } else if($_POST['password'] != $_POST['confirmPassword']) {
        echo "<br>Error: Passwords do not match.";
    } else if($_POST['pin'] != $_POST['confirmPin']) {
        echo "<br>Error: Pin codes do not match.";
    } else if(strlen($_POST['password']) > 20 || strlen($_POST['password']) < 8) {
        echo "<br>Error: Passwords must be between 8 and 20 characters in length.";
    } else if(strlen($_POST['pin']) != 4) {
        echo "<br>Error: Pin codes must be a maximum of 4 characters.";
    } else if(strlen($_POST['firstName']) > 50 && strlen($_POST['lastName']) > 50 && strlen($_POST['email']) > 50) {
        echo "<br>Error: First Name, Last Name, and E-mail fields must be shorter than 50 characters in length.";
    } else if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
        echo "<br>Error: You must use a valid e-mail address.";
    } else {
        echo "<br><br>Successfully checked all data!";

        $options = [
            'cost' => 12,
        ];
        $hash = password_hash($_POST['password'], PASSWORD_BCRYPT, $options);
        $numericPin = (int)$_POST['pin'];
        $currentDateTime = date("Y-m-d H:i:s");

        $stmt = $conn->prepare("INSERT INTO account (firstName, lastName, email, password, pin, registerDate) VALUES (:firstName, :lastName, :email, :password, :pin, :registerDate)");
        $stmt->bindParam(":firstName", $_POST['firstName']);
        $stmt->bindParam(":lastName", $_POST['lastName']);
        $stmt->bindParam(":email", $_POST['email']);
        $stmt->bindParam(":password", $hash);
        $stmt->bindParam(":pin", $numericPin);
        $stmt->bindParam(":registerdate", $currentDateTime);

        if($stmt->execute()) {
            echo "<br><br>Data added successfully";
        } else {
            echo "<br><br>Failed to add data";
        }
    }
?>

MySQL DB Structure: MySQL DB Structure Picture Link

Do you actually see Failed to add data printed? If not, then how do you know the insert failed? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 19, 2018 at 4:24
Because when I look at my database table there are 0 rows in it. But it doesn't say that it failed to add data, nor does it say data added successfully. — Jeremy Voss
– Jeremy Voss, Commented Aug 19, 2018 at 4:28
Then perhaps your code is failing even before it gets as far as the insert call. You should check your connection logic. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 19, 2018 at 4:31
The connection completes successfully. conn.php has an echo in it that successfully passes a check. If I add an echo at the end of the file, it doesn't execute. If I run it just before the if($stmt->execute()) { line that one executes. So it is failing at the execute portion. Is there a way to query the $stmt variable for errors? — Jeremy Voss
– Jeremy Voss, Commented Aug 19, 2018 at 4:35
See here for how to use try catch with PDO...my guess is that you'll see something. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 19, 2018 at 4:37

ArtisticPhoenix · Accepted Answer · 2018-08-21 20:35:46Z

You have a misspelled placeholder

":registerdate"

You have this in the SQL

 $stmt = $conn->prepare("INSERT INTO account (firstName, lastName, email, password, pin, registerDate) VALUES (:firstName, :lastName, :email, :password, :pin, :registerDate)");

Specifically :registerDate vs :registerdate, casing matters here.

A few other things:

change include to require ask youself will the script work without it, well no. Then it needs to be require. Include won't give you an error when it fails to find the file.

Change your validation to help your end users

 $errors = [];
if(empty($_POST['firstName']) || empty($_POST['lastName']) || empty($_POST['email']) || empty($_POST['password']) || empty($_POST['pin']))
    $errors[] ="<br>Error: Please fill out all required fields.";

if($_POST['password'] != $_POST['confirmPassword'])
   $errors[] ="<br>Error: Passwords do not match.";

if($_POST['pin'] != $_POST['confirmPin'])  ....

Then count the array

if(count($errors) > 0 ){
    echo implode("\n", $errors);
}else{
    //--- do insert

}

This way you can show them all the errors at once. Otherwise they have to submit, correct, submit, correct etc etc... This will get very frustrating very quickly and the last thing you want to do is frustrate your users, they may just leave. I have and will leave sites that don't show me the errors right away, I would rather find a site offering what I want that is properly build then deal with one that can't even do proper validation. It makes me wonder what else cant be trusted on the site. (but that is me and I have experience in this thing).

Naming Conventions (opinion): I always name DB fields all lowercase with spaces replaced with _. This reduces a lot of these kinds of issues because, is it registerdate, registerDate or RegisterDate.

There are a few other things related to table names that I do, but I won't bore you with all that.

Cheers!

Jeremy Voss · Accepted Answer · 2018-08-19 05:07:58Z

0

So just to add the answer here, it was simple. I added a try/catch block around where the statement was being executed (and where the script was dying). Found that there was some kind of duplicate placeholder usage or mismatch. Then I double checked my placeholders and found :registerDate was bound as :registerdate. Fixed and boom, adding to database. Thanks all for helping!

answered Aug 19, 2018 at 5:07

Jeremy Voss

432 silver badges9 bronze badges

Collectives™ on Stack Overflow

Adding data to MySQL DB with PDO not working

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related