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());
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
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?
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.
$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’
