N00b MySQL Question

RiZ

(I'm very new to PHP and MySQL, so please bare with me 😃)

Let me set this up for you guys. I have a db table which contains on each row information about a different "team," things like when they were founded, their status, name, etc. I also have ten fields for team members. These are of the INT type, as they will correspond to member identification numbers on a forum. I just got to thinking about this, and realized it was not the best way of doing it. I mean, what if there are more than 10 players? So, I thought I would have all the members in one feild. Problem is, I have no clue how to do this 🙁

So, could someone please explain to me how to have some PHP code seperate a bunch of numbers in one field into seperate entities, and then print them? I figure once I can do this, it should not be too difficult to use these numbers in WHERE clauses and get actual member names.

Any help would be great 🙂

craigh

you could do it that way, but there is a better way.

In each row of your team table add a team_id column. Make it auto_increment so it is always unique.

now, create a second table: team_members that has the team_id and the member_id. It might look like this:

team_id  mem_id
1             101
1             302
1             322
2             544
etc.

then, you can join the tables together to pull out all the members of the team:

select * from team_members left join team on team_members.team_id=team.team_id where team_name='whitesox' or something like that.

congrats - you have just stepped into DATABASE NORMALIZATION!

RiZ

OH JESUS! I have seen the LIGHT! Hallelujah!

lol, thanks for the insight. I think I see where you're going with this. Let me screw around with it a bit, and see what happens.

Thanks a bunch bro 🙂

skizzay

A much easier way to do this is to create a team table with each team having a unique ID (integer).

CREATE TABLE team(
id INT NOT NULL AUTO_INCREMENT,
name TEXT, ....
PRIMARY KEY(id));

Next make a player table where it has the id of the team that each player belongs:

CREATE TABLE player(
id INT NOT NULL AUTO_INCREMENT,
name TEXT,
team_id INT NOT NULL, ...
PRIMARY KEY(id));

The benefit of doing this you can add as many players to a team that you want. Plus, say one of your players was 'dropped' from the team's roster, just set his team_id to zero. This way, you save all his previous stats without having him assigned to a team.

To make a query that lists the player's name and team name, do this:

SELECT player.name AS name, team.name AS team FROM player, team WHERE player.team_id = team.id

The result will look something like this:

name | team

RiZ

Damn, these both seem like good suggestions, but I can't decide which is the better of them. Both will allow for players being on multiple teams, right? That's a must.

I'm going to have to meditate on this for a while 🙂

BuzzLY

If you need to have a many to many relationship (in other words, teams have multiple players, and players are on multiple teams), then you need the "connecting" table with team id's and member id's, as craigh suggested.

team:

id
name
other_info

players:

id
name
other_stats

playerteam:

id
player_id
team_id

RiZ

I see what you're talking about.

So, when I execute this query:

SELECT * FROM team_members left join teams ON team_members.team_id=teams.team_id WHERE team_id=$tid

($tid will be in the URL)

I will get out the team information in table "teams," as well as the player identification #s in that team? Could I take all these player numbers, and get out player names (from the last table) using only one query?

Thanks for the replies, I know it must be difficult dealing with such a novice 🙁

One more thing, why does table "playerteam" need an ID for each row?

BuzzLY

If you already know the team id, you should be able to pull the player names and info by:

SELECT player.name as player, player.height as height FROM players left join playerteam WHERE player.team_id = '$tid'

Now, if you want to pull the info needed to display each team, and the player names and stats associated with each team, I'm not sure the SQL statement needed there. Perhaps a more knowledgable SQL expert can help with that? I'm not convinced you would really want a single SELECT statement that could do that, though. For example, if you had a page listing all teams, and each player associated with that team, I would think you would want a separate SQL statement for each display, wouldn't you?

One more thing, why does table "playerteam" need an ID for each row?

For this purpose, it doesn't. I am in the habit of always including a unique id whenever I create a new table. It's a good practice, IMHO. However, this is probably one of those very rare times where a unique id is not needed.

RiZ

Originally posted by BuzzLY
SELECT player.name as player, player.height as height FROM players left join playerteam WHERE player.team_id = '$tid'

I've been playing with this for a little while, but I can't seem to get it right. Let me explain my tables: I have "user" which includes "user_name" and "user_id," "teams" which includes "team_id," "team_name," and a few others, and "team_members" that includes "team_id" and "user_id".

Also, as of right now there are no other player stats, so there no worry about those.

I tried to change that into: "SELECT user.user_name as user FROM user left join team_members WHERE team_members.team_id = 1" but it didn't work. Where did I go wrong?

RiZ

:mad: I still can't figure out that query.

RiZ

I think I see the problem. There's no "team_id" in the player table. It looks like nobody's around so I'll have to figure this out by myself.

BuzzLY

😉 Sometimes that's the best way to figure things out 🙂