Jump to content

relationships with multiple tables


kemper

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/64531-relationships-with-multiple-tables/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.