kemper Posted August 12, 2007 Share Posted 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? Link to comment https://forums.phpfreaks.com/topic/64531-relationships-with-multiple-tables/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.