Jump to content


Photo

adding column contents


  • Please log in to reply
10 replies to this topic

#1 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 14 November 2003 - 01:19 AM

Hello


I\'m sorry if this question seems \"noobish\" but I can\'t figure it out


I have 2 tables

we will call them table1 and table2

I use this query
SELECT 1.blah, 2.id, 1.number FROM table1 1, table2 2  WHERE 2.id = "X" AND 1.blah = "X"
------------------------
id - blah - number
----------------------
X - X - 3.00
X - X - 4.00
X - X - 7.00
X - X - 4.00

I would like to add all of the numbers to give me one value I can use

can anyone help?
thank you

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 14 November 2003 - 09:14 AM

SELECT 1.blah, SUM(1.number )
FROM table1 1, table2 2
WHERE 2.id = 1.blah ### you need a join condition if more than 1 table
AND 1.blah = \"X\"
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 14 November 2003 - 01:37 PM

I get this error when using your query

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause


this is the exact query I used
SELECT m.clan, SUM(m.strength) FROM members m, clan c  WHERE c.id = "1" AND m.clan = "1"


when the I get this working for c.id = 1 and m.clan = 1 then both of the 1\'s become variables, since there are multiple clans and multiple members

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 14 November 2003 - 01:57 PM

Soory about missing the group by. This will give total for blah = X

SELECT 1.blah, SUM(1.number )
FROM table1 1, table2 2
WHERE 2.id = 1.blah
AND 1.blah = \"X\"
GROUP BY 1.blah

and this gives totals for all blahs

SELECT 1.blah, SUM(1.number )
FROM table1 1, table2 2
WHERE 2.id = 1.blah
GROUP BY 1.blah
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 14 November 2003 - 02:50 PM

thanks, in PHPmyAdmin it out puts what I want to see


SELECT m.clan, SUM(m.strength) FROM members m, clan c  WHERE c.id = "1" AND m.clan = "1" Group By m.strength

I made a php page in hopes to output m.strength

but it didn\'t do it
<?phpmysql_pconnect("*****","*****","*******");mysql_select_db("Clan");$Tstrength = mysql_query("SELECT m.clan, SUM(m.strength) FROM members m, clan c  WHERE c.id = "1" AND m.clan = "1" Group By m.strength");print  "$Tstrength";?>
this does not out put a number it outputs
Resource id #3

which really confuses me since no members have an ID of 3,

#6 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 14 November 2003 - 03:21 PM

Humm!!

Resource ID is something which the mysql_query. See the manual for more details.


<?php mysql_pconnect("*****","*****","*******"); mysql_select_db("Clan"); $Tstrength = mysql_query("SELECT m.clan, SUM(m.strength) FROM members m, clan c  WHERE c.id = "1" AND m.clan = "1" Group By m.strength"); while ($row = mysql_fetch_array($Tstrength)) { 	echo $row[0];	echo $row[1];}?>

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#7 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 14 November 2003 - 06:16 PM

Thanks

works, though I only need echo $row[1] echo $row[0] makes it out put a number that I can\'t figure out were it came from,

I must say you guys opened a can of worms being so fast and friendly, now you are stuck with me :P

#8 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 24 November 2003 - 05:10 AM

<?php mysql_pconnect("*****","*****","*******"); mysql_select_db("Clan"); $Tstrength = mysql_query("SELECT m.clan, SUM(m.strength) FROM members m, clan c  WHERE c.id = 1 AND m.clan = 1 Group By m.strength"); while ($row = mysql_fetch_array($Tstrength)) { print" Strength:  $row[1]";}?>

this code works great
if
c.id and m.clan are numbers, but I need them to be a variable

I tried
WHERE c.id =$clan[id] AND m.clan =$clan[id]

but I get

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/virtual/site55/fst/var/www/html/game/clan.php on line 163


any ideas?

what I need is to beable to see the users clan[id]\'s to determin the strength of said clan
the site uses sessions,
if some one is truely interested in troubleshooting with me beyond forum posts I\'ll show the enite source code to you give you a better understanding of what I\'m trying to do

#9 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 24 November 2003 - 05:11 AM

some glitch caused me to multi post

sorry

#10 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 24 November 2003 - 05:11 AM

as said above

the glitch was telling me
\"failed send email\"

and did not tell me it had posted


this forum seems to have an probelm
I posted on Nov 24 and it still says Nov 14 as the last post

#11 deRusett

deRusett
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 24 November 2003 - 06:50 AM

bumping as a test




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users