Insert entry if it doesn’t exist in MySQL

Yes, that’s probably a common issue – you want to make insert in MySQL only if a duplicate does not exist. I faced this issue today, and since it took me some time to figure out a way to do it (to be honest – a colleague gave me the query) – I’m sharing it here. Oh, and thanks for it, Vasko!

So it’s actually not that complicated, or not as much as I though when I started the task. We need to build a query, that will make this at once.

Let’s say we have a table with book titles with fields `lang_id` and `title` – which we need to check for duplicates. So let’s say we need to add two books ‘Crime and Punishment’ with language code 23 and ‘The Black OBelisk’ with language code 23. The query will be:

INSERT INTO `books` (`lang_id`, `title`)
SELECT `lang_id`, `title` FROM
(
(SELECT 23 AS `lang_id`, 'Crime and Punishment' AS `title`)
UNION
(SELECT 23 AS `lang_id`, 'The Black OBelisk' AS `title`)
) t_union
WHERE `title` NOT IN (SELECT `title` FROM `books` WHERE `lang_id` = 23);

This is nice, but how do we build it in PHP? Well, even simpler:
We create a function:

function updateBooks($langId, $titles)
{
	$sql = <<<___SQL
INSERT INTO `books` (`lang_id`, `title`)
SELECT lang_id, `title` FROM
(
___SQL;
	$values = [];
	$queries = [];
	foreach ($titles as $title) {
		$queries[] = "(SELECT ? AS `lang_id`, ? AS `title`)";
		$values[] = $langId;
		$values[] = $title;
	}
	$sql .= implode(" UNION ", $queries);
	$sql .= <<<___SQL
) t_union
WHERE `title` NOT IN (SELECT `title` FROM `books` WHERE `lang_id` = ?)
___SQL;
	$values[] = $langId;
	$this->db->query($sql, $values);
}

This method takes the `lang_id` as first parameter and array of titles as the second. I’ve intentionally have not gone into details on the DB execution – this is a topic for another guide. Hope this helps.