FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Display SUM(*) from JOIN table





medesignz
please note - i have not gotten over this problem by assigning the SUM with a name

============================================

Hello all... I'm in a bit of a muddle... and wondering if you may shed some light as to how I can rectify the problem.

I have four tables that a joint using mySQL:
1) promoters_tbl
2) venue_tbl
3) gigs_tbl
4) tickets_tbl

Each table has a linking factor to one or more of the other tables

venue_tbl.promoter = promoters_tbl.id
gigs_tbl.venue = venue_tbl.id
tickets_tbl.event = gigs_tbl.id
and the qty of tickets is how I want to sum it up... (ie, venue who has accumulated most ticket sales)

NOW...

I have the mysql code as such
Code:
SELECT venue_tbl.id, venue_tbl.venueName, promoters_tbl.company, tickets_tbl.event, COUNT( * ) , SUM( test_ipn_tbl.qty )
FROM promoters_tbl, venue_tbl, gigs_tbl, tickets_tbl
WHERE promoters_tbl.profile ='venue' AND venue_tbl.promoter = promoters_tbl.id AND venue_tbl.id = gigs.venue
AND gigs.id = tickets_tbl.event
GROUP BY venue_tbl.id
ORDER BY SUM( tickets_tbl.qty ) DESC
LIMIT 0 , 30


I can get this working fine on mySQL but when I pull it over to PHP it doesnt seem to pull any of the results other than information from the promoters_tbl

I have tried the following:
Code:

$sumoftickets = $row['sum(tickets_tbl.qty)'];

and

$sumoftickets = $row['sum(qty)'];
But to no success... Please help if you can suggest anything, much appreciated
Fire Boar
I would suggest using the SQL "AS" clause. For instance, instead of "SUM(test_ipn_tbl.qty)" use "SUM(test_ipn_tbl.qty) AS total". Then you can (in both SQL and PHP) refer to that column as "total".
Related topics
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.