List:General Discussion« Previous MessageNext Message »
From:Steve Meyers Date:October 10 2001 1:47am
Subject:RE: linking id's to actual names in MySQL/PHP
View as plain text  
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
> 
> 


Thread
linking id's to actual names in MySQL/PHPStuart White10 Oct
  • Re: linking id's to actual names in MySQL/PHPMark Rissmann10 Oct
  • RE: linking id's to actual names in MySQL/PHPSteve Meyers10 Oct
    • RE: linking id's to actual names in MySQL/PHPStuart White10 Oct
  • RE: linking id's to actual names in MySQL/PHPCal Evans10 Oct