Jump to content


Photo

Assign result of query to variable


  • Please log in to reply
10 replies to this topic

#1 johnny

johnny
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 August 2006 - 02:55 AM

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:

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

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

#2 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 15 August 2006 - 03:04 AM

If I am understanding you correctly, you can simply do this.
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {
       $f1 = $row['week'];
       echo "$f1<br>";
}

Hope this helps,
Tom

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#3 johnny

johnny
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 August 2006 - 03:28 AM

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:

QUERY 1 (which will display a single number in my table):
$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 (which will display another single number in my table):

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

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:

$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;



#4 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 15 August 2006 - 03:32 AM

Didn't you already post a thread for help on the same thing?

#5 johnny

johnny
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 August 2006 - 03:40 AM

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. 

#6 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 15 August 2006 - 03:41 AM

What is the name of the field that you are wanting to retreave from the data base? Is it week or ?

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#7 johnny

johnny
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 August 2006 - 03:49 AM

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.

#8 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 15 August 2006 - 04:26 AM

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).

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#9 johnny

johnny
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 August 2006 - 02:02 PM

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. 

#10 ScottRiley

ScottRiley
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 15 August 2006 - 02:26 PM

If I'm not mistaken, don't you need to use $rows['week'], as opposed to $rows[$week] ?

#11 johnny

johnny
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 August 2006 - 04:10 PM

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. 




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users