GROUP BY problem

[Walgermo]

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.

$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.

$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 ?

[guido2004]

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.

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

[Walgermo]

WOW, it seems to work as expected!

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

[r937]

r937 ?

sorry, i wasn't quick enough

[Walgermo]

sorry, i wasn't quick enough

hehe, maybe next time

[guido2004]

r937 ?

I ignored that part of the post

[Walgermo]

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?

[guido2004]

[deleted posted query]

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

[Walgermo]

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

[Walgermo]

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.

[guido2004]

i also want to sort by gender

Sort? Or select?

Tried to integrate it with no luck, hehe.

Post your integrated code

[Walgermo]

Sort? Or select?

Post your integrated code

Not sort i meant select

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.

[guido2004]

Eliminate it from the subquery, leave it only in the main query. The subquery doesn't use the users table.

[Walgermo]

Eliminate it from the subquery, leave it only in the main query. The subquery doesn't use the users table.

Yes..

	$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'

[guido2004]

Put single quotes around it:

AND users.user_gender= '". $gender ."'

[Walgermo]

Put single quotes around it:

AND users.user_gender= '". $gender ."'

Omg, i see it now! thanks for the reply..