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, Aug 19 '18 at 04: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, Aug 19 '18 at 04: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, Aug 19 '18 at 04: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, Aug 19 '18 at 04:35
[See here](https://stackoverflow.com/questions/272203/pdo-try-catch-usage-in-functions) for how to use try catch with PDO...my guess is that you'll see something. — Tim Biegeleisen, Aug 19 '18 at 04:37
The validation is ugly, what I mean by that is that if a user has 3 errors you only show him one. It's better to check individually build an array of errors and show them everything they did wrong. Instead of making them resubmit the error one by one by one and so on. — ArtisticPhoenix, Aug 19 '18 at 04:41
Oh and change this `include "conn.php";` to `require "conn.php";` everyone uses include when the script won't work without that file. With include it fails silently, instead make it required because that is what it is. Then if it fails you wont waste time debugging it, when it's just a wrong path. Id say this is 78% of probability that this is what is wrong... IF you see this `Successfully checked all data!` — ArtisticPhoenix, Aug 19 '18 at 04:45
And turn on error reporting without that file this `$stmt->prepare` turns into calling function of non-object error, which probably you can't see, so everything works up to there and then it just dies silently. — ArtisticPhoenix, Aug 19 '18 at 04:51
Good call, sorry I am still learning. So it says SQLSTATE[HY093]: Invalid parameter number: parameter was not defined. That is occuring when I try to execute the statement. I looked it up and apparently it's a common error given when you use a placeholder more than once? Or something like that, but I don't see that I am other than to bind param. — Jeremy Voss, Aug 19 '18 at 04:53
I got it, thank you all! It was where I was binding the registerDate, the placeholder in the query and the placeholder in the bind were mismatched. Also changed from include to require (thank you!) and that try / catch strategy worked wonders. My last step is to implement AJAX which is where I will fix the single error returns (thanks again!)! — Jeremy Voss, Aug 19 '18 at 04:59

ArtisticPhoenix · Accepted Answer · 2018-08-21T20:35:46.903

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!

score 0 · Answer 2 · answered Aug 19 '18 at 05:07

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!

Adding data to MySQL DB with PDO not working

2 Answers2