Jump to content

Pulling info from 2 tables on same page


johnny

Recommended Posts

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. 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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
Link to comment
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.

×
×
  • 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.