Junction

[vmars]

Hi!

I have created two tables, one 'country' [populated with country names] and the other 'flag colors' [populated with the colors of each country's flag]. The junction table combines these two tables. I want to create a page that lists each country and the colors of their respective flags. Obviously this info will be pulled from the junction table, but i am unsure of where to start. I know how to call on mysql in the script to give access to the database, however after that I am unsure of how to print this information. Any ideas of how to start? I've been working on this for a while and just can't figure this out. Thanks in advance!

[Muddy_Funster]

Could you post your full and actual table structure for all three tables please?

[vmars]

mysql> DESCRIBE flags;

+----------------+-----------------------+------+-----+---------+----------------+

| Field              | Type                    | Null | Key | Default | Extra          |

+----------------+-----------------------+------+-----+---------+----------------+

| flags_id | mediumint( unsigned| NO  | PRI | NULL  | auto_increment |

| name          | tinytext              | YES  |    | NULL    |                |

| directions    | text                  | YES  |    | NULL    |                |

+----------------+-----------------------+------+-----+---------+----------------+

 

mysql> DESCRIBE colors;

+------------+-----------------------+------+-----+---------+----------------+

| Field      | Type                  | Null | Key | Default | Extra          |

+------------+-----------------------+------+-----+---------+----------------+

| colors_id  | mediumint( unsigned | NO  | PRI | NULL    | auto_increment |

| name      | tinytext              | YES  |    | NULL    |                |

| directions | text                  | YES  |    | NULL    |                |

+------------+-----------------------+------+-----+---------+----------------+

 

mysql> DESCRIBE FlagsColors;

+----------------+-----------------------+------+-----+---------+----------------+

| Field          | Type                  | Null | Key | Default | Extra          |

+----------------+-----------------------+------+-----+---------+----------------+

| FlagsColors_id  | int(10) unsigned      | NO  | PRI | NULL    | auto_increment |

| flags_id | mediumint( unsigned | YES  |    | NULL    |                |

| colors_id        | int(10) unsigned      | YES  |    | NULL    |                |

| amount        | char(50)              | YES  |    | NULL    |                |

+----------------+-----------------------+------+-----+---------+----------------+

 

mysql> SELECT * FROM FlagsColors;

+--------------+----------------+---------+--------+

| FlagsColors_id | flags_id | colors_id | amount |

+--------------+----------------+---------+--------+

|            1 |                    1 |            2 | NULL  |

|            2 |                    1 |            4 | NULL  |

|            3 |                    1 |            2 | NULL  |

|            4 |                    2 |            2 | NULL  |

|            5 |                    2 |            3 | NULL  |

|            6 |                    3 |            1 | NULL  |

|            7 |                    3 |            3 | NULL  |

|            8 |                    3 |            6 | NULL  |

|            9 |                    2 |            7 | NULL  |

|          10 |                    3 |            6 | NULL  |

|          11 |                    1 |            5 | NULL  |

|          12 |                    2 |            4 | NULL  |

|          13 |                    3 |            4 | NULL  |

+--------------+----------------+---------+--------+

 

So, I'd like it to display Country 1's flag has Blue, Green and Yellow. Country 2's Blue, etc....(Also, I know there is a repeat with Country 1, it's ok...) SOrry the tables are a little messy!

[vmars]

I'm aware I have to use the Join clause, but the Internet examples are proving to be thoroughly confusing. Thank you for any help you can provide!

[fenway]

SELECT Colors.name, Flags.name
FROM FlagsColors 
INNER JOIN Flags USING ( flag_id )
INNER JOIN Colors USING ( color_id )

 

Though I see no reference to "country" anywhere.

[vmars]

Thanks! I have written my script but my page says the following: "Unknown table 'flags' in field list"

Any idea what is wrong with the following code?:

 

(Started the php and connected to mysql...script starts after that)

mysql_connect(localhost,$username,$password);
@mysql_select_db($database);

// Construct join query

$query = "SELECT flags.name, colors.name";
"FROM FlagsColors";
"INNER JOIN flags USING ( flags_id )";
"INNER JOIN colors USING ( colors_id )";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
        echo $row['Flag']. " - ". $row['Colors'];  
echo "<br />";
}
?>

 

Your help is greatly appreciated.

[vmars]

Nevermind my last reply, I've fixed that part. My trouble now lies in actually printing this table. I think my table code may be too simple? perhaps? aside from obviously just not working.

 

<?php
$username="***";
$password="***";
$database="***";
$host="localhost";

mysql_connect($host,$username,$password);
mysql_select_db($database);

// Construct our join query

$query = "SELECT flags.name, colors.name FROM FlagsColors INNER JOIN
flags USING (flags_id) INNER JOIN colors USING (colors_id)";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
echo "<table border='1'>
<tr>
<th>Flag</th>
<th>Colors</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['flags'] . "</td>";
  echo "<td>" . $row['colors'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

?>

[gizmola]

Code looks ok to me, what results are you getting?

 

[vmars]

I fixed it!

 

Thank you for your assistance everyone!

[Muddy_Funster]

problem is that there is no $row['flags'] or $row['color'] - since you are using mysql_fetch_array() change these to $row['0'] and $row['1'] respectivly and it should work, alternativly (and better practice) you can aliase your fields in the SQL

SELECT flags.name AS flags, colors.name AS color FROM...

 

also - have you got error reporting set to E_ALL? it should have told you that there were undifined variables