CODEWITHSUNDEEP
phpsqldatabasedatabase-designmysqli
user2773002
user2773002

Reputation: 5

Achievements in PHP with mysqli

I've currently got two tables:

mems (members):  
id,  
name,  
email,  
password,  
salt,  
achievements_id

achievements:  
id,  
achievement,  
description,  
points

I am able to correctly display the data for a user with:

"SELECT * FROM achievements WHERE id IN (SELECT achievements_id FROM mems WHERE name = '$name')";

My question is, how do I add the achievement ID to the user so each user has their own achievement records? Currently if I use an update it just wipes over the old achievement so it only ever displays 1 record.

Say I have 2 achievements and 2 users

User 1 then achieves achievement 2, it's viewable but now they have achievements_id set to 2.

I have no clue how to do this. I know what I want to do, but no clue how to design the database to have each user having their own records of achievements. I originally did it where achievements table had a member_id and I'd concat the other users ID into their, dodgy but it semi-worked.

Any help? Sorry if I am making no sense.

Upvotes: 0

Views: 312

Answers (2)

frosty11x
frosty11x

Reputation: 205

The way I would go about this would be to create another table to hold the users achievements.

So basically another table that looks like such:

UserAchievements:
userID, achievementID

That way you can join the tables like: 

SELECT * FROM achievements a 
INNER JOIN userAchievements uA ON uA.achievementID = a.id 
INNER JOIN users u ON uA.userID = u.id

That will give you all the users for all the different achievements.

Hope that helps!

Upvotes: 0

Mike Dinescu
Mike Dinescu

Reputation: 55720

It sounds like what you need is to model a many-to-many relationship (many users can share the same achievement - ie. be linked to the same entitiy in the achievements table; while a single user can have multiple achievements). This is usually done using an extra table. Let's call it: Members_Achievements_Map.

CREATE TABLE Members_Achievements_Map (
   MemberID,
   AchievementID
)

This table would link entities from the members table to entities in the achievements table.

Upvotes: 3

Related Questions