How to use joins in code?

Question

I'm actually not too sure if I even need a join. It's really the only thing while learning SQL that I didn't really understand or pay attention too, but basically I have 2 tables:

games
subgames

In games I have gamesname row:

Final Fantasy
Metal Gear Solid
Yu-Gi-Oh

In subgames I have subgamesname row:

Metal Gear Rising

They both have autoincrement for an id. However I am trying to display my games as clickable links then refreshes the page and shows the subgames. So for example if I clicked Metal Gear Solid it would take you to games.php?subgame=Metal%20Gear%20Solid and show Metal Gear Rising. Here is what I have so far, but it does not show Metal Gear Rising, probably because I have the games in one table and sub games in another with no reference to each other.

So my question is, how can I reference them in my code to display properly?

<?php
$sub = $_GET['subgame'];
if($sub){
$result = mysql_query("SELECT $sub FROM games");
 while ($row = mysql_fetch_array($result)) {
printf("%s<br />", $row["subgamename"], $row["subgamename"]);
 }
}
else{
$result = mysql_query("SELECT gamename FROM games ORDER BY gamename");
 while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
printf("<a href='games.php?subgame=%s'> %s </a><br />", $row["gamename"], $row["gamename"]);
 }
}
?>

Answer 1

The query you probably want is:

$sub = mysql_real_escape_string($_GET['sub']);
$query = "
    SELECT subgamesname
    FROM subgames s
    JOIN games g ON g.id = s.game_id
    WHERE g.gamesname = '$sub'
";

I'm assuming the subgames table has a game_id column that's a foreign key to the games table. You need to replace those column names with what you're actually using in your schema.

Answer 2

Your query it's wrong. What is:

$result = mysql_query("SELECT $sub FROM games"); ????

it should be

mysql_query("SELECT subgamesname FROM subgamesname where gamesName ='".$_GET['subgame']."'");

But your name of parameter it's not intuitive subgame, it's should be gameName because you search through games name