Welcome to WebmasterWorld Guest from 54.145.208.64

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)

5+ Year Member



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)

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



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)

5+ Year Member



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)

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



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)

5+ Year Member



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)

5+ Year Member



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)

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



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)

5+ Year Member



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";
}