PDO INSERT statement silently failing

Question

In my test environment, which is MAMP 4.5, the statement works perfectly with no issues. But when I use the same statement on my test VM the INSERT fails, without error.

I am using this to catch any errors:

try {
        // http://php.net/manual/en/pdo.connections.php
        $dbConn = new PDO("mysql:host={$databaseHost};dbname={$databaseName}", $databaseUsername, $databasePassword);

        $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting Error Mode as Exception
        // More on setAttribute: http://php.net/manual/en/pdo.setattribute.php
    } catch(PDOException $e) {
        echo $e->getMessage();
    }

And here is my INSERT statement:

<?php
    // Database connection file
    include_once("../config.php");

    if(isset($_POST['Submit'])) {
        $status=$_POST['status'];
        $system=$_POST['system'];
        $first_name=$_POST['first_name'];
        $last_name=$_POST['last_name'];
        $primary_num=$_POST['primary_num'];
        
        // Insert data into table
        $sql = "INSERT INTO table(
            status,
            system,
            first_name,
            last_name,
            primary_num
        ) VALUES(
            :status,
            :system,
            :first_name,
            :last_name,
            :primary_num
        )";

        $query = $dbConn->prepare($sql);
        
        $query->bindParam(':status', $status);
        $query->bindParam(':system', $system);
        $query->bindParam(':first_name', $first_name);
        $query->bindParam(':last_name', $last_name);
        $query->bindParam(':primary_num', $primary_num);

        $query->execute();

        // Redirect to the display page
        header("Location: index.php");

    }
?>

Fix:

I set the default values for each column in my database (except the Primary Key) to be NULL and now the data posts successfully!

Possible duplicate of [Why is my PDO not working?](https://stackoverflow.com/questions/10640648/why-is-my-pdo-not-working) — Masivuye Cokile, Jun 12 '18 at 12:50
I am able to connect to my database, since I am able to retrieve/edit data already in the database, it is just my INSERT that is failing. — Anthony Vadala, Jun 12 '18 at 13:15

rideron89 · Accepted Answer · 2018-06-12T14:18:20.897

3

You could try and print internal error information, in case exceptions aren't actually being thrown:

// http://php.net/manual/en/pdostatement.errorinfo.php
$error = $query->errorInfo();

// see if anything is output here
print_r($error);

You also mentioned in a comment that is was erroring out before the execute() call. It might be because you are using all lowercase names for bindparam().

http://php.net/manual/en/pdostatement.bindparam.php

Try changing your parameter binding to this:

$query->bindParam(':status', $status);
$query->bindParam(':system', $system);
$query->bindParam(':first_name', $first_name);
$query->bindParam(':last_name', $last_name);
$query->bindParam(':primary_num', $primary_num);

Or better yet; remove the above lines and set them in the execute() call:

$query->execute([
    'status'      => $status,
    'first_name'  => $first_name,
    'last_name'   => $last_name,
    'primary_num' => $primary_num,
]);

If the above information doesn't help, can you make sure you have error reporting enabled? Taken from the instructions on this answer, you can do so like this:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

edited Jun 12 '18 at 14:18

answered Jun 12 '18 at 13:45

rideron89

491
3
14

Ok, I got this as an output `Array ( [0] => [1] => [2] => )` – Anthony Vadala Jun 12 '18 at 13:53
Ok, so no error. I updated my answer with some more things to try if you want to test them out. – rideron89 Jun 12 '18 at 14:02
I tried both methods, it is still failing in the same place for both, updated original post to reflect the change. – Anthony Vadala Jun 12 '18 at 14:06
Next thing I'd try is to make sure all PHP errors are being shown. And check the errors logs in case PHP is giving a warning somewhere. – rideron89 Jun 12 '18 at 14:19
Finally getting somewhere! `Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1364 Field 'first_name' doesn't have a default value` – Anthony Vadala Jun 12 '18 at 14:34
1

I set the default values for each column (except the Primary Key) to be NULL and now the data posts successfully! Any ideas to why I could post to the same database from one location, but not the other? – Anthony Vadala Jun 12 '18 at 14:39
Good news! But I don't know why it wouldn't work on both if you are using the same database for both. Could it be that you were giving a first name on the old server, but you are not giving one this time around? – rideron89 Jun 12 '18 at 14:53
I was attempting to POST test data in all fields, so that shouldn't be the case. Either way thank you for the help! – Anthony Vadala Jun 12 '18 at 15:03
@AnthonyVadala I was having this same issue until I manually set all fields to null except Primary Key. This was helpful 3 years later! – CJT3 May 27 '21 at 23:27

score 0 · Answer 2 · answered Jun 12 '18 at 14:36

0

Remove : in bindParam

$query->bindParam('status', $status); $query->bindParam('system', $system); $query->bindParam('first_name', $first_name); $query->bindParam('last_name', $last_name); $query->bindParam('primary_num', $primary_num);

answered Jun 12 '18 at 14:36

Mahmoud Gamal

322
4
10

score -1 · Answer 3 · answered Jun 12 '18 at 13:21

-1

have you tried to use $GLOBAL['dbConn'] instead of $dbConn .

answered Jun 12 '18 at 13:21

Znar

369
2
11

Yup, seems to be still failing silently. – Anthony Vadala Jun 12 '18 at 13:33
you have to echo some numbers after each block of code to figure out what line is preventing code run – Znar Jun 12 '18 at 13:37
It appears to be failing after `$query = $dbConn->prepare($sql);` but before `$query->execute();` – Anthony Vadala Jun 12 '18 at 13:50
as rideron89 said you are using binparam with lowercase and they should be like this bindParam – Znar Jun 12 '18 at 14:04

PDO INSERT statement silently failing

3 Answers3