Display Query Help

[Intervelopment]

Hey All,

 

Im having a problem with a query and function im writing.

 

I have 2 database tables.

 

The first one, lets call it scores, has the columns email, correct, time, stamp and month

 

the second table, which is called users, has email, dispname, country

 

example of the first table

----------------------------------------------

| email | correct | time | stamp | month |

| [email protected] | 3 | 2 | 12345678 | 11 |

| [email protected] | 6 | 12 | 12345678 | 11 |

| [email protected] | 12 | 30 | 12345678 | 11 |

| [email protected] | 122 | 10 | 12345678 | 11 |

---------------------------------------------------------

 

So what i need to do is extract all the rows with the same email address and the same month, then add the correct fields and the time fields together so i am left with the following result

----------------------------------------------

| email | correct | time | stamp | month |

| [email protected] | 9 | 4 | 12345678 | 11 |

| [email protected] | 134 | 40 | 12345678 | 11 |

---------------------------------------------------------

 

and so on for every email address that happens to be inserted into the scores table.

 

Once that that has been completed, i need to find the top 10 people with the highest "correct" score and with the lowest "time" score, so they are ranked according to their score and time.

 

Once the top 10 have been found, i then need to search the 'users' table to find the dispname and country of each of the outputted top 10 users.

 

and finally i need to display the top 10 as:

 

rank - dispname - country - correct - time

 

If anyone could help me out .... i would be so greatful ... its doing my head in something bad hahah

 

Thanks

[DavidAM]

Something like this:

SELECT users.dispname, users.country, scores.email, scores.month, 
    SUM(scores.correct) AS TotalCorrect, SUM(scores.time) AS TotalTime
FROM scores JOIN users ON scores.email = users.email
GROUP BY users.dispname, users.country, scores.email, scores.month
ORDER BY TotalCorrect DESC, TotalTime ASC
LIMIT 10

 

[Intervelopment]

Thanks for that.

 

Just one quick question.

 

How do i echo out the results of that hahahaa ... im having a total brainfreeze from that query

[Intervelopment]

Ok,

 

This is what i have come up with:

 

						<?php
						function leaderboard(){
							global $database, $session;

							$newmonth = date('m');

							$q = "SELECT users.dispname, users.country, scores.email, scores.month, SUM(scores.correct) AS TotalCorrect, SUM(scores.time) AS TotalTime FROM scores JOIN users ON scores.email = users.email GROUP BY users.dispname, users.country, scores.email, scores.month ORDER BY TotalCorrect DESC, TotalTime ASC LIMIT 10";
							$result = $database->query($q);

							$num_rows = mysql_numrows($result);

							$rank = 1;
							echo '<li style="padding-left: 10px;">';
							echo '<p style="font-size: 17px; line-height: 1.5em;">';
								for($i=0; $i<$num_rows; $i++){
									$dispname = mysql_result($result,$i,"dispname");
									$country = mysql_result($result,$i,"country");
									$totalscore = mysql_result($result,$i,"TotalCorrect");

										//$w = "SELECT countries_name FROM ".TBL_COUNTRIES." WHERE countries_id = '$country'";
										//$resultw = $database->query($w);
										//$namecntry = mysql_result($resultw,$i,"countries_name");

									echo "$rank - ". $dispname ." - ". $totalscore ."<br />";

									$rank++;
								}
							echo '</p>';
							echo '</li>';
						}
					?>

 

That works perfect, except how do i make sure that i only display the records that are in the same month as the $newmonth at the top?

 

Cheers

[emopoops]

[code]WHERE month = '$newmonth'

[/code]