MySQL how to insert a record if it doesn't exist in the table?

Question

PHP

MySQL how to insert a record if it doesn't exist in the table?
My code, please find the error. Everything is ok in MySQL queries, but somehow it doesn’t go through pdo. What's my mistake?

$sth = $dbh->prepare("SELECT COUNT(`id_operator`) FROM `operators`");
$sth->execute();
$count = $sth->fetchAll(PDO::FETCH_ASSOC);
$id = $count['0']['COUNT(`id_operator`)'] + 1;

$sth = $dbh->prepare("INSERT INTO operators (id_operator, name_operator) 
SELECT * FROM (SELECT `id_operator` =:id_operator, `name_operator` =:name_operator) AS tmp 
WHERE NOT EXISTS( 
SELECT name_operator FROM operators WHERE name_operator =:name_operator
) LIMIT 1");
$sth->execute(array(
    'id_operator'    => 25,
    'name_operator'  => 'Ернат'));

Answer 1

INSERT IGNORE has several disadvantages:

• ignores all errors, not just the unique index error
  
• increments the numeric index anyway
  

Therefore, you should perform validation and insertion in 2 steps:

<?php
$operator = "Borat";

// Check if operator exists
$stmt = $pdo->prepare(
    "SELECT id_operator FROM operators WHERE name_operator =:name_operator;"
);

$stmt->execute(["name_operator" => $operator]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

// If not exists store new record
if (!$result) {
    $sth = $pdo->prepare(
        "INSERT INTO operators (name_operator) VALUES (:name_operator);"
    );

    $sth->execute(["name_operator" => $operator]);
    $id_operator = $pdo->lastInsertId();
} else {
    $id_operator = $result["id_operator"];
}

echo "id_operator: $id_operator";

Test PHP code online

Answer 2

MySQL how to insert a record if it doesn't exist in the table?

INSERT IGNORE , without all this pile of selects.