Monthly Total

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
iceman83
Forum Newbie
Posts: 9
Joined: Sat Sep 06, 2008 1:34 pm

Monthly Total

Post 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 :banghead: or go crazy :crazy:

Thank you

iceman83
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Monthly Total

Post 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.
iceman83
Forum Newbie
Posts: 9
Joined: Sat Sep 06, 2008 1:34 pm

Re: Monthly Total

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Monthly Total

Post 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`
iceman83
Forum Newbie
Posts: 9
Joined: Sat Sep 06, 2008 1:34 pm

Re: Monthly Total

Post 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);
 
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Monthly Total

Post 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.
iceman83
Forum Newbie
Posts: 9
Joined: Sat Sep 06, 2008 1:34 pm

Re: Monthly Total

Post 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..
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Monthly Total

Post 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
Post Reply