JOIN MySQL Tables, echo data based on different tables

[tmac5]

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

 

users: id / username / password / realname / access_level

users_access_level: access_level / access_name

 

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

 

Example row for users: 1234 / tmac / password / tmac / 99

Example row for users_access_level: 99 / Admin

 

Using the examples above, I would want the output to appear as such:

 

Username:Access Name:

TmacAdmin

 

I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

 

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

 

Thanks for reading!

[tmac5]

Thanks for anyone that looked at this thread. After playing around with the code off and on, I was able to find a way to make them interchangeable with this:

 

<?php

        $query = "SELECT users.username, users.access_level, users_access_level.access_name ".
"FROM users LEFT JOIN users_access_level ".
"ON users.access_level = users_access_level.access_level"; 
$result=mysqli_query($db,$query);

while ($row = mysqli_fetch_array($result)) 
{
echo "<tr><td>";
echo $row['username'];
echo "</td><td class='center'>";
echo $row['access_name'];
}
?>

 

If there is a better way to do this, I'd appreciate the guidance, but I do have a separate question. The reason I have it setup like this is I want to make a <select></select> option, that once the query is run, will automatically make the user's "access_name" the "selected" option by default when the page loads. I want to make it so I can change the user's access_level on the fly with a quick SQL update/delete query.

 

Thanks again!

[btherl]

Are there limits on how many access levels each user can have?  Can they have 0, 1, or more?

 

A left join is required if users can have 0 access levels AND you still want them listed.

 

If all users have exactly 1 access level then you can use "JOIN" instead of "LEFT JOIN".  The left join will still work but it limits what query plans the database can use, so it's better to use a plain "JOIN" if you don't need the left join.

[tmac5]

Are there limits on how many access levels each user can have?  Can they have 0, 1, or more?

 

A left join is required if users can have 0 access levels AND you still want them listed.

 

If all users have exactly 1 access level then you can use "JOIN" instead of "LEFT JOIN".  The left join will still work but it limits what query plans the database can use, so it's better to use a plain "JOIN" if you don't need the left join.

 

Ah, gotcha. Yes, each user will only have 1 access level that can be easily changed based on needs. So, I shall correct this with a JOIN and notate this thread as solved, thanks!