User row position

[EchoFool]

Hey,

 

I need help here, im trying to calculate how well a user is doing against other users by adding multipliers to fields to create a total sum.... then order every user with similiar fashion to see what position the user is against other players.

 

My attempt:

 

<?php
// multiplier values //
$Houses = 2;
$Apartments = 0.5;
$Schools = 3;
// end multipliers //

// work out player position against other users//
$Get = mysql_query("SELECT COUNT( (t2.Houses*$Houses) + (t2.Apartments*$Apartments) + (t2.Schools*$Schools) ) + 1 					
                                        AS Position FROM listings t1
				LEFT OUTER JOIN listings t2
				ON t1.Position < t2.Position
				WHERE t1.UserID = '$UserID'")
Or die(mysql_error());

$row = mysql_fetch_assoc($Get); 

// ordinal position //
Echo ordinal ($row['Position']); 
//ordinal function adds a string to the var so 1 becomes 1st - 2 becomes 2nd etc.
?>

 

 

Hope you can help here because at the moment the query is not working at all.

[kickstart]

Hi

 

suspect you mean to use SUM() rather than COUNT().

 

Also seems odd that you are joining the 2 tables using t1.Position < t2.Position

 

All the best

 

Keith

[EchoFool]

Well i dunno how else do it i was mainly guessing Should i know join the tables?

[kickstart]

Hi

 

Use this as a starting point. It will give you a listing of all the users order by score

 

SELECT UserID, SUM((Houses*$Houses) + (Apartments*$Apartments) + (Schools*$Schools) ) AS Score
FROM listings 
GROUP BY Score
ORDER BY Score DESC

 

Quick play and I think this would do it (although I have a feeling I am missing something obvious so hopefully someone will see an easier way of doing it).

 

SELECT COUNT(T1.UserId)
FROM (SELECT UserID, SUM((Houses*$Houses) + (Apartments*$Apartments) + (Schools*$Schools) ) AS Score
FROM listings
GROUP BY Score) T1
CROSS JOIN
(SELECT UserID, SUM((Houses*$Houses) + (Apartments*$Apartments) + (Schools*$Schools) ) AS Score
FROM listings
WHERE t1.UserID = '$UserID'
GROUP BY Score) T2
WHERE T1.Score > T2.Score

 

All the best

 

Keith