Welcome to WebmasterWorld Guest from 54.226.238.178

Forum Moderators: open

Message Too Old, No Replies

issue with order by in mysql

Order by is adding all my values together

     
3:39 am on Jan 7, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 7, 2010
posts:8
votes: 0


Hello all. I have a problem with group by and order by in mysql. For whatever reason that I can't figure out, it is adding my total_points values together for all my user_id's.

For Example:
User1: 330 (total_points)
User1: 310 (total_points)
User1: 280 (total_points)
User2: 330 (total_points)
User2: 310 (total_points)
User2: 280 (total_points)
And so on and so on for all my users. It adds them all together and displays each total_points value for each user_id. I hope I haven't confused you guys to much.

Here is how my table I am querying from is setup.

== Table structure for table race


¦------
¦Field ¦Type ¦Null ¦Default
¦------
¦//**id_race**//¦bigint(15) ¦No ¦
¦race_id ¦bigint(10) ¦No ¦0
¦user_id ¦bigint(15) ¦No ¦0
¦f ¦mediumint(5) ¦No ¦0
¦s ¦mediumint(5) ¦No ¦0
¦race_interval ¦varchar(255) ¦No ¦
¦race_laps ¦smallint(3) ¦No ¦0
¦led ¦varchar(50) ¦No ¦
¦points ¦mediumint(5) ¦No ¦0
¦total_points ¦mediumint(5) ¦No ¦0
¦race_status ¦varchar(50) ¦No ¦

This is my query I am using to get the results I need.


$r_query2=mysql_query("SELECT user_id, points, SUM(points) AS total_points FROM race GROUP BY user_id ORDER BY total_points DESC");
while($get_r_query2=mysql_fetch_array($r_query2))
{
echo "{$get_r_query3['driver']}"; //this query result works fine & is a seperate query all together
"{$get_r_query2['total_points']}";
}
5:02 pm on Jan 7, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 48


Not sure why you are getting both points and SUM(points) in your query. The following makes more sense:
SELECT user_id, SUM(points) AS total_points
FROM race
GROUP BY user_id
ORDER BY total_points DESC
6:07 pm on Jan 7, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 7, 2010
posts:8
votes: 0


Ok I don't know why I was doing that either..lol I guess I was trying to make something work and forgot to remove that extra 'points' out. But I made the change to what you said, I still have the same issue. Below is the link to a test page to show what it is doing on my end. Maybe if you guys can see the exact problem I'm describing then you might have some ideas. Thanks for the help so far.

[66.191.161.110...]

6:48 pm on Jan 7, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 48


Try:
SELECT user_id, SUM(points) AS total_points
FROM race
GROUP BY user_id
ORDER BY SUM(points) DESC
7:07 pm on Jan 7, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 7, 2010
posts:8
votes: 0


It gave the same result in testpoints2.php at the link above. I have a feeling it has something to do with the numerical data that is trying to be ordered. I just can't seem to find what is causing it to duplicate it over and over.
7:20 pm on Jan 7, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 7, 2010
posts:8
votes: 0


Actually it may be, because I am using 2 separate queries to return driver and points. Below is the query exactly as I have it right now, and that produces the driver table like it should but it adds up all the total_points and assigns it to each driver.

Test example below:
[66.191.161.110...]


$r_query3=mysql_query("SELECT user_id, driver FROM user GROUP BY driver");
while($get_r_query3=mysql_fetch_array($r_query3, MYSQL_ASSOC))
{
$r_query2=mysql_query("SELECT user_id, SUM(points) AS total_points FROM race ORDER BY SUM(points) DESC");
while($get_r_query2=mysql_fetch_array($r_query2, MYSQL_ASSOC))
{
echo "<tr>\n<td>" . $get_r_query3['driver'] . "</td>
<td>" . $get_r_query2['total_points'] . "</td>\n
</tr>\n";
}
}
7:44 pm on Jan 7, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 48


Actually it may be, because I am using 2 separate queries to return driver and points.

Um, yeah! :)

You should combine the two together.

8:28 pm on Jan 7, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 7, 2010
posts:8
votes: 0


Alright that fixed my problem. :-) Thank you for getting my brain working like it should be hehe.. Below is the code that I used to get it working for future reference for anyone that may need it.


$query = "SELECT user.user_id, user.driver, race.user_id, SUM(race.points) AS total_points "." FROM user, race "." WHERE user.user_id=race.user_id GROUP BY driver ORDER BY total_points DESC";
$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo "<tr>\n<td>" . $row['driver'] . "</td>
<td>" . $row['total_points'] . "</td>
\n</tr>\n";
}

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members