Best way to add/remove comma seperated IDs in DB

[TEENFRONT]

Hey all,

 

I'm trying to improve the speed of my database queries and I want to put some players IDs in a single column and seperate them by a comma.

 

So inside my colum "awaiting" I want it to be like 123,356,8678,9799 etc.

 

What's the fastest way or best way to add new IDs to this column and remove them.

 

Basically when a player joins a team they go into awaiting, then when they get approved they get removed from awaiting and added into joined with the same comma seperated IDs

[Zane]

[ot]I tried going down this road just recently and failed..  I guess it's just a fact.  Sticking a comma separated list of id within a column is complete no-no in web development.[/ot]

Rather than have awaiting set as a column, it needs to be it's own table.  Crazy right? No.. that's the way it's gotta be.

 

Create a table called awaiting or something relevant to what you're making and considering the information you've given us.. put 3 columns in it..  all INTs

- ID //  For the awaiting id

- TID // For the team id

- PID // For the player id

 

Now, when you have a user request to be on a team... run a query similar to this

INSERT into awaiting (`tid`,`pid`) VALUES ($desiredTeam, $currentPlayer)

To see all the players awaiting approval for a certain team.. then run this query

SELECT name.players FROM awaiting
LEFT JOIN players ON awaiting.pid = name.id
INNER JOIN teams ON awaiting.tid = teams.id
WHERE teams.name = "Bulldogs";

At least I think the above query would work... don't take my word on it.

[TEENFRONT]

Hey thanks for the advice.

 

That last query you posted, iv never actually seen a query like that lol, could you explain what it's doing? Left join and inner join etc?

 

If I was going to select all awaiting players I would have just done this

 

select * from awaiting where team_id=$teamid

 

what the difference between that and the complicated looking query that you just posted with joins etc?

 

Cheers!

[Zane]

Well.. your query would actually be the most ideal when I think about it.  For some reason I was writing it in the assumption that I didn't know the id of the team name, but obviously you would have that information.

 

The INNER JOINs and LEFT JOINs just make things easier... less queries.  You probably will use at least one JOIN before it's over with.  By using a JOIN you can SELECT not only all the awaiting player id WHERE the team = suchAndsuch..  but you can also have MYSQL select those ids... match them with their respective player names / team names.. and return that instead.

 

Say for instance, instead of a list of the awaiting ids.. you wanted a list of the actual player names.  That's the ticker right there.

SELECT players.name FROM awaiting 
INNER JOIN players ON awaiting.pid = players.id
WHERE awaiting.tid = $teamid

[TEENFRONT]

ah yes, as I wouldn't have the players names in the awaiting table, just their IDs. So I'd use those to get the players names using the join query.

 

Hmmmm.  Looks like il have to rethink my overall layout. Cheers!!