Skip to main content
Known Participant
May 22, 2010
Question

PHP & MySQL, joining tables

  • May 22, 2010
  • 3 replies
  • 1914 views

I have a table in my database with the following fields:

user_name

score

points

Some users have 30 scores and others have 5.  What I want to do is create a recordset that will calculate the highest 15 points for each user.  Then count the total scores (either 15 or less) and get an average points.  The only way I have been able to figure anything out is by defining an exact user.  I can't get it to calculate it for each users and create a list.

Any ideas?

This topic has been closed for replies.

3 replies

June 1, 2010

What I understand is, the SQL result with single SQL statement will be something like this:

each user's 15 top records with points and scores of all 15 records individually, AND sum of each user's points AND count of each user's records (less than 15), which can be a big chunk of data with all the user's names and data mixed in same result.

The code I posted, as I said, is an abstract. Every SQL call in the loop limits the query to 15, and if you use some prepared statements, like PDO Mysql, you can retrieve the data a lot faster by only changing the variables (user name) in the same database call.

Probably a matter of taste, but single SQL statement can easily return very big amount of data if the user list grows to few thousands or more.

Participating Frequently
June 1, 2010

>Probably a matter of taste, but single SQL statement

>can easily return  very big amount of data if the user list

>grows to few thousands or more.

True. The single SQL statement will result in more data (not garbage data) being returned as each row will contain the user's individual points/score as well as the aggregates. But assuming each 'query' does result in thousands of rows, I think I would worry more about the thousands of database requests over the increase in data. If the numbers are small, either method would work fine.

Known Participant
June 7, 2010

I tried both ways and can't get either to work properly.

What do you mean at the end where you say #use the user data here?

I am not to familiar with loops so I am obviously missing something...

June 1, 2010

What I can tell from what data you need, even if you can achieve this  in a single SQL shot, you will get awful amount of mixed garbage, which  you have to pick and choose the info you need with big and most likely nested loops and  control statements.

Sometimes, the simplest and and the  most primitive solutions yields the best results:

Here is the  code abstract (not actual programming code):

#Get all the users first:

mysql_result = SELECT user_name FROM events GROUP BY user_name

#then, loop inside that result:

while (rows = mysql_result->fetch){

each_user_result = SELECT SUM(user_points) AS sum_points, COUNT(user_score) as total_records,

                   user_points, user_score, user_name FROM events WHERE user_name = rows["user_name"] ORDER BY user_points DESC LIMIT 15

while(user_info = each_user_result->fetch){

#use the user data here....

}

}

Participating Frequently
June 1, 2010

>What I can tell from what data you need,

>even if you can achieve this   in a single SQL shot,

>you will get awful amount of mixed garbage,

You can do it with a single SQL statement. Why do you think there will be garbage? The output requirements they posted are quite simple. You would not need any nested loops. On the other hand, you are suggesting a nested loop which (if I am reading your code correctly) puts a call back to the database for each outer row.

>Sometimes, the simplest and and the  most

>primitive solutions yields the  best results:

Sometimes, but not always. Correlated subqueries often have poor performance, but so does a procedure that has to send multiple queries back to the database. You'd have to test with trial and error to see.

Participating Frequently
May 24, 2010

It hard to understand without knowing the definition of your fields. What's the difference between score and points? Please provide some sample data. Sounds like you are looking for a 'top n per group' solution.

Search for that term or see if this helps:

http://sqlblog.com/blogs/adam_machanic/archive/2008/02/08/who-s-on-first-solving-the-top-per-group-problem-part-1-technique.aspx

http://www.sql-ex.ru/help/select16.php

Known Participant
May 24, 2010

Example:

Field: username

Field: score

Field: points

Data example:

Username: bill

Score: 80

Points: 130

Username: john

Score: 84

Points: 125

Username: bill

Score: 99

Points: 150

Username: jim

Score: 77

Points: 65

Username: john

Score: 104

Points: 55

Obviously, I have hundreds of lines of data, but this is an example. The

score is the score and the points are how many points they get associated

with the score. Varies all the time.

I want to find the highest 15 points for each username. Then I want to add

the points and divide by how many records they actually have. Not all users

will have 15 records. I want this for all different usernames.