MySQL PHP左连接查询求助：关联Users与Department表显示用户信息

Hey there! Let's fix this so you can display both user and their linked department data in your table. Here's a step-by-step solution:

1. Clean Up Your SQL Query #

Your initial join attempt is on the right track, but using explicit JOIN syntax (instead of the old comma-separated table list) is cleaner and more maintainable. Also, avoid SELECT *—explicitly list the fields you need to prevent conflicts (like if both tables have a column with the same name) and improve performance.

For example, if your Department table has a Department_Name column (replace this with your actual department field name), use this query:

SELECT 
    Users.firstname, 
    Users.lastname, 
    Users.email, 
    Users.phnumber, 
    Users.provider, 
    Users.location, 
    Users.Dpt_id, 
    Department.Department_Name, -- Add your department fields here
    Users.Dadmin, 
    Users.Superuser
FROM Users
INNER JOIN Department ON Department.Department_ID = Users.Dpt_id

• Use INNER JOIN if you only want users who have a linked department.
• Use LEFT JOIN instead if you need to include users who don't have a department assigned (their department fields will show as NULL).

2. Update Your Table Output #

Now you can access the department fields from your query result. I also added htmlspecialchars() to all output to prevent XSS attacks—this is a best practice you should always follow!

Here's the full updated code:

require_once "database.php";

// Define your query with explicit fields and join
$sql = "SELECT 
            Users.firstname, 
            Users.lastname, 
            Users.email, 
            Users.phnumber, 
            Users.provider, 
            Users.location, 
            Users.Dpt_id, 
            Department.Department_Name, 
            Users.Dadmin, 
            Users.Superuser
        FROM Users
        INNER JOIN Department ON Department.Department_ID = Users.Dpt_id";

$output = mysqli_query($conn, $sql);

// Check if the query ran successfully
if (!$output) {
    die("Query failed: " . mysqli_error($conn));
}
?>

<table>
    <thead>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Phone Number</th>
            <th>Provider</th>
            <th>Location</th>
            <th>Dept ID</th>
            <th>Department</th> <!-- New column for department data -->
            <th>Role</th>
        </tr>
    </thead>
    <tbody>
        <?php while ($single = $output->fetch_assoc()):?>
        <tr>
            <td><?php echo htmlspecialchars($single['firstname']); ?></td>
            <td><?php echo htmlspecialchars($single['lastname']); ?></td>
            <td><?php echo htmlspecialchars($single['email']); ?></td>
            <td><?php echo htmlspecialchars($single['phnumber']); ?></td>
            <td><?php echo htmlspecialchars($single['provider']); ?></td>
            <td><?php echo htmlspecialchars($single['location']); ?></td>
            <td><?php echo htmlspecialchars($single['Dpt_id']); ?></td>
            <td><?php echo htmlspecialchars($single['Department_Name']); ?></td> <!-- Display department data -->
            <td>
                <?php 
                if ($single['Dadmin'] == 1) { 
                    echo '<p>Department admin</p>'; 
                } elseif($single['Superuser'] == 1) { 
                    echo '<p>SUPER</p>'; 
                } else { 
                    echo '<p>USER</p>'; 
                } 
                ?>
            </td>
        </tr>
        <?php endwhile ?>
    </tbody>
</table>

Quick Notes #

• Replace Department_Name with the actual column name(s) from your Department table that you want to display (like Department_Description, Location, etc.).
• The htmlspecialchars() function escapes special characters to prevent malicious code from being injected into your page—always use this when outputting user-supplied data.

内容的提问来源于stack exchange，提问作者CodeBug