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