I wrote recently about using JOIN when needing to incorporate a lookup in a MySQL delete command. What if one needs to do the same when inserting into a table instead?

This time it’s SELECT to the rescue.

Imagine we have a simple bookmarking function that stores a user id and article id in a lookup table. When a user wants to bookmark an article we invoke a command like this (employing EasyPDO library in PHP):

<?php
$db->ExecuteSQL("insert into bookmarks (article_id,user_id) values (?,?)", "ii", $article_id, $user_id);
?>

Nice and simple. But suppose we only have a user’s email address — not their id. We would have to use their email to look up their id, then add that to our INSERT command:

<?php
$id = $db->FetchValue("SELECT id FROM users WHERE email=?","s",$email);
$db->ExecuteSQL("insert into bookmarks (article_id,user_id) values (?,?)", "ii", $article_id, $id);
?>

Sure, it works. But also, it sucks.

Thankfully we can employ our friend SELECT to trim things down a little. Instead of the “values” clause of the INSERT statement like the examples above, we SELECT the data we want. If we already know the data (in this case, “article_id”) we just put that data right into the SELECT statement. If we need to gather the data, we provide the criteria:

<?php
$db->ExecuteSQL("insert into bookmarks (article_id,user_id) select ?, u.id from users u where u.email=?", "is", $article_id, $email);
?>