MySQL Many to Many with Unique keys and Update/Select if Exists otherwise Insert

Question

In my goal to have a Many-to-Many relationship in my MySQL database I have arrived at another bridge to build.

Current Tables:
Users (id, name)
Tags (id, name)
User_Tags (user_id, tag_id)

Here is the goal:
I would like to have the ability to take a tag i.e: #fb and insert it into my Tags database which has a unique constraint on name and if the tag exists I would like to return it's id

I would like to insert the tag.id and the current user's user.id into User_Tags. I do not have any unique constraints on the User_Tags table because I would like to be able to track the user's most commonly used tags and present them in a list ordered by frequency of use.

I am currently developing this project in PHP using prepared statements.
Additionally, performance is not currently an issue as my user count is still fairly small, but it is steadily growing and may be an issue in the future.

Answer 1

You can use MySQL's ON DUPLICATE KEY UPDATE and LAST_INSERT_ID(expr) to get the tag id regardless of whether it has been inserted or it has already been there, e.g.

INSERT INTO
  Tags
  (name) 
VALUES
  (:name)
ON DUPLICATE KEY UPDATE
  id = LAST_INSERT_ID(id)

and

INSERT INTO
  User_Tags
  (user_id, tag_id)
VALUES
  (:userid, LAST_INSERT_ID())