Jump to content

relationships with multiple tables

kemper

By kemper
in PHP Coding Help

 Share

Recommended Posts

kemper Regular Member

kemper

Posted
Posted

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
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.