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);

In the execute array try to add manually values instead of using the variables — Marios Nikolaou, Jan 27 '19 at 17:53
whats the error in php error log? Place this code in top of your file or somewhere in your config files and let me know what it returns `ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);` — Saroj Shrestha, Jan 27 '19 at 17:55
Have you actually enabled `PDO::ERRMODE_EXCEPTION`? You seem to be relying on that instead of checking the return value of `prepare()` and `execute()`, but you haven't shown where you set it. — Bill Karwin, Jan 27 '19 at 17:56
I would put a call to `error_log()` after the `execute()` to prove that no exception is thrown. — Bill Karwin, Jan 27 '19 at 17:56
I would also double-check that I'm committing the transaction, or prove that I'm using autocommit. — Bill Karwin, Jan 27 '19 at 17:57
I don't know how to reply to individual comments... I just tried adding manual values instead of using the variables and the problem persists without change. As I mention in my message my find function is defined elsewhere as a SELECT wrap and works fine. Even if the find function failed MAX_INSERT_ATTEMPTS times the INSERT would still have worked once and it does not. As I mentioned no error appears in the PHP error log. The only line that appears there is the error_log() call I put. All three of your error display calls are higher up on my code and always there when I'm debugging. — Derek Erb, Jan 27 '19 at 18:07
@BillKarwin I put a call to error_log() after the execute() statement and it never gets called. So I assume an exception is being thrown... but I'm not seeing it appear anywhere. Any suggestion to find my exception? Thanks! — Derek Erb, Jan 27 '19 at 18:08
@BillKarwin correction to my previous comment. I put a call to error_log in my try and right after the execute and it DOES appear in my PHP error log so there appears to be no exception. — Derek Erb, Jan 27 '19 at 18:14
Okay, that makes me wonder even more strongly whether you're committing the transaction for this insert. — Bill Karwin, Jan 27 '19 at 18:15
It also makes me wonder if you really set `PDO::ERROMODE_EXCEPTION`? — Bill Karwin, Jan 27 '19 at 18:17
Apparently I can't post my new code in a comment here as it's too long. Where can I post a longer reply? — Derek Erb, Jan 27 '19 at 18:17
Edit your question above. You can add a separator by using `---` on a line by itself, and then make clear what follows the separator is new code. — Bill Karwin, Jan 27 '19 at 18:17
@BillKarwin I try adding the following above my do { loop: $DBInsertHorse->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); In my PHP Error log I get the following error message: PHP Warning: PDOStatement::setAttribute(): SQLSTATE[IM001]: Driver does not support this function: This driver doesn't support setting attributes in /../horse.inc on line 67 — Derek Erb, Jan 27 '19 at 18:19
Maybe it is the same, but as far little I remember from my PHP developer past days the array must include parm names exactly as written above. $DBInsertHorse->execute(array( ':HORSENAME' => strtoupper($HorseName), ':HORSEYOB' => $YOB, ':HORSECOB' => $COB, ':HORSESEX' => strtoupper($HorseSex), ':HORSEYOD' => $YOD, ':HORSEDEAD' => $bDead, ':FAM' => $FAM, ':FDM' => $FDM) — A. Lion, Jan 27 '19 at 18:22
@P.Lion, That was true in an old version of PDO, but they changed it eventually. You don't need the `:` prefixes when you bind params. That makes it easier for example to bind directly using your `$_POST` array, where the keys probably don't have the `:` prefix. — Bill Karwin, Jan 27 '19 at 18:27
@DerekErb, Use `$DB->setAttribute()`. You can set attributes at the connection level, not the statement level. — Bill Karwin, Jan 27 '19 at 18:28

score 1 · Answer 1 · answered Jan 27 '19 at 19:28

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!

Marios Nikolaou · Answer 2 · 2019-01-27T18:31:02.270

0

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;

edited Jan 27 '19 at 18:31

answered Jan 27 '19 at 18:19

Marios Nikolaou

1,326
1
13
24

2

IIRC, You can't `bindParam()` to the result of a function like `strtoupper()`, it's not something that PHP can make a reference to. Did you really test this? – Bill Karwin Jan 27 '19 at 18:21
I tested it - it does work, but does not insert a value for horsename and horsesex. Also you have a missing parenthesis in your call to `prepare()`. – Bill Karwin Jan 27 '19 at 18:24
I have test it with custom values but no with strtoupper(). If that is the problem then he can do it before bind. – Marios Nikolaou Jan 27 '19 at 18:25
But I did not downvote your answer - I try not to downvote anyone. – Bill Karwin Jan 27 '19 at 18:25
I downvoted as this should definetly throw a strict standards violation message for the reason @BillKarwin pointed out. For configuration of error reporting see https://stackoverflow.com/a/21429652/3223157 – Xatenev Jan 27 '19 at 18:26
I try to help, missing closing tags it's not big problem.it's fixed now – Marios Nikolaou Jan 27 '19 at 18:32
`I try to help` - That doesn't change the fact that your answer was wrong. – Xatenev Jan 27 '19 at 18:38
Thanks for trying to help. Your example demonstrates that it's possible to insert successfully to a table like the OP's. I suspect that something is wrong with their code that they haven't shown yet. Either exceptions are not enabled, or else the transaction was not committed after the INSERT. – Bill Karwin Jan 27 '19 at 18:45
First of all THANK YOU ALL for your helpful comments and suggestions. I have mostly used execute(array(...)) in my code and it has always worked. But I have no problem changing as you can see from the modified code above. Unfortunately it acts like it works, the error_log() call right after the execute() runs and acts like it inserted correctly. However it runs 5 times, as it does not find the inserted record afterwards, and the info is never inserted in to the table. :-( – Derek Erb Jan 27 '19 at 18:48
Try to execute it without the do while statement – Marios Nikolaou Jan 27 '19 at 19:04
Using `execute(array())` is exactly like using `bindValue()` for each parameter. Using `bindParam()` requires the variables be references, and it allows you to bind before you assign values to the variables. It's useful if you execute your prepared statement many times in a loop, using different values each time. – Bill Karwin Jan 27 '19 at 19:04

Problem with PHP INSERT in to MySQL database

2 Answers2