I have a problem with the following function:
function get_organization_score($org_id) { $sql1 = "SET @rownum := 0"; $sql2 = "SELECT rank, xp_total FROM ( SELECT @rownum := @rownum + 1 AS rank, g_org.id AS org_id, (Sum(g_npc.xp) + (Sum(g_npc.level)*128)) AS xp_total FROM g_org LEFT JOIN g_npc ON g_org.id = g_npc.g_org_id GROUP BY g_org.id ORDER BY xp_total DESC ) as result WHERE org_id='".$org_id."'"; mysql_query($sql1); $result = mysql_query($sql2); $rows = ''; $data = array(); if (!empty($result)) $rows = mysql_num_rows($result); else $rows = ''; if (!empty($rows)){ while ($rows = mysql_fetch_assoc($result)){ $data[] = $rows; } } if (empty($data[0]['rank'])) return 1; return $data[0]['rank']; }
This code should show the rating of the organization on the scoreboard. So, for example, when I give an organization identifier, I get its rank number located on the scoreboard.
Currently, the problem is that I am not getting the rank number, but the identifier itself, which I call the function as the organization identifier.
Example: get_organization_score (1) then I get the rank number as 1 or when I call get_organization_score (34) then I get the rank number 34
but I want his position in the scoreboard, ordered by the XP sum, which comes from another table on the left join.
Can anyone help? If you have questions, please ask ...
EDIT: Adding a table schema and sample data:
CREATE TABLE IF NOT EXISTS `g_org` ( `id` int(255) NOT NULL AUTO_INCREMENT, `org_name` varchar(255) NOT NULL, `founded` datetime NOT NULL, `g_userid` int(255) NOT NULL, `g_username` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `g_userid` (`g_userid`), KEY `g_username` (`g_username`(191)), KEY `g_username_2` (`g_username`), KEY `org_name` (`org_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=47 ; INSERT INTO `g_org` (`id`, `org_name`, `founded`, `g_userid`, `g_username`) VALUES (1, 'test_org_1', '2012-07-04 02:30:56', 1, 'DDD'), (2, 'test_org_2', '2012-07-04 02:34:57', 2, '777'); -- -- Table structure for table `g_npc` -- CREATE TABLE IF NOT EXISTS `g_npc` ( `id` int(255) NOT NULL AUTO_INCREMENT, `g_userid` int(255) NOT NULL, `g_username` varchar(255) NOT NULL, `g_org_id` int(255) NOT NULL, `g_org_name` varchar(255) NOT NULL, `g_npc_name` varchar(255) NOT NULL, `level` int(255) NOT NULL, `xp` int(255) NOT NULL, `last_update` datetime NOT NULL, PRIMARY KEY (`id`), KEY `g_userid` (`g_userid`), KEY `g_username` (`g_username`), KEY `g_org_id` (`g_org_id`), KEY `g_org_name` (`g_org_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=181 ; INSERT INTO `g_npc` (`id`, `g_userid`, `g_username`, `g_org_id`, `g_org_name`, `g_npc_name`, `level`, `xp`, `last_update`) VALUES (1, 1, 'DDD', 1, 'test_org_1', 'Kinuzehute Doqasi', 4, 155, '2012-11-13 14:30:54'), (2, 1, 'DDD', 1, 'test_org_1', 'Zabava Qigatagise', 2, 107, '2012-10-30 17:38:12'), (3, 2, '777', 2, 'test_org_2', 'Roci Vuzoducu', 6, 135, '2012-11-13 23:17:40'), (4, 2, '777', 2, 'test_org_2', 'Gexoye Zeze', 4, 638, '2012-11-06 02:02:27'), (5, 2, '777', 2, 'test_org_2', 'Sibalabu Gozi', 9, 285, '2012-11-06 02:02);
When I call the get_organization_score (2) function; then the organization identifier will be 2, and I would like to query the table "g_org" with the identifier 2 and LEFT JOIN in the table "g_npc" to get all the entries with the value "g_org_id" 2 in the table "g_npc" and SUM up all the results from the field "g_npc" "xp" and then ORDER BY all these results for the field "xp" DESC.
Then the remaining PHP code with @rownum will give this organization a ranking among other organizations.
Thus, the following function with organization identifier 2 will cause the ranking to be 1, since it has more XP in the "xp" field, and when I call the same function with organization identifier 1, then the ranking number will be 2, because it has a smaller number in the "XP" field.
Basically, I do not want to give a list of organizations, but only the rating number. Nothing more.
The ending must have WHERE org_id = '". $ Org_id." ', because he needs to get a rating number for only 1 record.