Outputting from database - PHP/MySQL

herrpoon

herrpoon

herrpoon

Associate
Joined
1 Aug 2005
Posts
28
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:

Code: 
<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:

Code: 
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.
 
remember what's inside the while loop can only output individual rows in your (html) table. does your query return results in the right format to do this? looking at what you want in the first part of your post i have to say i'm a little confused. :p
 
I'm finding it hard to understand the logic of your code, particularly the $organismid variable, which is set to -1 and is never subsequently changed, yet you make a comparison with it (which won't change its value) and then pointlessly set it to -1 again.

What's the purpose of $organismid and $familyid? Perhaps you meant to set $organismid to a value from the database?

If all you're trying to do is output rows of data into a table, it looks like you're trying to make it more complicated than it needs to be.
 
Last edited:
I'm trying to write you a fixed version of code but I don't understand your current code to understand how your database looks and exactly what you want.
 
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 :p

The SQL:

Code: 
$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)

Code: 
<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!
 
Last edited:
I'm still confused by what you are trying to do, mainly from the fact that I can't visualise what your SQL query will output.

If you would like to MSN me I got some free time and i'l try get it working for you :)

addr on profile.
 
You must log in or register to reply here.
Back
Top Bottom