Pulling info from 2 tables on same page

[johnny]

Ok this will bore you advanced guys because I'm sure it's very simple, I just don't know how to do it.

I have 2  mysql tables, 'schedule' and 'lineup' 

the schedule table has fields for week #, team 1, team 2, etc....
the lineup has fields for teamname, week#, player1, player2, etc...

I want to have a page that lists the matchups (pulling team 1 vs team 2 from the schedule table) as well as pull the players from each team and put them in. 

so i've got a drop-down menu where you can select what week you want to view that sends you to my "weekdata.php" page.

I can fill in the teamnames with the following code:

<?php
$link = mysql_connect("server address", "username", "pass")
  or die("Could not connect : " . mysql_error());
mysql_select_db("database") or die("Could not select database");
$query="SELECT team1 from schedule WHERE week=$week";
$res = mysql_query($query);
while($rows = mysql_fetch_assoc($res))
{
echo $rows['team1'].'<br>';
}
?>

what i want to do directly under that is have it pull the 'player1' field from the LINEUPS table for team 1.  i know i can do the following:

      <?php
     
$link = mysql_connect("server address", "username", "pass")
  or die("Could not connect : " . mysql_error());
mysql_select_db("database") or die("Could not select database");
$query="SELECT player1 from lineups WHERE week=$week [b]AND teamname=$team1[/b]";
$res = mysql_query($query);
while($rows = mysql_fetch_assoc($res))
{
echo $rows['player1'].'<br>';
}
?> 

but i don't know how to make the $team1 variable I'm adding above be the same team as the $team1 from the schedule page.

i know i probably need to add a:

$team1 = ....  but i don't know what to put. 

[ronverdonk]

Let me try to recap:
[list]
[*]you have a schedule page that sets the $team1 variable
[*]upon completion of the schedule page you call another page that needs that $team1 variable
[*]you don't  know how to pass $team1 from the schedule page to the other page
[/list]
You can set the $team1 variable by doing
[code]$team1= $rows['team1'].'[/code]

You can pass the $team1 variable to the other page via the $_GET or the $_POST, depending how you call the schedule page (action in <form> statement, via click on a <a> link). You can also pass the variable via the $_SESSION. In that case you start a session in both pages. In the schedule page you save the $team1 variable in the $_SESSION array. In the second page you read the variable $team1 from the $_SESSION array.

I hope that sums up what I understand. If not, please explain your problem.

[johnny]

Ok I think it was late.  You understand most of what I want, but I don't think I explained it correctly.

I have one page where you can select from a menu which week's data you want to view.  Upon submission this brings up my weekdata.php page.

On this one page, I want it to show this:

lions vs. tigers (which i can pull using the "SELECT team1 from schedule WHERE week=$week" syntax)

under that i want it to pull the players for the lions from my lineups sheet.  so to do this, it would need to first know the week# and then know which team is team1.  that's why i wanted to use:

"SELECT player1 from lineups WHERE week=$week AND teamname=$team1"


I don't want to go to a schedule page and THEN to a lineups page, I want it all done on the same page.

Maybe I need to include a hidden variable on my week selection page to define what $team1, $team2, etc. are?    My code for that form is:

[code] <form name="form1" method="post" action="weekdata.php" target="mainFrame">
  <?php

$link = mysql_connect("xxxx", "xxxx", "xxxx")
  or die("Could not connect : " . mysql_error());
 
mysql_select_db("xxxx") or die("Could not select database");

$query = "SELECT week FROM schedule";

$result = mysql_query($query) or die('Error: Probably not a category created yet');

echo '<select name="week">';

while ($row = mysql_fetch_array($result))

{
$selected = $row['week']==$row->link_category ? ' $selected="Selected"' : '';

echo '<option value="'.$row[week].'" '.$selected.'>'.$row[week].'</option>';
}

echo '</select>';
?>
                      <input type="submit" name="Submit" value="What Happened This Week?">[/code]

[dual_alliance]

Hi johnny,

I was interested in how to do this as well so a googled it and found this thread on another forum.


Long Explanation:
[url=http://www.informit.com/articles/article.asp?p=30875&seqNum=5&rl=1]http://www.informit.com/articles/article.asp?p=30875&seqNum=5&rl=1[/url]

Nice Short One:
[url=http://www.daniweb.com/techtalkforums/thread22066.html]http://www.daniweb.com/techtalkforums/thread22066.html[/url]

Hope it helps,

dual_alliance