fifeclub — Fri, 12 Sep 2003 17:07:32 +0000

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

ROB — Fri, 12 Sep 2003 16:24:41 +0000

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