Monthly total - 2 tables joined

[Smackie]

Greetings mates,

 

I'm trying to join 2 tables together to show the totals for each member each month.

 

$top_scorers = array();
$query_users = "SELECT user_name FROM users ORDER BY user_name";
$result_users = mysql_query($query_users);
while ($line = mysql_fetch_array($result_users)) {
$user_name = $line['user_name'];
$total = 0;
$query_scores = "SELECT r1.total, r1.bonus, r2.total2, r2.bonus2
             FROM reports as r1
JOIN reports2 as r2
ON r1.user_name = '$user_name'
WHERE 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;

}
$top_scorers[$user_name] = $total;
$month_total += $total;
}

 

I know im probably doing something not right but not sure but im getting this warning

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in htdocs\Scoreboard\monthlyscore.php on line 45

 

now the join starts from the $query_score, I hope someone can help me figure this one out..

 

thank you

Smackie

[genericnumber1]

change

$result_scores = mysql_query($query_scores);

to

$result_scores = mysql_query($query_scores) or die(mysql_error());

 

It will give you some output to help you fix your sql syntax.

[Smackie]

Column 'date' in where clause is ambiguous

 

ok but not sure how date would effect that... how can i fix this? thats a first time i ever got that lol

 

but up at the top of my page i got this code for the date.. not sure if it might have something to do with the ambiguous

 

<?php
include 'db.php';
$today_date = date("m/Y");
if (($_REQUEST['month'] == 99) || (!$_REQUEST['month'])) {
$today_month = date("m");
} else {
$today_month = $_REQUEST['month'];
}
if (($_REQUEST['year'] == 9999) || (!$_REQUEST['year'])) {
$today_year = date("Y");
} else {
$today_year = $_REQUEST['year'];
}
?>

 

and thanx for saying to add  "or die(mysql_error());

 

i should have remember it..

[genericnumber1]

when you say MONTH(date) and YEAR(date) what are you actually trying to retrieve? what is "date"? a column? if so, try specifying what table it is in.

[Smackie]

what im tring to retrieve is the Month on one and the Year on the other..

 

it should be looking for all the users posted a report in Month/Year.. but its suppose to be set for current month then at the bottom users can view older months and years to see how active our group is.

 

if you look at http://www.seashadows.org/index.php?seashadows=monthly-score you would see the script working properly (but thats only 1 table being used which is reports  and well we just added 2 new games and needing the monthly score to show users scores altogether in one showing.

 

 

[genericnumber1]

you need to specify which table you're pulling date from... in this case r1.date or r2.date

[Smackie]

yeah i figured it out lol i better hit the sack so i can get a fresh start on this page.. thank you all for your help..

 

Smackie

[Smackie]

Greetings,

 

      now i just ran into a new problem i might be over looking alittle bit but not sure.. here is the script i have now.

 

$top_scorers = array();
$query_users = "SELECT user_name FROM users ORDER BY user_name";
$result_users = mysql_query($query_users);
while ($line = mysql_fetch_array($result_users)) {
$user_name = $line['user_name'];
$total = 0;
$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";

 

but when i check the page out it shows no user_name and has no scores but when i look at the totals for everyone it shows 22.. thats from a test report i did..

 

to see what im talking about check out http://69.66.65.137/index.php?seashadows=monthly-score it should look more like http://seashadows.org/index.php?seashadows=monthly-score

 

only thing is the seashadows link is set for only 1 database..

 

thank you

Smackie

[Smackie]

I am having no luck here. anyone got a clue on why its not showing anything thats its suppose to?