Page 1 of 1

Join MySQL tables

Posted: Thu Dec 23, 2004 7:58 am
by Archy
I am having a problem with joining 2 tables, and getting the required output. I currently have a member list where people can choose how they want to see it; either sorted by usernames, userlevels, joined date etc.

I have done everything for it apart from the userlevels, which I need to join 2 tables. In the userlevel table I have the order in which to display the levels. For simplicity, the table could look a bit like below.

ID order userlevel
1 1 Admin
2 3 Member
3 2 Moderator

The member table could look a bit like below

ID username userlevel
1 Chris Admin
2 John Member
3 Olly Moderator
4 Matt Member
5 Dave Member

I need to get the order, and then display each user in terms of their userlevel, and then username, so the output would look like below:

Chris
Olly
Dave
John
Matt

I am using this code at the moment:

Code: Select all

$sql = "SELECT * FROM `users`, `levels` ORDER BY levels.order, levels.userlevel LIMIT $perPage";
$perPage is simply how many users to display on one page.

Any help would be gratefully appreciated,
Thanks, Archy.[/google]

Posted: Thu Dec 23, 2004 8:37 am
by Jean-Yves

Code: Select all

$sql = "SELECT * FROM users INNER JOIN levels ON levels.userlevel = users.userlevel ORDER BY levels.order, levels.userlevel LIMIT $perPage";
Try that

Strictly speaking, you would not have a userlevel field in users set as text, but would rather use a foreign key (integer) that matched the integer key in the levels table

Posted: Thu Dec 23, 2004 8:54 am
by Archy
Thank you, worked nicely : )

Posted: Thu Dec 23, 2004 10:15 am
by Jean-Yves
No problem :)