PHP/MYSQL Ranking Users

[rycore]

Hey guys and girls,

 

Okay so am trying to provide a rank for each user within my database but I cant seem to get it working here is my code

 

SELECT 

`Table`.`Id`,
`Table`.`Photo`,
`Table`.`Fname`,
`Table`.`Lname`,
`Table`.`Gender`,
`Table`.`Gd`,
`Table`.`Bd`,
`Table`.`Point`,

(SELECT COUNT(*) + 1 FROM
	(SELECT U.Id, T.`User Id` FROM `u_account` U
		LEFT JOIN `score_system` T ON `T`.`User Id` = `U`.`Id`) x

		WHERE Table.Point > (SELECT SUM(Good)-SUM(Bad) FROM `score_system` GROUP BY `User Id`)
) AS RANK 

	FROM
		(SELECT 
			`C`.`Id`,
			`C`.`Photo`,
			`C`.`Fname`,
			`C`.`Lname`,
			`C`.`Gender`,

			CASE WHEN COALESCE( SUM(`S`.`Good`), '') = '' THEN '0' ELSE SUM(`S`.`Good`) END AS Gd,
			CASE WHEN COALESCE( SUM(`S`.`Bad`), '') = '' THEN '0' ELSE SUM(`S`.`Bad`) END AS Bd,

			CASE WHEN COALESCE( SUM(`S`.`Good` - `S`.`Bad`), '') = '' 
			THEN '0' ELSE SUM(`S`.`Good` - `S`.`Bad`) END AS Point

				FROM
					`u_account` AS `C`
					LEFT JOIN `score_system` AS `S` ON `C`.`Id` = `S`.`Child Id`

					AND (SELECT Start FROM event_schedule WHERE CURDATE() >= Start AND End >= CURDATE()) < `S`.`Date`
					AND (SELECT End FROM event_schedule WHERE  CURDATE() >= Start AND End >= CURDATE()) > `S`.`Date`

				GROUP BY `C`.`Id`

		) AS `Table`

ORDER BY Point DESC, Fname ASC

 

I cant seem to pull the users rank from this and its donig my head in I've tried different things and they all dont work this is perhaps as close as I've got to figuring it out.

 

This is the part that creates the users rank now its saying the Subquery returns more than 1 row but I just cant seem to think of away to implement this into my query above so that it displays the rank for each user.

 

(SELECT COUNT(*) + 1 FROM
	(SELECT U.Id, T.`User Id` FROM `u_account` U
		LEFT JOIN `score_system` T ON `T`.`User Id` = `U`.`Id`) x

		WHERE Table.Point >= (SELECT SUM(Good - Bad) FROM `score_system` GROUP BY `User Id`)
) AS RANK 

 

I'm also looking for this to display the same rank for both users with the same points so If bill and ben have 200points then they would both have the same rank but if bill was to have 201points then he would be higher up than ben.

 

So can anyone lend me a hand.

 

Thanks in advance

[rycore]

Okay so i've kind of found a solution the problem with the solution is that it requires me to group the SUM() which I know cannot be done.

 

here is the code

 

SELECT COUNT(*) AS NewRank
			FROM (SELECT SUM(Good-Bad) AS correct, COUNT(*)
			FROM star_system

			GROUP BY `correct`
			HAVING  correct >= (SELECT SUM(Good-Bad) 
			FROM star_system WHERE `Child Id`=".$Child['Id'].")

			) as Rank4Id;

 

[uPDATE] Ignor the below text I found that even if I change the group to the users id still does not help me. I need away to group the value of the sum so that it gets a proper ranking.. just not sure on how to do it.

 

So this will grab the users rank for me providing I was to replace the ">=" with just ">" and replace "GROUP BY correct" with "GROUP BY `userId`" then it would work fine. But the thing is if I can find away to group my value from the SUM() and use the ">=" I will be able to display user ranks a lot better so if a user has the same score as another user they will both display the same rank.

 

 

 

Hopefully someone knows a work aroud for this I've been googling for hours trying to figure this out.

 

Thanks in advance.

 

 

[jcbones]

Without table structure, and desired output this will be difficult.  This is more MySQL than PHP though.  You may have to try that forum.

[rycore]

Thank you for your reply I was beginning to think that myself but thought there could of been away Ill try googling about using pure mysql and see what I can find thanks