Stuart,
The layout of your tables makes it very difficult to do effective joins to get the player
names. It is possible, but you'll need to do a separate join for each player. I would
suggest the following table structure:
TEAMS
=====
teamID *
teamName
POSITIONS
=========
positionID *
positionName
PLAYERS
=======
playerID *
teamID
positionID
jerseyNum
fname
lname
Make the columns with *'s next to them the primary keys. Then, add an additional index on
teamID in players, and a unique index on (teamID, positionID) in players. This should
give all of the functionality you need, and make it much easier to get the information
you want, in as few simple queries as possible.
Steve Meyers
> -----Original Message-----
> From: Stuart White [mailto:superstu@stripped]
> Sent: Tuesday, October 09, 2001 7:31 PM
> To: mysql@stripped
> Subject: linking id's to actual names in MySQL/PHP
>
>
> I've got 2 tables, one with teams, one with players. the teams table
> has 12 playerID records each, one for each player, that I've marked
> as index since they are foreign keys. The tables look like this:
>
> team
>
> teamID c pf sf sg pg 6th 7th 8th 9th 10th 11th 12th
> 17 289 277 288 274 275 287 281 285 286 283 278 273
>
>
> players
>
> playerID jerseyNum fname lname
> 289 3 Loren Woods
> 277 21 Kevin Garnett
> 288 10 Wally Szcerbiak
> 274 4 Chauncy Billups
> 275 7 Terrell Brandon
> 287 32 Joe Smith
> 281 13 Felipe Lopez
> 285 44 Anthony Peeler
> 286 35 Reggie Slater
> 283 8 Rados Nesterovic
> 278 22 Dean Garret
> 273 5 Will Avery
>
>
> And it goes on like that for 29 teams and 300 some odd players. When
> I write a php script to interact with this MySQL database, and
> display the players on the web in HTML, how do I link the playerID's
> with the players' actual names? I'm thinking that I should use an
> associative array, but I'm not sure, and I'm not sure how to
> implement that...but I've yet to think about it. Is an associative
> array the way to go with this? What do you think?
> Thanks, -stu
> --
> Stuart A. White
> Stanford University
> Office of Government & Community Relations
> Intern
> 650-723-6318
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <mysql-thread87490@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-steve-mysql-mainlist=spamaphobia.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
