SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY problem

    Hi

    I have a similar problem posted in this thread:
    http://www.sitepoint.com/forums/show....php?p=4327892

    I'm building an trainingblog site and I want to show the strongest members in the selected exercise and repetition.

    I only want to show the max kg for each user.

    Its now showing wrong max for user.

    Code MySQL:
    $result = mysql_query("SELECT
    	users.user_id,
    	users.user_country,
    	lifts.kg,
    	lifts.url,
    	training.place,
    	training.date,
    	training.training_id,
    	training.bodyweight
    	FROM lifts, training, users
    	WHERE lifts.rep = ". $nr_reps ." 
    	AND lifts.exercise_id= ". $ex_id ." 
    	AND training.training_id = lifts.training_id 
    	AND training.user_id = users.user_id 
               GROPU BY user_id
    	ORDER BY kg DESC, ABS(bodyweight) ASC, training.date ASC") or die(mysql_error());

    And here is the php code for viewing.
    Code PHP:
    $num = mysql_num_rows($result);
    $i=1;
    if($i <= $num)
    { 
    		echo "<table border='1' width='100%'>";
    		echo "<tr><th>#</th><th>" . ucf(translate('name')) . "</th><th>" . ucf(translate('country')) . "</th><th>" . ucf(translate('place')) . "</th><th>" . translate('date') . "</th><th>" . ucf(translate('bodyweight')) . "</th><th>". translate('lifted') ."</th></tr>";
     
     
    		while($row = mysql_fetch_array( $result )) 
    		{
    			echo "<tr";  
    			if($i == 1): echo " style='background-color:#F9F99F;'"; endif; if($i == 2): echo " style='background-color:#F6F9F9 ;'"; endif; if($i == 3): echo " style='background-color:#EBC79E;'"; endif; echo ">";
    			echo "<td align='center'>";
    			if($i == 1): echo "<img src='img/gold.png' />"; endif;
    			if($i == 2): echo "<img src='img/silver.png' />"; endif; 
    			if($i == 3): echo "<img src='img/bronze.png' />"; endif; 
    			echo "<b>" . $i++ . "</b>";
    			echo "</td>
     
    			<td align='center'>";
    			echo "<b><a style='color:#555555;' href='index.php?action=profile&id=". $row['user_id'] ."'>" . getNameFromId($row['user_id']) . "</a></b>"; 
    			echo "</td>
     
    			<td align='center'>";
    			echo "<div><img src='img/flags/16/". strtolower($row['user_country']) .".png' /></div> " . translate(ucf(getCountryName(strtolower($row['user_country'])))) . "";
    			echo "</td>
     
    			<td align='center'>";
    			echo $row['place'];
    			echo "</td>
     
    			<td align='center'>";
    			echo formatDate($row['date'], $dateformat);
    			echo "</td>
     
    			<td align='center'>";
    			echo r_nr2(calcFromKg($row['bodyweight'])). $w_metric;
    			echo "</td>
     
    			<td align='center'>"; 
    			echo "<a style='color:#555555;' href='index.php?action=profile&sub=show&tid=". $row['training_id'] ."'>";
    			if($w_metric == "kg")
    			{
    			echo "<b>" .  r_nr2(calcFromKg($row['kg'])). $w_metric . "</a></b>";
    				if($row['url'] != "")
    				{
    					echo " <a href='" . $row['url'] . "' target='_blank'><img title=". translate('show_video') ." src='img/video.png' /></a>";
    				}
    			}
    			else
    			{
    			echo "<b>" .  r_nr(calcFromKg($row['kg'])). $w_metric . "</a></b>";
    				if($row['url'] != "")
    				{
    					echo " <a href='" . $row['url'] . "' target='_blank'><img title=". translate('show_video') ." src='img/video.png' /></a>";
    				}
    			}
    			echo "</td>
    			</tr>"; 
     
    		}
     
    		echo "</table>

    r937 ?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try this. Didn't test it, so it might need some tweaking. And if the user lifted the same max weight in several training sessions, it'll give multiple rows for that user.
    Code:
    SELECT
        users.user_id
      , user_country
      , lifts.kg
      , lifts.url
      , training.place
      , training.date
      , training.training_id
      , training.bodyweight
    FROM lifts
    INNER JOIN training
    ON training.training_id = lifts.training_id 
    INNER JOIN users
    ON training.user_id = users.user_id 
    INNER JOIN
      (SELECT
           training.user_id
         , MAX(lifts.kg) AS maxlift
       FROM lifts
       INNER JOIN training
       ON training.training_id = lifts.training_id 
       WHERE lifts.rep = ". $nr_reps ." 
       AND lifts.exercise_id= ". $ex_id ." 
       GROUP BY user_id
      ) AS ml
    ON training.user_id = ml.user_id
    AND lifts.kg = ml.maxlift 
    WHERE lifts.rep = ". $nr_reps ." 
    AND lifts.exercise_id= ". $ex_id ." 
    ORDER BY 
        kg DESC
      , ABS(bodyweight) ASC
      , training.date ASC

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW, it seems to work as expected!

    I would never come close to figuring out that query, you made my day

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Walgermo View Post
    r937 ?
    sorry, i wasn't quick enough
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, i wasn't quick enough
    hehe, maybe next time

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Walgermo View Post
    r937 ?
    I ignored that part of the post

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    And if the user lifted the same max weight in several training sessions, it'll give multiple rows for that user.
    Missed that, i only want to show the first time the user did the lift.

    So if i benched 100kg 15.02.2011 and did the same 12.01.2011 <- this will show.
    Where do i make the changes then?

    Add A GROUP BY

    AND lifts.exercise_id= ". $ex_id ."
    GROUP BY user_id
    ) AS ml
    ON training.user_id = ml.user_id
    AND lifts.kg = ml.maxlift
    WHERE lifts.rep = ". $nr_reps ."
    AND lifts.exercise_id= ". $ex_id ."
    GROUP BY user_id <------------------ Here?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    [deleted posted query]

    nope, too quick... that wasn't the solution

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Walgermo View Post
    Missed that, i only want to show the first time the user did the lift.

    So if i benched 100kg 15.02.2011 and did the same 12.01.2011 <- this will show.
    Where do i make the changes then?

    Add A GROUP BY

    AND lifts.exercise_id= ". $ex_id ."
    GROUP BY user_id
    ) AS ml
    ON training.user_id = ml.user_id
    AND lifts.kg = ml.maxlift
    WHERE lifts.rep = ". $nr_reps ."
    AND lifts.exercise_id= ". $ex_id ."
    GROUP BY user_id <------------------ Here?
    It seen to do the work

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i also want to sort by gender

    the column name is
    users.user_gender

    and variable:
    ". $gender ."

    So selection will be exercise, gender, repetions

    Tried to integrate it with no luck, hehe.

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Walgermo View Post
    i also want to sort by gender
    Sort? Or select?
    Tried to integrate it with no luck, hehe.
    Post your integrated code

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Sort? Or select?

    Post your integrated code
    Not sort i meant select

    Code PHP:
    if($gender == "")
    {
    	// Get all the data
    	$result = mysql_query("SELECT
        users.user_id
      , users.user_country
      , lifts.kg
      , lifts.url
      , training.place
      , training.date
      , training.training_id
      , training.bodyweight
    FROM lifts
    INNER JOIN training
    ON training.training_id = lifts.training_id 
    INNER JOIN users
    ON training.user_id = users.user_id 
    INNER JOIN
      (SELECT
           training.user_id
         , MAX(lifts.kg) AS maxlift
       FROM lifts
       INNER JOIN training
       ON training.training_id = lifts.training_id 
       WHERE lifts.rep = ". $nr_reps ." 
       AND lifts.exercise_id= ". $ex_id ." 
       GROUP BY user_id
      ) AS ml
    ON training.user_id = ml.user_id
    AND lifts.kg = ml.maxlift 
    WHERE lifts.rep = ". $nr_reps ." 
    AND lifts.exercise_id= ". $ex_id ." 
    GROUP BY user_id
    ORDER BY 
        ABS(lifts.kg) DESC
      , ABS(training.bodyweight) ASC
      , ABS(training.date) ASC
    LIMIT ". $nr_to_show ."") or die(mysql_error());
    }
    else
    {
    	// Get all the data with sex seleceted
    	$result = mysql_query("SELECT
        users.user_id
      ,	users.user_gender
      , users.user_country
      , lifts.kg
      , lifts.url
      , training.place
      , training.date
      , training.training_id
      , training.bodyweight
    FROM lifts
    INNER JOIN training
    ON training.training_id = lifts.training_id 
    INNER JOIN users
    ON training.user_id = users.user_id 
    INNER JOIN
      (SELECT
           training.user_id
         , MAX(lifts.kg) AS maxlift
       FROM lifts
       INNER JOIN training
       ON training.training_id = lifts.training_id 
       WHERE lifts.rep = ". $nr_reps ." 
       AND lifts.exercise_id= ". $ex_id ."
    AND users.user_gender= ". $gender ."
       GROUP BY user_id
      ) AS ml
    ON training.user_id = ml.user_id
    AND lifts.kg = ml.maxlift 
    WHERE lifts.rep = ". $nr_reps ." 
    AND lifts.exercise_id= ". $ex_id ."
    AND users.user_gender= ". $gender ."
    GROUP BY user_id
    ORDER BY 
        ABS(lifts.kg) DESC
      , ABS(training.bodyweight) ASC
      , ABS(training.date) ASC
    LIMIT ". $nr_to_show ."") or die(mysql_error());
    }
    Gives me Unknown column 'users.user_gender' in 'where clause' but i cant see exactly where i should insert it.

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Eliminate it from the subquery, leave it only in the main query. The subquery doesn't use the users table.

  14. #14
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Eliminate it from the subquery, leave it only in the main query. The subquery doesn't use the users table.
    Yes..

    Code PHP:
    	$result = mysql_query("SELECT
        users.user_id
      ,	users.user_gender
      , users.user_country
      , lifts.kg
      , lifts.url
      , training.place
      , training.date
      , training.training_id
      , training.bodyweight
    FROM lifts
    INNER JOIN training
    ON training.training_id = lifts.training_id 
    INNER JOIN users
    ON training.user_id = users.user_id 
    INNER JOIN
      (SELECT
           training.user_id
         , MAX(lifts.kg) AS maxlift
       FROM lifts
       INNER JOIN training
       ON training.training_id = lifts.training_id 
       WHERE lifts.rep = ". $nr_reps ." 
       AND lifts.exercise_id= ". $ex_id ."
       GROUP BY user_id
      ) AS ml
    ON training.user_id = ml.user_id
    AND lifts.kg = ml.maxlift 
    WHERE lifts.rep = ". $nr_reps ." 
    AND lifts.exercise_id= ". $ex_id ."
    AND users.user_gender= ". $gender ."
    GROUP BY user_id
    ORDER BY 
        ABS(lifts.kg) DESC
      , ABS(training.bodyweight) ASC
      , ABS(training.date) ASC
    LIMIT ". $nr_to_show ."") or die(mysql_error());
    Then i get: Unknown column 'male' in 'where clause'

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Put single quotes around it:
    Code:
    AND users.user_gender= '". $gender ."'

  16. #16
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Put single quotes around it:
    Code:
    AND users.user_gender= '". $gender ."'
    Omg, i see it now! thanks for the reply..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •