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.

Last Season's Schedule code:

<?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();
?>

Obviously, this is innefficient due to the fact that all information is contained in one table. I now understand that I was not using enough tables for all of my variables.

My NEW 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`),

CREATE TABLE `f2007_registrations` (

`id` int(10) unsigned NOT NULL auto_increment,

`datestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,

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

`Club` text NOT NULL,

`Team` text NOT NULL,

`age` text NOT NULL,

`Division` text NOT NULL,

`Coach` text NOT NULL,

`Coach_Address` text NOT NULL,

`Coach_City` text NOT NULL,

`Coach_State` text NOT NULL,

`Coach_Zip` text NOT NULL,

`Coach_Phone` text NOT NULL,

`Coach_Cell` text NOT NULL,

`Coach_email` text NOT NULL,

`Manager` text NOT NULL,

`Manager_Address` text NOT NULL,

`Manager_City` text NOT NULL,

`Manager_State` text NOT NULL,

`Manager_Zip` text NOT NULL,

`Manager_Phone` text NOT NULL,

`Manager_Cell` text NOT NULL,

`Manager_email` text NOT NULL,

`Field` text NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `id_2` (`id`),

KEY `id` (`id`)

CREATE TABLE `fields` (

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

`field_name` text NOT NULL,

`map_filename` varchar(100) NOT NULL default '0',

`clubs_name` smallint(3) unsigned NOT NULL default '0'

How can I make my above code more efficient and build relationships with the different tables?

Sign In

relationships with multiple tables

Recommended Posts

kemper

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information