Outputting from database - PHP/MySQL

[herrpoon]

Hi there, I am trying to get the following table outputted from my database with all the values etc., here is what I want it to look like:

<table id="mytable" cellspacing="0" summary="Database Overview">
<caption>Table 1:Database Overview </caption>
  <tr>
    <th>Family Name</th>
	<th>Organism1</th>
	<th>Organism2</th>
	<th>Organism3</th>

  </tr>
  <tr>
    <th>Family1</th>
    <td>Protein1</td>
    <td>Protein2</td>
    <td>Protein3</td>
  </tr>

   <tr>
      <th>Family2</th>
      <td>Protein3</td>
      <td>Protein4</td>
      <td>Protein5</td>
  </tr>
</table>

Where organism, family and protein are fields in a database.
This is what I have got so far (I have got the sql sorted), but it looks completely wrong:

if (mysql_num_rows($subresult) == 0)
		{
			echo "no data";
		}
		else
		{

			echo "
				<table id='mytable' cellspacing='0' summary='Database Overview'>
				<caption>Table 1:Database Overview </caption>
				<tr>
	    			<th>Family</th>
			";

			$familyid = -1;
			$organismid = -1;

			while ($row = mysql_fetch_assoc($subresult))
			{
				if ($organismid != $row['organism_id'])
				{

					if ($organismid == -1)
					{
						echo "";
					}

					echo "<th>" . $row['organism'] ."</th>";

					$familyid = $row['family_id'];
					$organismid = -1;
				}
				if ($familyid != $row['family_id'])
				{
					echo "<th>" . $row['family'] . "</th>";
					$familyid = $row['family_id'];
				}
				else
				{

					echo "</tr>";
				}
				echo "<td><a href='protein.php?id=" . $row['id'] . "'>" . $row['name'] . "</a></td>";
			}

			echo "
				</tr>
				</table>
			";

	}

I'm not even sure I am going about it the right way, any help would be very much appreciated, thanks, Chris.

 

[herrpoon]

Thanks for the response guys, my brother originally helped me with this, and I remember being a little confused at the time to say the least too! I'll try and explain a little better this time

The SQL:

$subsql = "SELECT chap.*, families.family, organisms.organism FROM chap, families, organisms ". "WHERE chap.family_id = families.id AND chap.organism_id = organisms.id ". "AND chap.active = 1 ORDER BY families.family, organisms.organism;"; 
$subresult = mysql_query($subsql);

I'm querying data from three tables. The tables are called:

• chap
• families
• organisms

organism_id, families_id are cross-referenced (foreign keys?) in the chap table, they correspond to organism and family in their respective tables (organisms and families)

<table id="mytable" cellspacing="0" summary="Database Overview">
<caption>Table 1: Chaperone Database Overview </caption>
  <tr>
    <th>Family Name</th>
	<th> ". $row['organism'] . "</th>
  </tr>
  <tr>
    <th>" . $row['family'] . "</th>
    <td>" . $row['name'] . "</td>
  </tr>
</table>

Name is the name of the protein in the chap table. Right, I think that covers everything, let me know if you need any more info, thanks!