Use of multiple tables in query

[Das_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_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),

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_id' 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['div']."</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['div']."?

[cahva]

Heres an example join:

$sql = 	"SELECT * FROM `f2007_scheduling` AS s ".
		"LEFT JOIN f2007_divisions AS d ON s.division_id=d.division_id ".
		"WHERE s.division_id='$division_id' ORDER BY s.date ASC";

Homever, replace * with the actual fields you need. You also need to use the tablenames (I hae given them shorten names in the query, s= f2007_schedules and d=f2007_divisions).

So instead of * use:

SELECT s.division div,s.game_id AS game_id,s.date AS date,d.division AS div ...

And I quess the code that you gave is not a working code because that way the query wont surely work.

// Whats this? :)
while ($data = dbarray($sql)) {

// replace that with:
while ($data = mysql_fetch_assoc($result)) {

With that you you can get the proper info with eg. $data['div'],$data['game_id'] etc.