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 ? :)

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

---

WOW, it seems to work as expected! :D

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

Quote:

---

Originally Posted by Walgermo

r937 ? :)

---

sorry, i wasn't quick enough

Quote:

---

Originally Posted by r937

sorry, i wasn't quick enough

---

hehe, maybe next time ;)

Quote:

---

Originally Posted by Walgermo

r937 ? :)

---

I ignored that part of the post :D

Quote:

---

Originally Posted by guido2004

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?

[deleted posted query]

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

Quote:

---

Originally Posted by 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 :)

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.

Quote:

---

Originally Posted by Walgermo

i also want to sort by gender

---

Sort? Or select?

Quote:

---

Tried to integrate it with no luck, hehe.

---

Post your integrated code :)

Quote:

---

Originally Posted by guido2004

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.

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

Quote:

---

Originally Posted by guido2004

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'

Put single quotes around it:

Code:

---

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

---

Quote:

---

Originally Posted by guido2004

Put single quotes around it:

Code:

---

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

---

---

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