[SOLVED] Retrieving Data from Multiple Tables???

[kemper]

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` 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:

<?php

// mySQL Table
$division_id = $_GET['division_id'];
$sql = "SELECT * FROM `f2007_scheduling` WHERE division='$division' ORDER BY date ASC";
$result = mysql_query($sql) or die(mysql_error());
$i = 0;


echo "</p>
<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['division']."</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['division']."?

 

This will probably give me a basic idea to make my database more efficient, moving teams into their own table for proper autoamtic standings generation also.

[simcoweb]

First, this line is wrong. Your WHERE doesn't match the variable you've named:

 

$sql = "SELECT * FROM `f2007_scheduling` WHERE division='$division' ORDER BY date ASC";

 

should be:

 

$sql = "SELECT * FROM `f2007_scheduling` WHERE division='$division_id' ORDER BY date ASC";

[MadTechie]

NO DUPLICATE POSTS!

 

you have the same post here

[kemper]

How can I have the wrong Where, when it is only pulling from f2007_scheduling, which contains only "$division"?

 

I would like to pull from f2007_division, but am unsure how to do so.

[MadTechie]

No $division wasn't set, but $division_id was

 // mySQL Table
$division_id = $_GET['division_id'];
$sql = "SELECT * FROM `f2007_scheduling` WHERE division='$division' ORDER BY date ASC";

 

as for joins

nice example here

[kemper]

Oh Crap.  I just noticed that.  I had that set in Spring season with division_id as a text field containing the full division name.  How can this be corrected?

[kemper]

OK, so my script should be:

<?php

 

// mySQL Table

$sql = "SELECT * FROM `f2007_scheduling` WHERE division='$division' ORDER BY date ASC";

$result = mysql_query($sql) or die(mysql_error());

$i = 0;

 

 

echo "</p>

<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['division']."</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>

?>

How do I link up the division table?

[simcoweb]

Simply by changing your query. The WHERE is looking for a value from $division right now. If you change it to $division_id then the value would be passed from the form.

[kemper]

you lost me.

[MadTechie]

Read the lesson here and here,

 

this will display both tables

SELECT * FROM f2007_scheduling, f2007_division WHERE division='$division' ORDER BY date ASC

 

this will display both tables where division is $division

SELECT * FROM f2007_scheduling, f2007_division WHERE f2007_scheduling.division='$division' AND  f2007_division.division_id = '$division' ORDER BY date ASC

 

as for joins

nice example here

[kemper]

Thanks MadTechie!