MYSQL Insert Where Not Exists with PDO

Question

I am conditionally inserting values to a MySQL database using a PHP PDO command. The condition states that if the row to be inserted already exists, do not insert it. If it does not exist, insert the row. The code performs just fine when I execute through PHPmyadmin, and thus swap the PDO variables (:a, :b) with static numbers.

I am getting the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name '0'' in...

The PHP code is as follows:

I prepare the sql statement...

$InsertFoo = $pdo->prepare("

    INSERT INTO `MyTbl` (
                            `ColA`,
                            `ColB`
                            )
    SELECT * FROM ( SELECT :a, :b ) AS tmp
    WHERE NOT EXISTS (
        SELECT `MyTbl`.`ColA`
        FROM `MyTbl`
            WHERE `ColA` = :aa
            AND `ColB` = :bb
        )
    LIMIT 1

");

And then loop through an array to execute the prepared statement over and over. (The array was generated earlier using SimpleXML, hence the ->. If you're unfamiliar with SimpleXML, just consider $foo->fooA as a variable that changes for each loop.)

foreach ($loopme as $foo) {

    // query to be executed for ticket insertion
    $TicketsToInsert->execute(array(
                                    a => $foo->fooA,
                                    b => $foo->fooB,
                                    aa => $foo->fooA,
                                    bb => $foo->fooB
                                    ));

}

Any advice is much appreciated:

As a side note, there are many similar questions, but I have not found any that exactly deal with this PDO issue on insert.

1. PDO can only execute one statement at a time
2. Same error, seems to be a different source
3. PDO variables inside a select followed by a Where statement

Many thanks!

---

Answered

Phil offered the much simpler solution of applying a unique constraint across multiple rows, which would in turn reject a newly inserted row that was unique across said rows. This as opposed to testing for the non existence of the row that is about to be inserted. More details below.

Answer 1

Your query looks completely messed up, especially in your EXISTS sub-query. You're selecting MyTbl.ColA from tickets???

My advice would be to simply add a unique constraint on MyTbl (ColA, ColB).

ALTER TABLE MyTbl ADD UNIQUE (ColA, ColB);

Then, your INSERT will fail with a unique constraint violation which can be caught in a PDOException.

$stmt = $pdo->prepare('INSERT INTO MyTbl (ColA, ColB) VALUES (?, ?)');
foreach ($loopme as $foo) {
    try {
        $stmt->execute([$foo->fooA, $foo->fooB]);
    } catch (PDOException $e) {
        $errorCode = $stmt->errorInfo()[1];
        if ($errorCode == 1586) {
            // I think 1586 is the unique constraint violation error.
            // Trial and error will confirm :)
        } else {
            throw $e;
        }
    }
}

To address the error message you're seeing... it's because you aren't differentiating between the INSERT table and the sub-query table.