Page 1 of 1

Display query help

Posted: Sat Nov 21, 2009 7:56 am
by 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 |
| test@test.com | 3 | 2 | 12345678 | 11 |
| test@test.com | 6 | 12 | 12345678 | 11 |
| testing@testing.com | 12 | 30 | 12345678 | 11 |
| testing@testing.com | 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 |
| test@test.com | 9 | 4 | 12345678 | 11 |
| testing@testing.com | 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

Re: Display query help

Posted: Sat Nov 21, 2009 8:05 am
by iankent
The first one you need to use something like this:

Code: Select all

SELECT email, SUM(correct) AS correct, SUM(time) AS time, stamp, month FROM scores s INNER JOIN users u ON (u.email = s.email) GROUP BY email, month, stamp;
That gets a result from the table grouped by email, then month, then stamp. The other fields (correct and time) are added together using SUM()

To get the top 10 highest correct score, you need to use ORDER BY and LIMIT, e.g.:

Code: Select all

SELECT email, SUM(correct) AS correct, SUM(time) AS time, stamp, month FROM scores s INNER JOIN users u ON (u.email = s.email) GROUP BY email, month, stamp ORDER BY correct DESC LIMIT 10;
ORDER BY correct DESC is telling it to use the correct column and in descending order
LIMIT 10 is telling it to get the top 10 results

Not sure how you'd take into account both highest score and lowest time, I suppose you could just add them together and order them like this:

Code: Select all

SELECT email, SUM(correct) AS correct, SUM(time) AS time, stamp, month, correct+time AS combined FROM scores s INNER JOIN users u ON (u.email = s.email) GROUP BY email, month, stamp ORDER BY combined DESC LIMIT 10;
Finally, because of the INNER JOIN, its already including the users table, so just add the additional fields you need to the SELECT bit, e.g. SELECT displayname, rank, country, email, SUM(correct) AS correct etc.
and it'll return them from the users table.

hth

edit: this was all off the top of my head and not checked for accuracy, so you may need to adjust them slightly - if any dont work when you run them, post the error message here

Re: Display query help

Posted: Sat Nov 21, 2009 8:30 am
by intervelopment
Hey, Thanks for your help.

I tried the first query out like so

Code: Select all

                        <?php
                            function leaderboard(){
                                global $database, $session;
                                
                                //$month = date('m');
                                
                                $q = "SELECT email, SUM(correct) AS correct, SUM(time) AS time, stamp, month FROM ".TBL_SCORES." s INNER JOIN ".TBL_USERS." u ON (u.email = s.email) GROUP BY email, month, stamp";
                                $result = $database->query($q);
                                $num_rows = mysql_numrows($result);
                                
                                for($i=0; $i<$num_rows; $i++){
                                    $email = mysql_result($result,$i,"email");
                                    $correct = mysql_result($result,$i,"correct");
                                    $time = mysql_result($result,$i,"time");
                                    $stamp = mysql_result($result,$i,"stamp");
                                    $month = mysql_result($result,$i,"month");
                                    
                                    echo $email ."<br />". $correct ."<br />". $time ."<br />". $stamp ."<br />". $month;
                                }
                            }
                        ?>
and i go the following error

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in

Re: Display query help

Posted: Sat Nov 21, 2009 8:42 am
by iankent
Assuming $database->query() just returns the value of a mysql_query call, then the query is slightly wrong. Try adding this line just under the mysql_query() line (between line 8 and 9):

Code: Select all

echo mysql_error();
That'll output an SQL error that should tell us a bit more about why the query isn't working!

Re: Display query help

Posted: Sat Nov 21, 2009 6:05 pm
by intervelopment
Column 'email' in field list is ambiguous

Re: Display query help

Posted: Sat Nov 21, 2009 7:16 pm
by iankent
Ahh ok, on each of the queries change SELECT email blah to SELECT u.email blah

Because both tables (scores and users) have a column called email it doesn't know which to use, so saying u.email chooses the value from the users table (they'll both be the same, so it doesn't really matter which table you get it from!)

Re: Display query help

Posted: Sat Nov 21, 2009 7:20 pm
by intervelopment
Hmmm im not quite sure i know what you mean.

What would i have to change in the query?

Re: Display query help

Posted: Sat Nov 21, 2009 7:23 pm
by iankent
Yep. In you're SELECT queries you have a list of fields you're getting back, i.e.
SELECT email, SUM(correct) AS correct, SUM(time) AS time, stamp, month, correct+time AS combined FROM scores .......

On the 'email' following the SELECT you need to change it to u.email, so it looks like this:
SELECT u.email, SUM(correct) AS correct ....

do that for all the queries where you join users and scores that use SELECT email

hth