Help getting data from another table

Fife_Club

I'm a novice so sorry if I get some terms wrong. Can somebody help me with how to code a specific part of php for my many-to-many mySQL relationship. I'll make this the simplest example I can so I can learn how it's basically done (rather than just taking the code and not learning for myself). Lets say...

I have one table that lists leagues in town

league_id || league_name
1 || Alpha League
2 || Beta League

I have another table that lists ballfields in town

field_id - field_name
1 || X Field
2 || Y Field
3 || Z Field

Now I haven't made it yet but I suspect that I'll need an intermediate table like this. Lets say the Alpha League plays on every field in town but the Beta League only plays on the Z field.

league_id || field_id
1 || 1
1 || 2
1 || 3
2 || 3

So far so good, right? Okay, so now I've got all my mySQL tables setup right (I hope), but I don't know how to do the php coding that utilizes this relationship on a webpage. With only one table I can easily display all the data about the Alpha League by using code similar to this:

<?
$db = mysql_connect("localhost", "xxx", "xxx");
mysql_select_db("league_database",$db);

$result = mysql_query("SELECT * FROM league_table WHERE id=$id",$db);
$myrow = mysql_fetch_array($result);

print $myrow["tableinfo1"]."<br />";
print $myrow["tableinfo2"]."<br />"; 
print $myrow["tableinfo3"]."<br />"; 
..........and so forth.........
?>

But aside from listing info from just this single league_table, how would I list info from the fields_table too? In other words, how do I list on this same webpage that the Alpha League plays at X Field, Y Field, and Z Field? (fields is on a 2nd table and the relationship table is a 3rd table) What is the basic coding for how to display this data? Thank you so much for your help!

LordShryku

Just need to link the tables. Modify as needed

SELECT a.league_name,
       b.field_name
FROM   leagues a, ballfields b, links c
WHERE  a.league_id = c.league_id
AND    b.field_id = c.field_id

Fife_Club

I want to make sure I understand this correctly before start fiddling with the code improperly. Does this mean that it's all done in the connecting to the database, and that if I get that top section right all I need to do to print the fields used by LeagueA is go ahead and print field_names (and only field names that LeagueA use will be listed). I may have gotten some details wrong but basically, it's all done at the top part, not down where I acutally print the data? If I understand correctly, that doesn't seem too hard for me to do. THANKS A BUNCH!!!! 🙂

LordShryku

Correct. For the most part, the SQL will do everything for you. Any constraints you wanted to add, you could in thr WHERE statement by adding on to it. Like if you just wanted to look at LeagueA, you could do this

SELECT a.league_name,
       b.field_name
FROM   leagues a, ballfields b, links c
WHERE  a.league_id = c.league_id
AND    b.field_id = c.field_id
AND    a.league_id = 1