1

I'm currently working on building a text-based game. I've been able to create the signup and where the user can pick between three classes. The next part I'm working is on is having a ranking system. The ranking would order the users by username and by highest-lowest experience, based on the user class. I don't have much experience with JOINS and the query is not giving the desired results. Any help would be appreciated.

SELECT ux_users.id, ux_users.username, ux_users_power.experience 
FROM ux_users, ux_users_power 
WHERE user_class ='ninja' 
ORDER BY experience DESC 
LIMIT 5

Table One ux_users:

     id   |  username |   
------------------------
     1    |   zak     |
     2    |   mike    |
     3    |   john    |

Table Two ux_users_power:

     id   | experience|   user_class   
---------------------------------
     1    |     22    |     Ninja
     2    |     01    |     Ninja
     3    |     34    |     Pirate
6
  • 1
    what is your expected result? why did you tag it with PHP even if the question is MySQL only? Commented Sep 14, 2020 at 22:38
  • this would be a good starting point in understanding joins: mysqltutorial.org/mysql-join Commented Sep 14, 2020 at 22:39
  • How are these two tabes related? They look totally different.
    – tadman
    Commented Sep 14, 2020 at 22:46
  • @CornelRaiu I'm running the query within a PHP file. Since I'm not showing the PHP code, I will remove the PHP tag.
    – cod3rShea
    Commented Sep 14, 2020 at 22:46
  • @tadman The first table has information from the user when they signup (username, email, password). The second table has the user's stats(attack, hp, class bonus attack, class bonus hp, character class). I need both the User username and exp.
    – cod3rShea
    Commented Sep 14, 2020 at 22:57

1 Answer 1

1

Take the plunge into the 21st century and use explicit JOIN operations in place of the old-timey comma join.

In your case, this should work.

FROM ux_users
JOIN ux_users_power 
     ON ux_users.id = ux_users_power.id

The ON clause indicates the criteria for matching rows from the tables on the two sides of the join.

This is based on my guess that your two tables' id values both identify the user.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.