Join MySQL tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

Join MySQL tables

Post 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]
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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
Last edited by Jean-Yves on Tue Jan 11, 2005 10:11 am, edited 1 time in total.
Archy
Forum Contributor
Posts: 129
Joined: Fri Jun 18, 2004 2:25 pm
Location: USA

Post by Archy »

Thank you, worked nicely : )
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

No problem :)
Post Reply