relationships with multiple tables

kemper · August 12, 2007

I am still somewhat new at this stuff, been able to modify code to my likings, but am starting to write my own script for my schedules. I am having difficulty trying to build a relationship with different tables.

MY TABLES:

CREATE TABLE `f2007_divisions` (

`division_id` int(5) unsigned NOT NULL auto_increment,

`age` text NOT NULL,

`division` text NOT NULL,

PRIMARY KEY (`division_id`)

CREATE TABLE `f2007_scheduling` (

`club_id` smallint(3) NOT NULL default '0',

`status` text NOT NULL,

`division_id` int(5) NOT NULL default '0',

`game_id` int(6) unsigned NOT NULL auto_increment,

`date` date NOT NULL default '0000-00-00',

`notes` text NOT NULL,

`time` time NOT NULL default '00:00:00',

`home_team` varchar(50) NOT NULL default '',

`h_score` text NOT NULL,

`field` text NOT NULL,

`field_no` text NOT NULL,

`visit_team` varchar(50) NOT NULL default '',

`v_score` text NOT NULL,

`sched_user` smallint(5) unsigned NOT NULL default '0',

PRIMARY KEY (`game_id`),

KEY `division_id` (`division_id`),

How do I build the relationship so that when I write:

<table width='100%' cellpadding='0' cellspacing='1' class='tbl-border'>
<tr>
	<td><b><font size='1'>Division:</font></b></td>
	<td><b><font size='1'>Game ID:</font></b></td>
	<td><b><font size='1'>Date:</font></b></td>
	<td><b><font size='1'>True Time:</font></b></td>
	<td><b><font size='1'>Teams:</font></b></td>
	<td><b><font size='1'>Field:</font></b></td>
</tr>\n";
while ($data = dbarray($sql)) {
	$i % 2 == 0 ? $tclass='tbl1' : $tclass='tbl2';

	echo "<tr>
	<td class='$tclass'><font size='1'>".$data['div']."</font></td>
	<td class='$tclass'><font size='1'>".$data['game_id']."</font></td>
	<td class='$tclass'><font size='1'>".$data['date']=date("D, M j, Y",strtotime($data['date']))."</font></td>
	<td class='$tclass'>
	<p style='margin-top: 0; margin-bottom: 0'><font size='1'>".$data['time']."</font></p></td>
	<td class='$tclass'><p style='margin-top: 0; margin-bottom: 0'><font size='1'>".$data['home_team']." 
vs.</font></p>
	<p style='margin-top: 0; margin-bottom: 0'><font size='1'>".$data['visit_team']."</font></font></td>
	<td class='$tclass'><font color='#0000FF' size='1'>".$data['field']." ".$data['field_no']."</font></td>
</tr>\n";
}
echo "</table>

I can have "division" from f2007_division table display in place of ".$data['div']."?

jeffrydell · August 12, 2007

$sql = "SELECT * FROM `f2007_divisions`. `f2007_scheduling` WHERE f2007_divisions.division_id = f2007_scheduling.division_id";

$res = mysql_query($sql);

... should join the tables by division_id and give you all the fields included in the two tables.

Then if you want to spank that all in to an array named Sched...

$Sched = mysql_fetch_array($res);

Is that what you were looking for?

kemper · August 12, 2007

I believe so... But what if I want to expand it more? I cannot add to the where statement.

Lets say that I change "home_team" and "visit_team" to team_id and add team details to a different table.

This makes more complex, yet I am not sure what $res exactly is, as you documented.

cooldude832 · August 12, 2007

I think your issue is you are using too few tables. Try making tables for team names fields names etc etc and limit the data in this primary table to be primarly IDS and then query off those.

jeffrydell · August 12, 2007

cooldude832 is correct, you should have a table for each set of like items (teams, fields, etc.)

The WHERE can be expanded to accommodate as many table joins as necessary.

Example:

http://AgilityEvents.net/events.php uses:

select

clubs.name AS ClubName,

clubs.url as ClubURL,

clubs.sec_id as ClubSecretaryID,

venues.name AS VenueName,

events.*,

unix_timestamp(events.start_date) as unix_start_date,

unix_timestamp(events.end_date) as unix_end_date,

unix_timestamp(events.post_date) as unix_post_date,

unix_timestamp(events.close_date) as unix_close_date

from

events,

venues,

event2venue,

clubs

where

events.club_id = clubs.club_id

AND

events.event_id = event2venue.event_id

AND

venues.venue_id = event2venue.venue_id

Flexible enough for ya?

kemper · August 12, 2007

I was already thinking that, but it is hard to just jump into something that large without understanding about the relationships with tables.

I do have the following tables now:

f2007_schedule

f2007_divisions

f2007_teams

fields

I think that would cover all of my variables, but not exactly sure where to begin now.

cooldude832 · August 12, 2007

Well the ultimate goal is to spit out the database data in some formatted way. Best way is to think of what you need.

First lets say we want a specific's team schedule (very basic) We would need to know that team so lets say on Page A we have a drop down menu of teams that you can generate off a simple (select TeamID, TeamName From f2007_teams) then spit it out in a while loop with it being a big select and the values being the teamID the physical drop down string being the TeamName (For humans to read and value for computer to read) i.e

while($row = mysql_fetch_array($result){

echo "<option value=\"".$row['TeamID']."\">".$row['TeamName']."</option>";

}

so we now have that pretty simple. Next part is to handle the submission of this selection from a user. It will come in as $_POST['team'] (for say) so what we need is on page to query the schedule thing for the teams schedule (I'm assuming 1 meeting/game is a single row like it should be ) so we can say for the query here

select * from schedule Where TeamID = $_POST['team']

Run that and we can loop out the results, however because the schedule table only contains TeamIds, FieldIDs, etc etc we need to help this out so before that lets query the teams/fields tables for their data and throw it in array like saying

select TeamID, TeamName from Teams and same for fields. Then in the while say $teams[$row['TeamID'] = $row['TeamName'] (same for fields) then in the while of producing the schedule say for the team names simply $teams[$row['TeamIDhome or away'] and you can get the data do the same for fields.

Hope this clears it ups odds are it only confused you more (A note you might need to do some stuff if you only want a single division's data either by setting a session or using some sort of Get action.

kemper · August 12, 2007

You have gone beyond my goal for now.

Right now, my focus is to produce my schedules to appear:

<table width='100%' cellpadding='0' cellspacing='1' class='tbl-border'>
<tr>
	<td><b><font size='1'>Division:</font></b></td>
	<td><b><font size='1'>Game ID:</font></b></td>
	<td><b><font size='1'>Date:</font></b></td>
	<td><b><font size='1'>True Time:</font></b></td>
	<td><b><font size='1'>Teams:</font></b></td>
	<td><b><font size='1'>Field:</font></b></td>
</tr>\n";
while ($data = dbarray($sql)) {
	$i % 2 == 0 ? $tclass='tbl1' : $tclass='tbl2';

	echo "<tr>
	<td class='$tclass'><font size='1'>".$data['div']."</font></td>
	<td class='$tclass'><font size='1'>".$data['game_id']."</font></td>
	<td class='$tclass'><font size='1'>".$data['date']=date("D, M j, Y",strtotime($data['date']))."</font></td>
	<td class='$tclass'>
	<p style='margin-top: 0; margin-bottom: 0'><font size='1'>".$data['time']."</font></p></td>
	<td class='$tclass'><p style='margin-top: 0; margin-bottom: 0'><font size='1'>".$data['home_team']." 
vs.</font></p>
	<p style='margin-top: 0; margin-bottom: 0'><font size='1'>".$data['visit_team']."</font></font></td>
	<td class='$tclass'><font color='#0000FF' size='1'>".$data['field']." ".$data['field_no']."</font></td>
</tr>\n";
}
echo "</table>

I can use WHERE statement to display by a specific division. I have done this already by displaying all data from one table (which I know is inefficient).

Here was last seasons schedules:

<?php

switch ($division) {
    case 'g910b':
       $division = "Girls U-9/10 Blue";
    break;

   case 'g910r':
       $division = "Girls U-9/10 Red";
   break;

   case 'u7b':
       $division = "U-7 Blue";
   break;

   case 'u8b':
       $division = "U-8 Blue";
   break;

   case 'u8r':
       $division = "U-8 Red";
   break;

   case 'u9b':
       $division = "U-9 Blue";
   break;

   case 'u9r':
       $division = "U-9 Red";
   break;

   case 'u10b':
       $division = "U-10 Blue";
   break;

   case 'u10r':
       $division = "U-10 Red";
   break;

   case 'u10y':
       $division = "U-10 Yellow";
   break;

   case 'u11ab':
       $division = "U-11 A Blue";
   break;

   case 'u11ar':
       $division = "U-11 A Red";
   break;

   case 'u11by':
       $division = "U-11 B Yellow";
   break;

   case 'u11bg':
       $division = "U-11 B Green";
   break;

   case 'u11cp':
       $division = "U-11 C Purple";
   break;

   case 'u11co':
       $division = "U-11 C Orange";
   break;

   case 'u11cw':
       $division = "U-11 C White";
   break;

   case 'u12ab':
       $division = "U-12 A Blue";
   break;

   case 'u12by':
       $division = "U-12 B Yellow";
   break;

   case 'u12bg':
       $division = "U-12 B Green";
   break;

   case 'u12cp':
       $division = "U-12 C Purple";
   break;

   case 'u12co':
       $division = "U-12 C Orange";
   break;

   case 'u13ab':
       $division = "U-13 A Blue";
   break;

   case 'u13ar':
       $division = "U-13 A Red";
   break;

   case 'u13by':
       $division = "U-13 B Yellow";
   break;

   case 'u13bg':
       $division = "U-13 B Green";
   break;

   case 'u13cp':
       $division = "U-13 C Purple";
   break;

   case 'u14ab':
       $division = "U-14 A Blue";
   break;

   case 'u14ar':
       $division = "U-4 A Red";
   break;

   case 'u14by':
       $division = "U-14 B Yellow";
   break;

   case 'u14cp':
       $division = "U-14 C Purple";
   break;

   case 'u15r':
       $division = "U-15 Red";
   break;

   case 'u15w':
       $division = "U-15 White";
   break;

   case 'u15b':
       $division = "U-15 Blue";
   break;

   case 'u16rx':
       $division = "U-16 Red -- X";
   break;

   case 'u16ry':
       $division = "U-16 Red -- Y";
   break;

   case 'u16b':
       $division = "U-16 Blue";
   break;

   case 'u17r':
       $division = "U-17 Red";
   break;

   case 'u17b':
       $division = "U-17 Blue";
   break;

   case 'u1819r':
       $division = "U-18/19 Red";
   break;

   case 'u1819b':
       $division = "U-18/19 Blue";
   break;

   default:
       $division = "Default as none of the above were it.";
   break;
}

if (isset($readmore) && !isNum($readmore)) fallback(FUSION_SELF);
opentable("Spring 2007 Scores & Schedules for $division");

    // mySQL Table
    $db_con = mysql_connect(****, ******, ******) or die("Connetion to database failed!");
    mysql_select_db(*****);
$division = $_GET['division'];
$sql = "SELECT * FROM `s2007schedules` WHERE division='$division' ORDER BY game_no ASC";
$result = mysql_query($sql) or die(mysql_error());
$i = 0;


echo "</p>
<table width='100%' border='1' cellspacing='0' cellpadding='0' bordercolor='#3c64a0'>";
         
    while ($row = mysql_fetch_array($result)) {
    echo "<tr>
    <td valign='top' width='100%'>
    <table border='1' width='100%' id='table1' cellspacing='0' cellpadding='0' bordercolor='#2B538E'>
<tr>
	<td style='border-left-style: solid; border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-style: solid; border-top-width: 1px; border-bottom-style: none; border-bottom-width: medium'>
	<table border='0' width='100%' id='table2' cellspacing='0' cellpadding='2'>
		<tr>
			<td width='50%'>
			<font color='#FF0000' face='Arial' size='1'><b>Date: </b> </font>
			<font face='Arial' size='1'>" . $row['date'] . "</font></td>
			<td width='20%'><b><font face='Arial' size='1' color='#ff0000'>Game ID: </font>
			</b><font face='Arial' size='1'>" . $row['gameid'] . "</font></td>
			<td width='30%'><b><font face='Arial' size='1' color='#ff0000'>Status: </font>
			</b><font face='Arial' size='1'>" . $row['status'] . "</font></td>
		</tr>
	</table>
	</td>
</tr>
<tr>
	<td style='border-left-style: solid; border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px'>
	<table border='0' width='100%' id='table3' cellspacing='0' cellpadding='2'>
		<tr>
			<td width='15%'><font face='Arial' size='1' color='#ff0000'><b>Time: </font></b>
			<font face='Arial' size='1'>" . $row['time'] . "</font></td>
			<td width='30%'><b><font face='Arial' size='1' color='#ff0000'>Visitors: </font></b>
			<font face='Arial' size='1'>" . $row['visitor'] . "</font></td>
			<td width='30%'><b><font face='Arial' size='1' color='#000000'><b>@ </b></font><font face='Arial' size='1' color='#ff0000'>Home: </font></b>
			<font face='Arial' size='1'>" . $row['home'] . "</font></td>
			<td width='25%'><b><font face='Arial' size='1' color='#ff0000'>Field: </font></b><a target='_blank' href=" . $row['field_link'] . "><font face='Arial' size='1' color='#3c64a0'><b><u>" . $row['field'] . " " . $row['field_no'] . "</u></b></font></a></td>
		</tr>
	</table>
	</td>
</tr>
</table>
</td>
    </tr>";
    }
echo "</table>\n";

  // mySQL ends
closetable();
?>

I am just looking to take the first step to make this efficient. Whe select menus will come later.

Sign In

relationships with multiple tables

Recommended Posts

kemper

Link to comment

Share on other sites

jeffrydell

Link to comment

Share on other sites

kemper

Link to comment

Share on other sites

cooldude832

Link to comment

Share on other sites

jeffrydell

Link to comment

Share on other sites

kemper

Link to comment

Share on other sites

cooldude832

Link to comment

Share on other sites

kemper

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information