1

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.

Jayrox
  • 4,335
  • 4
  • 40
  • 43

1 Answers1

3

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())
VolkerK
  • 95,432
  • 20
  • 163
  • 226
  • Perfect; Is there a way to do `ON DUPLICATE KEY SELECT`? – Jayrox May 10 '10 at 04:15
  • I dunno, maybe I'm over optimizing; but as I understand it reads are cheaper than writes. So if I dont have to write, it would be better to just read, right? – Jayrox May 10 '10 at 22:48
  • True, but the first statement doesn't cause a write operation in case of a unique constraint violation. If you have a statement like `UPDATE foo SET x=x` MySQL doesn't write anything, you'd even get affected_rows()=0. Same thing with `on duplicate key update`, old value=new value => no write operation. – VolkerK May 11 '10 at 09:14