Jump to content

Archived

This topic is now archived and is closed to further replies.

johnny

Assign result of query to variable

Recommended Posts

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>';
}

Share this post


Link to post
Share on other sites
If I am understanding you correctly, you can simply do this.
[code=php:0]
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {
      $f1 = $row['week'];
      echo "$f1<br>";
}[/code]

Hope this helps,
Tom

Share this post


Link to post
Share on other sites
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;

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
What is the name of the field that you are wanting to retreave from the data base? Is it week or ?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Please post the exact names of the fields in both tables that you are wanting to add. For instance if the week field is named week or CelebTotal.$week($week would mean the you create a new field for each week).

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

×

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.