Multi-Relational Database

[mcfmullen]

I have a database that matches animals to themes. More than one animal can belong to more than one theme.

 

I have the following:

Animal Table: idAnimal, nameAnimal

Theme Table: idTheme, nameTheme

animalTheme Table: nameAnimal, nameTheme

 

This system works. I also have animals.php which lists all the animals in the databse and an animalspecs.php page that dynamically changes depending on what animal was chosen in the animals.php page (i.e animalspecs?animal=Goat displays the goat info). This also all works.

 

The problem is when it comes to displaying ALL the themes that animal belongs to. On my animalspecs.php page I can only get one theme to show but some animals belong to two or more themes. How can I get ALL the themes that animal belongs to to show?

 

My animalspecs.php code that calls the database info:

<?php
$sql = "SELECT Animals.photoAnimal, Animals.nameAnimal, animalThemes.nameTheme FROM (Animals LEFT JOIN animalThemes USING (nameAnimal)) WHERE Animals.nameAnimal = '{$_GET['animal']}'";
    $result = mysql_query($sql);
    if ($result) {
      if (mysql_num_rows($result) > 0) {
        $row = mysql_fetch_assoc($result);
?>

 

My animalspecs.php code where the themes show in html:

<tr>
<td width="14%" align="right">Theme:</td>
<td width="22%">
<?php 
if (trim($row['nameTheme']) == '') 
  echo "None";
else
  echo "{$row['nameTheme']}";
?>
</td>
</tr>

 

I suppose the question is, how do I get the $row['nameTheme'] variable to store more than the first theme the animal belongs to?

[mcfmullen]

I got it working by using this:

<tr>
<td width="14%" align="right">Theme:</td>
<td width="22%">
<?php 
if (trim($row['nameTheme']) == '') 
  echo "None";
else
$sql2 = "SELECT animalThemes.nameTheme FROM animalThemes WHERE nameAnimal = '{$_GET['animal']}'";
$themes = mysql_query($sql2);
while($row2 = mysql_fetch_array($themes)){
echo $row2['nameTheme'];
}
?>
</td>
</tr>

 

Now the problem is that I don' know how to get each theme to appear in a seperate row rather than one right after the other....

 

EX:

I get this: fallspring

 

rather than this:

fall

spring

[mcfmullen]

Go it working by adding <br> after echoing the row while in the loop!