Multiple Counts in MYSQL PHP Query

Question

I'm trying to create a leaderboard but i'm not sure how to do the mysql query.

I would like to count all the levels from a player in the skills table and get the total Level and count all the experience from a player in the experience table and get the Total Exp along with displaying the persons name from the users column.

There is 3 tables factions_mcmmo_users, factions_mcmmo_experience, factions_mcmmo_skills.

This is what i have so far but it doesn't work:

    $sql = ("SELECT a.id,
(SELECT COUNT(*) FROM factions_mcmmo_experience WHERE user_id = a.id) as TotalXP,
(SELECT COUNT(*) FROM factions_mcmmo_skills WHERE user_id = a.id) as TotalLevel
FROM (SELECT DISTINCT id FROM factions_mcmmo_users) a LIMIT 10;");

Any help would be very appreciated

EDIT: I have it working now but i'm unsure if its the most efficient way to do things so if anyone could help me out if theres a better way, it would mean a lot.

I would also like to know if it's possible to display the total exp and level with commas if the number is in the thousands for example: total level 5,882 and total xp 582,882

EDIT 2: I have figured out how to format the numbers but still don't know if my code is efficient

$sql = ("SELECT id, user,
(SELECT FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy),0) FROM factions_mcmmo_skills b WHERE b.user_id = a.id) as TotalLevel,
(SELECT FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy),0) FROM factions_mcmmo_experience c WHERE c.user_id = a.id) as TotalXP
FROM (SELECT id, user FROM factions_mcmmo_users) a group by id ORDER BY TotalLevel DESC, TotalXP DESC LIMIT 10;");

EDIT 3 Updated code from scaisEdge but was displaying everyones level as 1 and XP as 1, so i changed count(*) changed to sum, added an order By TotalLevel in Descending order and that seems to have worked but i can't get it to display the persons name (user column) in the user table? not sure if i was supposed to change to sum because it didn't work the other way.

$sql = ("SELECT a.id, b.TotalXP, c.TotalLevel
   FROM (SELECT DISTINCT id FROM factions_mcmmo_users) a 
   INNER JOIN (
        SELECT user_id, Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy) as TotalXP 
        FROM factions_mcmmo_experience
        GROUP By user_id
   ) b on b.user_id = a.id
   INNER JOIN (
       SELECT user_id, Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy)  as TotalLevel
       FROM factions_mcmmo_skills
       GROUP by user_id

   ) c on  c.user_id = a.id
   ORDER BY TotalLevel DESC
   LIMIT 10;");

EDIT 4 Everything working but when i try to format the totals using "FORMAT(Sum(Columns), 0) on the inner joins, the EXP Total appears to work but the main Total Level is not displaying results that are over 1,000 and it breaks the leaderboard positioning, it should be sorting them on total level but it appears to be random, when u remove the format,0 it goes back to working

I would like it to display commas if the number number is the thousands for example: Total Level: 5,532 and Total EXP 5882,882

See live demo: http://mcbuffalo.com/playground/leaderboards/server/factions-mcmmo.php

Updated Code trying to use Format:

    $sql = ("SELECT a.id, a.user, b.TotalXP, c.TotalLevel
   FROM (SELECT id, user FROM factions_mcmmo_users) a 
   INNER JOIN (
        SELECT user_id, FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy), 0) as TotalXP 
        FROM factions_mcmmo_experience
        GROUP By user_id
   ) b on b.user_id = a.id
   INNER JOIN (
       SELECT user_id, FORMAT(Sum(taming)+Sum(mining)+Sum(woodcutting)+Sum(repair)+Sum(unarmed)+Sum(herbalism)+Sum(excavation)+Sum(archery)+Sum(swords)+Sum(axes)+Sum(acrobatics)+Sum(fishing)+Sum(alchemy), 0)  as TotalLevel
       FROM factions_mcmmo_skills
       GROUP by user_id

   ) c on  c.user_id = a.id
   ORDER BY TotalLevel DESC;");

EDIT 5 Changed number with PHP, everything works

Original Images

ScaisEdge · Accepted Answer · 2017-05-27 19:33:05Z

1

you could use an couple of inner join

$sql = ("SELECT a.id, a.name,  b.TotalXP, c.TotalLevel
       FROM (SELECT DISTINCT id, name FROM factions_mcmmo_users) a 
       INNER JOIN (
            SELECT user_id, COUNT(*) as TotalXP 
            FROM factions_mcmmo_experience
            GROUP By user_id
       ) b on b.user_id = a.id
       INNER JOIN (
           SELECT user_id, COUNT(*)  as TotalLevel
           FROM factions_mcmmo_skills
           GROUP by user_id

       ) c on  c.user_id = a.id
       LIMIT 10

edited May 27, 2017 at 19:33

answered May 27, 2017 at 16:47

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Christopher Ward Over a year ago

I get the error "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM factions_mcmmo_users a INNER JOIN factions_mcmmo_experience b on WHERE b.us' at line 1"

Christopher Ward Over a year ago

That seemed to be counting the total rows in the table? i seemed to have worked it out and have fixed it with an edit in the top post, could you tell me if there is a better way to do it or if that way is fine.

ScaisEdge Over a year ago

i have see you code .. if you are able, the use of join is really better respect the use of subselect ..in more fast .. and more coherent with relational model and sql features ..

Christopher Ward Over a year ago

Getting this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'b.user_id' in 'on clause'

Christopher Ward Over a year ago

Thanks for the further help, i couldn't get it to work but after i made some modifications it works, see edit 3 on the main post, not sure if i was supposed to or how to get it to display the persons name and format the number with commas if it's in the thousands

|

Collectives™ on Stack Overflow

Multiple Counts in MYSQL PHP Query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related