johnny

Ok I have the following two queries that return a numeric value: QUERY 1: $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; } QUERY 2: $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f2 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; } I want to build a THIRD QUERY and I want that to return the SUM of the first two. I realize that I will likely have to build in the first two queries into this third query, so I've tried this: $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $res=mysql_query($query); while($rows = mysql_fetch_assoc($res));{ $f1 = $rows[$week]; }; $query2=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f3 AND schedule.week = $week AND schedule.team = '1' "; $res2=mysql_query($query2); while($rows = mysql_fetch_assoc($res));{ $f2 = $rows[$week]; }; $total= $f1 + $f2; print $total; But that comes back Resource Id #3. I have tried to do this: $res = mysql_query($query); $res2 = mysql_query($query2); $total = $res + $res2; print $total; but that always returns a numeric value of 7. the answer is not 7. heck I can change the queries to go fetch out different cells in the table and it still comes back 7. Help!

I use $rows[$week] because the column that i'm selecting is a variable that is selected via a menu.  Thus in week 1, it makes it $rows['1'] All my queries work as far as pulling information from both the CelebTotal and schedule table, the only thing I can't figure out is how to sum up the results of the queries. 

CelebTotal table ______________ Celeb  |    1    |    2    | Pam    |    4    |    0    | Bill      |    2    |    -1  | Frank  |    3    |    1    | Chris    |  -3    |  6    | Schedule table ______________ week  |  team  |    f1    |    f2    | 1        |      1    |    Pam  |  Frank | 1        |      2    |  Bill    |  Chris  | The layout of the page I'm trying to create is like this: Team 1  |  total score  |    vs    |  total score  |  Team 2 Pam        |      4          |          |      2          |    Bill Frank      |      3          |          |      -3          |    Chris I can make it do all that, what I can't do is make the total score add up the numbers below it.  The query has to pull the celeb names from the schedule sheet and then get their week score from the CelebTotal sheet. 

well the numbers that will be added up are all in the $week field, which of course changes with the submission of my form.  however the query must not only choose the week field but must only choose those certain rows in the field that correspond to values in my "schedule" table.

Yes but I didn't think I explained it very well and thus was trying to rephrase it more clearly.  I didn't want the other answers to cloud anyone's suggestions. Sorry if I'm breaking the rules, and if the mods aren't happy feel free to delete this post.  Not trying to flood the board. 

Well what I really want to do is this: I'm going to LEAVE my original query.  I want that value printed out, and my original query works fine for that. I also have another similar query which I can print out in the same way. What I want to do at another point in the page is build a NEW query that ADDS the results of these two queries.  I realize I will probably have to recode the other queries into this new query, and I'm ok with that. So, to make a long story short (too late, probably! haha)  I'm going to have the following: [b]QUERY 1 (which will display a single number in my table):[/b] $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; } [b] QUERY 2 (which will display another single number in my table):[/b] $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f2 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; } [b]What I want to add is this QUERY (which is built to get the same two results as in query 1 and 2 and ADD them together).  This is what I'm trying right now, to no avail:[/b] $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $res=mysql_query($query); while($rows = mysql_fetch_assoc($res));{ $f1 = $rows[$week]; }; $query2=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f2 AND schedule.week = $week AND schedule.team = '1' "; $res2=mysql_query($query2); while($rows = mysql_fetch_assoc($res));{ $f2 = $rows[$week]; }; $total= $f1 + $f2; print $total;

I want to make multiple queries that return numeric values.  On the same page I want to return the sum of these queries.  Here is one of my queries, which returns a numeric value: [code]$query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; }[/code] Later in the page I want that value to be, say, $f1, and then at the end I'm going to have: $total = $f1 + $f2 print $total I figure I have to change something in the following lines, but don't know what/how: $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; }

That is displaying "Resource id #3" - WTF is that?

What does the "++" mean/do?  That code only includes one of the two queries I want to add together. 

Ok I have the following queries that display numeric values on my page: [code]      <?php $link = mysql_connect("xxx", "xxx", "xxx")   or die("Could not connect : " . mysql_error()); mysql_select_db("xxx") or die("Could not select database"); $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; } ?>       <?php $link = mysql_connect("xxx", "xxx", "xxx")   or die("Could not connect : " . mysql_error()); mysql_select_db("xxx") or die("Could not select database"); $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f2 AND schedule.week = $week AND schedule.team = '1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row [$week].'<br>'; } ?>[/code] I want to ADD these two values and display that number on the same page.  I have tried something like this, but it's not working: [code]<?php $link = mysql_connect("xxx", "xxx", "xxx")   or die("Could not connect : " . mysql_error()); mysql_select_db("xxx") or die("Could not select database"); $query=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f1 AND schedule.week = $week AND schedule.team = '1' "; $query2=" SELECT CelebTotal.$week FROM CelebTotal,schedule WHERE CelebTotal.Celeb = schedule.f2 AND schedule.week = $week AND schedule.team = '1' "; $res=mysql_query($query); $res2=mysql_query($query2); $total = $res+$res2; echo $total; ?>[/code] It returns a number, but that number is always 7 and that's not correct. 

well that would be news to me and my database! lol i have gotten other queries to work on that table, just not the one i'm asking about.

yes, 1 and 2 refer to week 1, week 2...

Ok I am beating my head against the wall trying to do this.  Here are the tables I am working with: tablename = schedule  __________________ week  |  team  |  name 1      |    tm1  |  A 1      |    tm2  |  B 2      |  tm1  |  C 2      |    tm2  |  B tablename = points ___________________ name    |  1    |  2  A        |  4    |  -2 B        |  0    |  3 C        |  2    |  1 so I have a page where I want it to able to display the teams that play eachother (week1.tm1. vs week2.tms) as well as the player on each team AND how many pts they scored.  so far I can get the teams and the players pretty easily, because they are in the same table.  what i cannot figure out is how to code it to find that in Week 1, Team 1 had player A...then take that to the points page and pull the points that A scored in Week 1.  I am currently trying this: [code]<?php $link = mysql_connect("xxx", "xxx", "xxx")   or die("Could not connect : " . mysql_error()); mysql_select_db("xxx") or die("Could not select database"); $query=" SELECT points.1 FROM points.schedule WHERE points.name= schedule.name AND schedule.week = '1' AND schedule.team = 'tm1' "; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { echo $row ['1'].'<br>'; } ?>[/code] I have also tried something like: [code] "SELECT 1 FROM points WHERE name = (SELECT name FROM schedule WHERE week='1' AND team='tm1') "[/code] Both give me "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in line xx" I'll buy someone a keg to figure this out lol.

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]

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.