Page 1 of 1
Monthly Total
Posted: Sat Sep 13, 2008 10:28 pm
by iceman83
Greeting Mates,
I am trying to Join 3 tables together reports, reports2, reports3.. The reason why i want to do this is to display the totals for all 3 games for each member of our online group each month..
heres what i got for so far for the join..
Code: Select all
$query_scores = "SELECT r1.total, r1.bonus, r2.total2, r2.bonus2, r3.total3, r3.bonus
FROM reports r1
JOIN reports2 r2
LEFT JOIN reports3 r3
ON r1.user_name = r2.user_name
AND MONTH(date)=$today_month AND YEAR(date)=$today_year";
$result_scores = mysql_query($query_scores);
while ($line = mysql_fetch_array($result_scores)) {
$total_score2 = $line['total'];
$bonus = $line['bonus'];
$total_score = $bonus + $total_score2;
$total += $total_score;
}
can someone help me before i hit my head to many times? lol

or go crazy
Thank you
iceman83
Re: Monthly Total
Posted: Sat Sep 13, 2008 10:39 pm
by josh
you need to put "as" if youre using alias. Also you have a join without a condition. Also your date field im guessing is probably ambiguous.
Re: Monthly Total
Posted: Sat Sep 13, 2008 10:45 pm
by iceman83
what you mean but ambiguous? because i know there is not problem with the date since i use to use
Code: Select all
$query_scores = "SELECT total, bonus FROM reports WHERE user_name='$user_name' AND MONTH(date)=$today_month AND YEAR(date)=$today_year";
to display for 1 database and worked just fine..
i know the 3rd join didn't have a condition because last join script i did was only for 2 tables. im not sure on how to do 3 tables thats why im trying to get help on that one.. but also its be along time since i did joins. lol
Re: Monthly Total
Posted: Sun Sep 14, 2008 5:03 am
by josh
select a.field as field1, b.field as field2 c.field as field3
from a
left join a on a.field = b.field
left join c on c.field = b.field
ambiguous means, if you had a field called "field" in all 3 tables, in your where clause you must specify the table name ( and everywhere else ), either that or use aliases
`tableA`.`field`, not `field`
Re: Monthly Total
Posted: Sun Sep 14, 2008 8:59 am
by iceman83
Well last night before hitting the sack i got it somewhat working..
here is what i got so far.
Code: Select all
$query_scores = "SELECT r1.total, r1.bonus, r2.total2, r2.bonus2, r3.total3, r3.bonus3
FROM reports as r1
JOIN reports2 as r2 ON r1.user_name = r2.user_name
JOIN reports3 as r3 ON r3.user_name = r2.user_name
WHERE MONTH(r1.date)=$today_month AND YEAR(r1.date)=$today_year AND MONTH(r2.date)=$today_month AND YEAR(r2.date)=$today_year AND MONTH(r3.date)=$today_month AND YEAR(r3.date)=$today_year";
i don't get any errors but nothing shows up but where the total of everyones score it shows 22 which is a test score i made and that would be on reports table.. but doesn't show my user_name on the table either.
yeah i found out about the ambiguous last night when i added
Code: Select all
$result_scores = mysql_query($query_scores) or die(mysql_error());
instead of
$result_scores = mysql_query($query_scores);
Re: Monthly Total
Posted: Sun Sep 14, 2008 10:55 am
by josh
just because you join on the username field doesnt mean mysql will select it, try adding it to the SELECT __ FROM part of the query.
Re: Monthly Total
Posted: Sun Sep 14, 2008 11:24 am
by iceman83
it does the same when adding r1.user_name ans stuff but see above the 3 join tables i have a query there to get the user_name from users..
and then in the while loop i have $user_name = $line['user_name']; so im not sure what to do from here..
Re: Monthly Total
Posted: Mon Sep 15, 2008 5:55 am
by josh
iceman83 wrote: but see above the 3 join tables i have a query there to get the user_name from users..
no you dont