Jump to content


Photo

Display SQL Table, just query label headings from different table


  • Please log in to reply
1 reply to this topic

#1 richie19rich77

richie19rich77
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 14 October 2006 - 05:50 PM

Hi Everyone

Very new to PHP and anything web related, just a normal VB programmer. Building a sports website that will list league tables, fixtures, percentages and so on. I have built my backend database, but just trying to get some of the basic queries to work.

For starters I have a table called Clubs

Club_ID  Club_Name
1            Howard
2            Chatham
3            Rainham

Then I have another table called Teams

Team_ID  Club_ID  Team_Name
1            1          1st
2            1          2nd
3            2          1st
4            3          1st

Now I want to create a dynamic table that displays the "Teams" table, but instead of listing the Club_ID I want it to do a lookup and show the real name of that club.  e.g  Team_ID 1 would be: 

Team_ID  Club_ID    Team_Name
1            Howard    1st

Sorry if it does not make sence, but the problem I have is that I do not no the wording for this kind of function otherwise I could look it up on the internet.

I am using Dreamweaver 8.0.2

Thanks



#2 richie19rich77

richie19rich77
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 15 October 2006 - 12:09 AM

Ok I have worked it out, not quite sure if this is the best way,

<table border="0">
  <tr>
    <td>Teams_ID</td>
    <td>RealClub_ID</td>
    <td>Club_ID</td>
    <td>Team_ID</td>
    <td>RealWeekday_ID</td>
    <td>Weekday_ID</td>
    <td>Team_Captian</td>
  </tr>
  <?php do { ?>
    <tr>
      <td><?php echo $row_Team['Teams_ID']; ?></td>
<?php
	  $Club = $row_Team['Club_ID'];
	  $query_Club = "SELECT * FROM clubs WHERE Club_ID = $Club";
      $Club = mysql_query($query_Club, $Medway_Database) or die(mysql_error());
      $row_Club = mysql_fetch_assoc($Club);
?>
      <td><?php echo $row_Team['Club_ID']; ?></td>
      <td><?php echo $row_Club['Club_Name']; ?></td>
      <td><?php echo $row_Team['Team_ID']; ?></td>
<?php
	  $Day = $row_Team['Weekday_ID'];
	  $query_Weekday = "SELECT * FROM weekday WHERE Weekday_ID = $Day";
	  $Weekday = mysql_query($query_Weekday, $Medway_Database) or die(mysql_error());
	  $row_Weekday = mysql_fetch_assoc($Weekday);
?>
      <td><?php echo $row_Team['Weekday_ID']; ?></td>
      <td><?php echo $row_Weekday['Day']; ?></td>
      <td><?php echo $row_Team['Team_Captian']; ?></td>
    </tr>
    <?php } while ($row_Team = mysql_fetch_assoc($Team)); ?>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>

Leaves me with:

Teams_ID RealClub_ID Club_ID Team_ID RealWeekday_ID Weekday_ID Team_Captian
1 1 Anchorians TTC 1 2 Tuesday Jeff Norman
2 1 Anchorians TTC 2 2 Tuesday Sean McMillan
3 1 Anchorians TTC 3 2 Tuesday Roger Sayer
4 1 Anchorians TTC 4 2 Tuesday Gay Turner
5 2 Boxley TTC 1 1 Monady Jim Bradford
6 2 Boxley TTC 2 1 Monady Steve Neilson
7 3 B.T Engineers 1 1 Monady Melvyn Curtis
8 4 Chatham TTC Ark Royal 2 Tuesday Dave Macaulay
9 4 Chatham TTC Black Prince 2 Tuesday Mathew Connell
10 4 Chatham TTC Coventry 2 Tuesday Martin Birley






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users