using more than one table in mySQL

fifeclub posted this at 15:44 — 12th September 2003.

Joined: Feb 2001

Yup, this is cutting edge advanced coding

I've got this php page that displays the results of a signle mySQL database table. This unimpressive work marks the limits of my mySQL experience, but I want to learn more so I ask for your help. In particular, can somebody please show me how to make use of a second and third table. I read about primary key and foreign key but I still can't figure out how to actually make use of it.

The example in the linked page above lists sports organizations and where they are located (there are many more fields but I'm just simplifying this). At the end of this table I have one field league_organization and another field league_eligibility. My goal here is to have these values represent data located in two other tables. For Organization Type (league_organization), "1" should represent "Government" and "2" represent "Business" from a table "organization". And all those "1"'s in Eligibility Type (league_eligibility), should all represent "Open/Public" from a table "eligibility". My main question here is how do I make those texts which the numbers represent actually get placed inside this html table in place of the numbers?

Here's much more detail to help explain what I've done so far. Right now my display makes no attempt to connect to those other two tables, because I just don't know how.

Quote: <?

$db = mysql_connect("localhost", "XXXXXXXXXX", "XXXXXXXXXX");
mysql_select_db("softball_leagues",$db);

$per_page = 20;
$query = "SELECT * FROM leagues";

$result = mysql_query ($query);

if (@mysql_num_rows($result))
{
print "

League ID
League Name
City
State
Organization Type
Eligibility Type
\n";

while($row = mysql_fetch_array($result)) {

print "\n";
print "".$row['league_id']."\n";
print "".$row['league_name']."\n";
print "".$row['league_city']."\n";
print "".$row['league_state']."\n";
print "".$row['league_organization']."\n";
print "".$row['league_eligibility']."\n";
print "\n";
}
print "\n";

} else { echo "Sorry, no records were found!"; }

?>

And here are how my other two tables are set up:

Quote: table "organization"
field 1 > "org_id" > auto-incr
field 2 > "org_type" > varchar

records as follows
1 Government
2 Business
3 Religious

Quote: table "eligibility"
field 1 > "elig_id" > auto-incr
field 2 > "elig_type" > varchar

records as follows
1 Open/Public
2 Private
3 Religious

Sorry if this was all overkill but all I want to do is get the 1's, 2's, and 3's in the main table to display the text they represent in the other tables? Can you explain to me how I do this? THANK YOU SO MUCH!

ROB posted this at 16:24 — 12th September 2003.

They have: 447 posts

Joined: Oct 1999

first replace the league_organization and league_eligibility fields in your leagues table with id fields, and index them so your table looks something like this:

table leagues (
	league_id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	league_name VARCHAR,
	league_city VARCHAR,
	league_state VARCHAR,
	org_id INTGER UNSIGNED NOT NULL,
	elig_id INTEGER UNSIGNED NOT NULL,
	INDEX(org_id),
	INDEX(elig_id)
);

and your other tables look like this:

table organization (
	org_id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	org_type VARCHAR NOT NULL
);

table eligibility (
	elig_id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	elig_type VARCHAR NOT NULL
);

now you can join your tables based on these ids.

SELECT l.*, o.org_type, e.elig_type
FROM leagues AS l
LEFT JOIN organization AS o ON l.org_id=o.org_id
LEFT JOIN eligibility AS e ON l.elig_id=e.elig_id
ORDER BY l.league_name ASC

a LEFT JOIN will return all records from the left table, and matched records from the right table or NULL values if not found
an INNER JOIN will only return records from the left table which have matching records in right table

so the above example will return a list of all leagues, along with their org_type and elig_type if they have one, but replacing LEFT JOIN with INNER JOIN will only return leagues that have both an org_type and elig_type

fifeclub posted this at 17:07 — 12th September 2003.

He has: 688 posts

Joined: Feb 2001

Awesome. Thanks. That'll REALLY help me out a lot. That join stuff is new to me but I'll look up more info on that. I do however have a few small follow up questions about that redoing the table structures.

I get the renaming league_organization and league_eligibility to org_id and elig_id to match the fields in the two other tables. But I only half understand the INDEX(org_id),INDEX(elig_id). I'm making these tables via phpMyAdmin. Does that just mean to hit that "index" link on that field's row when looking at the table structure? Nothing else other than click that link for each of the two new fields?

One side questions: What program or program type do people use to make mySQL databases with commands (not using a browser based script)? I'm assuming that's the style you were using to explain what I should do.

Oh, one other side question: This isn't about structure, but what if one of my leagues offers both Open/Public (elig_id=1) AND Private (elig_id=2) or any similar situation where I need multiples? Will that screw up the way I'm doing this now? Am I going down the wrong road?

Thanks a ton again.

---------------------

Since posting this, just letting everybody know about a good article I found. I doesn't go too much into the php coding but it's pretty good at explaining normalization concepts. http://www.phpbuilder.com/columns/barry20000731.php3