MySQL insert non-duplicate multiple values for each key

Question

it's late, my child cried all dayand I'm expected to pull this off by the next 3 hours. I have a 3 MySQL tables "entities", "users", "priviledges" (all have their id as key).

The table "priviledges" is having these columns: id(PRIMARY KEY), user_id, entity_id, priviledge

The thing is that when...

I grant the highest priviledge(3) to a user the system should create new table entries for all remaining entities (once you become "admin" for one entity, you should be "admin" for all entities; if you should have priviledge(2) for some entity already, it should be changed to 3).

I cannot alter the SQL structure and I'm using PHP with PDO. I tried, but I always end up with duplicate entries in "priviledges" as I'm not able to utilise INSERT IGNORE (yep, still SQL noob).

Right now I just select all users with priviledges.priviledge(3), then I select all DISTINCT entities.id and dump them into PHP arrays and then I loop foreach through every "user" and "entity". So instead of sending 1 query, I'm ending with USERSxENTITIES and rightnow its over 500 queries.

Will there be a merciful soul to help me?

danblack · Accepted Answer · 2020-09-02 01:43:45Z

1

First it seems from your data structure that (user_id, entity_id) is meant to be unique.

Because of your "no table schema changes" criteria (otherwise I'd drop id (assumed to be an auto_increment) and add this as the primary key), so instead, create a unique index:

CREATE UNIQUE INDEX uniq_u_e ON priviledges (user_id, entity_id)

You probably have duplicate already so search existing questions for deleting duplicates and then add this index.

I assume you have another table of entities.

To create an admin (3) user on all entities that has an id as its entity primary key.

INSERT INTO priviledges( user_id, entity_id, priviledge)
SELECT 42 as user_id, id, 3
FROM entities
ON DUPLICATE KEY
UPDATE priviledge = 3

So this ensure that the user 42 has admin(3) access on all entities.

answered Sep 2, 2020 at 1:43

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

danblack Over a year ago

You're welcome, in spite of your desperation you came up with a follow-able question. I'm happy to save your day with a small amount of compassion. I'm glad you learnt something. Please look after yourself.

Collectives™ on Stack Overflow

MySQL insert non-duplicate multiple values for each key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related