php - 尝试在 MYSQL 表中显示 'name' 而不是 'id'

Question

我有一个简单的表单，它显示用户详细信息并连接来自单独表的用户名。在mysql的用户表中，我有以下列'user_record_id'，'forename'，'surname'，'email'，'role_type_code'在login_details表中，我有以下列'用户名，密码，user_record_id'

users 表中的“role_type_code”是 role_type 表的外键，该表包含以下列“role_type_code”和“role_title”。

我正在尝试显示“role_title”而不是代码，但我不确定如何使用表格来完成此操作。

我之前使用下拉列表完成了此操作，但我不确定如何将相同的逻辑应用于此表:

<select name="role">
      <?php foreach($roles as $role): ?>
          <option value="<?php echo $role['role_type_code'] ?>"><?php echo $role['role_title'] ?></option>
      <?php endforeach ?>
  </select>

这是表格:

<?php

    include "db_conx.php";

    try {

        $db_conx = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);

        $db_conx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $db_conx->prepare('SELECT * FROM user INNER JOIN login_details ON user.user_record_id=login_details.user_record_id ORDER BY user.surname');
        $stmt->execute();
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    catch(Exception $e)
    {
        die ("Could not connect to the database $mysql_dbname :" . $e->getMessage());
    }
    ?>

    <h4><center>Manage Users</center></h4>

    <div class="container">
        <div class = "container-fluid">
            <div id = "table_container" style="width:auto; margin-top:50px;" class="mainbox col-md-6">
                <div class="row clearfix">
                    <div class="col-md-12">
                        <table class="table table-bordered table-hover" id="tab_logic">
                            <thead>
                                <tr >
                                    <th class="text-center">
                                        User Record ID
                                    </th>
                                    <th class="text-center">
                                        Forename
                                    </th>
                                    <th class="text-center">
                                        Surname
                                    </th>
                                    <th class="text-center">
                                        Email
                                    </th>
                                    <th class="text-center">
                                        Role Type
                                    </th>
                                    <th class="text-center">
                                        Username
                                    </th>
                                </tr>
                            </thead>
                            <tbody>
                                <!-- populating the table with information from mysql database -->
                                <?php foreach ($users as $row) {
                                    echo "<tr><td>";
                                    echo $row['user_record_id'];
                                    echo "</td><td>";
                                    echo $row['forename'];
                                    echo "</td><td>";
                                    echo $row['surname'];
                                    echo "</td><td>";
                                    echo $row['email'];
                                    echo "</td><td>";
                                    echo $row['role_type_code'];
                                    echo "</td><td>";
                                    echo $row['username'];
                                    echo "</td><td>";
                                    echo "</tr>"; }
                                    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>

任何帮助将不胜感激。谢谢!

Answer 1

如何将查询更改为:

SELECT u.*, l.*, r.role_title  FROM user u
INNER JOIN login_details l USING (user_record_id)
LEFT JOIN role_type r USING (role_type_code)
ORDER BY u.surname

然后就可以输出:

 echo $row['role_title'];