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

Posted by Jayrox on Stack Overflow See other posts from Stack Overflow or by Jayrox
Published on 2010-05-09T04:39:06Z Indexed on 2010/05/09 4:48 UTC
Read the original article Hit count: 253

Filed under:

mysql

|

select-insert

|

php

|

many-to-many

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.

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about select-insert

SELECT, INSERT, UPDATE with the SQL MERGE Statement

as seen on Internet.com - Search for 'Internet.com'
In today's column we'll demonstrate how and when to use the new TSQL MERGE statement and how this can be a helpful addition to your toolbox. >>> More
SELECT, INSERT, UPDATE with the SQL MERGE Statement

as seen on Internet.com - Search for 'Internet.com'
In today's column we'll demonstrate how and when to use the new TSQL MERGE statement and how this can be a helpful addition to your toolbox. >>> More
Select,Insert,Update and Delete data with LINQ to SQL in an ASP.Net application

as seen on ASP.net Weblogs - Search for 'ASP.net Weblogs'
As you might have guessed I am continuing my LINQ to SQL posts. I am teaching a course right now on ADO.Net 3.5 (LINQ & EF) and I know a lot of people who have learned through my blog and my style of writing. I am going to use a step by step example to demonstrate how to select,update,insert,delete… >>> More
Transaction Isolation on select, insert, delete

as seen on Stack Overflow - Search for 'Stack Overflow'
What could possibly go wrong with the following transaction if executed by concurrent users in the default isolation level of READ COMMITTED? BEGIN TRANSACTION SELECT * FROM t WHERE pid = 10 and r between 40 and 60 -- ... this returns tid = 1, 3, 5 -- ... process returned data ... DELETE FROM t… >>> More
How to track auto-generated id's in select-insert statement

as seen on Stack Overflow - Search for 'Stack Overflow'
I have two tables detail and head. The detail table will be written first. Later, the head table will be written. The head is a summary of the detail table. I would like to keep a reference from the detail to the head table. I have a solution but it is not elegant and requires duplicating the… >>> More