CODEWITHSUNDEEP
phpmysql
JoKr
JoKr

Reputation: 86

MySQL insert non-duplicate multiple values for each key

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?

Upvotes: 1

Views: 49

Answers (1)

danblack
danblack

Reputation: 14666

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.

Upvotes: 1

Related Questions