Problem with PHP INSERT in to MySQL database

Question

I am hoping that I've just been looking at and debugging this code too long (days now!) and I'm just not seeing the problem.

I'm obviously trying to add an entry in to a MySQL database via my PHP code. To use the classic phrase "this code has always worked before and now it doesn't and I didn't change anything" ;-)

My code, with my current debugging traps, looks like this:

        // Prepare SQL Insert
    $strInsert = "INSERT INTO Horses ( HorseName, HorseYOB, HorseCOB, HorseSex, HorseYOD, HorseDead, FAM, FDM) " .
        "VALUES (:HORSENAME, :HORSEYOB ,:HORSECOB, :HORSESEX, :HORSEYOD, :HORSEDEAD, :FAM, :FDM)";

    $DBInsertHorse = $DB->prepare($strInsert);

    // Insert new Horse
    $iCtr = 0;
    do {
        try {
            $DBInsertHorse->execute(array(
                    'HORSENAME' =>  strtoupper($HorseName),
                    'HORSEYOB'  =>  $YOB,
                    'HORSECOB'  =>  $COB,
                    'HORSESEX'  =>  strtoupper($HorseSex),
                    'HORSEYOD'  =>  $YOD,
                    'HORSEDEAD' =>  $bDead,
                    'FAM'       =>  $FAM,
                    'FDM'       =>  $FDM)
            );
        }
        catch (Exception $error) {
            die($error->getMessage());
        }
    } while ($find($DB, strtoupper($HorseName), $YOB, $COB) == false && ++$iCtr < MAX_INSERT_ATTEMPTS);

    // Could not insert
    if ($iCtr == MAX_INSERT_ATTEMPTS) {

        // DEBUG HORSE IMPORT
        if (is_null($HorseName))
            $HorseName = 'NULL';
        if (is_null($YOB))
            $YOB = -2;
        if (is_null($COB))
            $COB = 'NULL';
        if (is_null($HorseSex))
            $HorseSex = 'NULL';
        if (is_null($YOD))
            $YOD = -2;
        if (is_null($bDead) || !$bDead)
            $Dead = -2;
        if (is_null($FAM))
            $FAM = 'NULL';
        if (is_null($FDM))
            $FDM = 'NULL';

        error_log('INSERT ERROR: Horse: \'' . strtoupper($HorseName) . '\' - YOB: ' . $YOB . ' - COB: \'' . $COB . '\' - SEX: \'' . strtoupper($HorseSex) . '\' - YOD: ' . $YOD . ' - Dead: ' . $bDead . ' - FAM: ' . $FAM . ' - FDM: ' . $FDM);
        return(false);
    }

If I go my favourite SQL editor (SQLPro for MySQL) and I enter in the insert manually it works fine:

INSERT INTO Horses (HorseName, HorseYOB, HorseCOB, HorseSex, HorseYOD, HorseDead, FAM, FDM)
VALUES ('HorseName', 2001, null, 'M', null, false, null, null)

For info: The find() function used is my own and wraps a "SELECT FROM ..." query and works fine. If I do a SELECT from the Horses table in the database afterwards the Horse was never added. MAX_INSERT_ATTEMPTS is my constant and the value is currently set at 5.

I'm working with MaMP PRO and I've looked in my PHP error log, where only the message I sent there appears and no other errors and in my MySQL error log, where no error message appears.

I can't figure out where to look next and I'm hoping whatever my stupid error might be is going to jump out at someone else looking at my code.

Thanks for looking and for any ideas, suggestions or corrections you may have.

---

UPDATED CODE WITH DEBUGGING

        do {
        try {
            $DBInsertHorse->bindValue(':HORSENAME', strtoupper($this->Name));
            $DBInsertHorse->bindValue(':HORSEYOB', $this->YOB);
            $DBInsertHorse->bindValue(':HORSECOB', $this->COB);
            $DBInsertHorse->bindValue(':HORSESEX', strtoupper($this->Sex));
            $DBInsertHorse->bindValue(':HORSEYOD', $this->YOD);
            $DBInsertHorse->bindValue(':HORSEDEAD', (int)$this->Dead);
            $DBInsertHorse->bindValue(':FAM', $this->FAM);
            $DBInsertHorse->bindValue(':FDM', $this->FDM);

            $DBInsertHorse->execute();
        }
        catch (PDOException $e) {
            error_log('SQL INSERT ERROR: ' . $e->getMessage());
        }
   } while($this->find($DB, strtoupper($this->Name), $this->YOB, $this->COB) == false && ++$iCtr < MAX_INSERT_ATTEMPTS);

Answer 1

As @BillKarwin mentioned I was missing a proper call to

        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Once that was in place I was able to see where the error was. This error apparently has appeared since my upgrade to PHP 7.2.10.

I also changed my call to array to a list of calls to bindValue... I may go back to array() but that's not important. ;-)

The PHP code did not like my passing "false" to a tinyint field. I had to type the variable with a call to (int)varname and everything works fine now.

(int)$this->Dead;

THANK YOU ALL!

Answer 2

Check this, it's tested and it works. Also check do while statement.

   try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // prepare sql and bind parameters
        $stmt = $conn->prepare("INSERT INTO Horses ( HorseName, HorseYOB, HorseCOB, HorseSex, HorseYOD, HorseDead, FAM, FDM) 
                                VALUES (:HORSENAME, :HORSEYOB ,:HORSECOB, :HORSESEX, :HORSEYOD, :HORSEDEAD, :FAM, :FDM)");

        $stmt->bindParam(':HORSENAME',$HorseName);
        $stmt->bindParam(':HORSEYOB', $YOB);
        $stmt->bindParam(':HORSECOB', $COB);
        $stmt->bindParam(':HORSESEX', $HorseSex);
        $stmt->bindParam(':HORSEYOD', $YOD);
        $stmt->bindParam(':HORSEDEAD', $bDead);
        $stmt->bindParam(':FAM', $FAM);
        $stmt->bindParam(':FDM', $FDM);

        // insert a row
        $HorseName = strtoupper($HorseName);
        $YOB = "John";
        $COB = "John";
        $HorseSex = strtoupper($HorseSex);
        $YOD = "John";
        $bDead = "John";
        $FAM = "John";
        $FDM = "John";
        $stmt->execute();

        echo "New records created successfully";
        }
    catch(PDOException $e)
        {
        echo "Error: " . $e->getMessage();
        }
    $conn = null;