Display query help
Moderator: General Moderators
-
intervelopment
- Forum Newbie
- Posts: 4
- Joined: Sat Nov 21, 2009 7:45 am
Display query help
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
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
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: Display query help
The first one you need to use something like this:
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.:
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:
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
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;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;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;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
Hey, Thanks for your help.
I tried the first query out like so
and i go the following error
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in
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;
}
}
?>Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: Display query help
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):
That'll output an SQL error that should tell us a bit more about why the query isn't working!
Code: Select all
echo mysql_error();-
intervelopment
- Forum Newbie
- Posts: 4
- Joined: Sat Nov 21, 2009 7:45 am
Re: Display query help
Column 'email' in field list is ambiguous
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: Display query help
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!)
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
Hmmm im not quite sure i know what you mean.
What would i have to change in the query?
What would i have to change in the query?
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: Display query help
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
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