Display query help

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
intervelopment
Forum Newbie
Posts: 4
Joined: Sat Nov 21, 2009 7:45 am

Display query help

Post 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
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Display query help

Post 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
intervelopment
Forum Newbie
Posts: 4
Joined: Sat Nov 21, 2009 7:45 am

Re: Display query help

Post 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
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Display query help

Post 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!
intervelopment
Forum Newbie
Posts: 4
Joined: Sat Nov 21, 2009 7:45 am

Re: Display query help

Post by intervelopment »

Column 'email' in field list is ambiguous
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Display query help

Post 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!)
intervelopment
Forum Newbie
Posts: 4
Joined: Sat Nov 21, 2009 7:45 am

Re: Display query help

Post by intervelopment »

Hmmm im not quite sure i know what you mean.

What would i have to change in the query?
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Display query help

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