php - 在 mysql 中给外键起别名

Question

假设我有两个表contacts 和departments。

表格内容如下:

联系人:

id |  first_name | last_name | email            | department_id
10 |  Mani       | Raj       | raj@mail.com     | 2
11 |  Santhos    | Sam       | santhos@mail.com | 3

部门:

id | department_name
1  | HR
2  | Sales
3  | Finance

我正在尝试使用 INNER JOIN 打印表格的详细信息

我的查询是:

$sql_query = "SELECT * FROM contacts INNER JOIN departments, departments.id as departments.department_id ON contacts.department_id=departments.department_id ORDE`R BY contacts.id";

我提到了下面的问题: SQL exclude a column using SELECT * [except columnA] FROM tableA?

但我不想创建临时表。

我在想可能有一种方法可以从部门表中删除 id 列。

添加

我可以使用查询打印表中的元素:

$sql_query = "SELECT * FROM $table_name INNER JOIN departments ON $table_name.department_id=departments.department_id ORDER BY $table_name.id";

效果很好，但问题是当我尝试在我的网页上打印详细信息时，contacts 表中的 id 不知何故被 覆盖了id 来自 departments 表。

我用来打印表格的代码是:

if ($query_result->num_rows > 0) {
            ?>
            <table>
                <tr>
                    <th>ID</th>
                    <th> First Name </th>
                    <th> Last Name </th>
                    <th>E-mail</th>
                    <th>Department</th>
                </tr>
                <?php
                while($row = $query_result->fetch_assoc()) {
                    ?>
                    <tr>
                        <td> <?php echo $row["id"]; ?> </td>
                        <td> <?php echo $row["first_name"]; ?> </td>
                        <td> <?php echo $row["last_name"]; ?> </td>
                        <td> <?php echo $row["email"]; ?> </td>
                        <td> <?php echo $row["department_name"]; ?> </td>
                    </tr>
                    <?php
                }
                ?>
            </table>
            <?php           
        }

这段代码的输出是:

ID | First Name | Last Name     | E-mail            | Department
2  | Mani       | Raj           | mani@mail.com     | Sales
8  | Santhos    | Sam           | santhos@mail.com  | Finance

而我的输出应该是:

ID | First Name | Last Name     | E-mail            | Department
10 | Mani       | Raj           | mani@mail.com     | Sales
11 | Santhos    | Sam           | santhos@mail.com  | Finance

现在我将如何在不被覆盖的情况下打印值？

我是新手。请帮助我...

编辑

好的，很酷，这行得通

$sql_query = "SELECT *, contacts.id as id FROM contacts INNER JOIN departments 
ON contacts.department_id=departments.department_id ORDER BY contacts.id";

但我也在尝试将详细信息导出到 .xls 文件。我在那里遇到错误!!

这是我的错误

You have an error in your SQL syntax     check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as id FROM  INNER JOIN departments ON .department_id=departments.id ORDER BY .id' at line 1<br />
<b>Warning</b>:  Invalid argument supplied for foreach() in <b>/opt/lampp/htdocs/contacts/export.php</b> on line <b>23</b><br />    

我的代码是:

$flag = false;
foreach($query_result as $row) {
    if(!$flag) {
        // display field/column names as first row
        echo implode("\t", array_keys($row)) . "\r\n";
        $flag = true;
    }
    array_walk($row, __NAMESPACE__ . '\cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
}

function cleanData(&$str) {
        $str = preg_replace("/\t/", "\\t", $str);
        $str = preg_replace("/\r?\n/", "\\n", $str);
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
    }

Answer 1

你需要告诉 SQL 你想要从哪个表中获取数据:

$sql_query = "SELECT t1.id, t1.first_name, t1.last_name, t1.email, t2.department_name 
FROM contacts t1
INNER JOIN departments t2 ON t12.department_id=t1.id 
ORDER BY t1.id";

当你在两个表中有相同的字段时(比如你的 ID)，SQL 会混淆选择哪个。

使用 * 不是一个好习惯，因为一旦您的表结构发生变化，它可能会破坏您的应用程序，但是，如果您必须使用它，则可以使用以下方法:

$sql_query = "SELECT t1.*, t2.department_name 
FROM contacts t1
INNER JOIN departments t2 ON t2.department_id=t1.id 
ORDER BY t1.id";

这里是“动态”表的例子

$sql_query = "SELECT t1.*, t2.department_name 
FROM $table_name t1
INNER JOIN departments t2 ON t2.department_id=t1.id 
ORDER BY t1.id";

要使其完全动态，您可以执行以下操作。

我将以您的两个表为例(未经测试，但应该能让您走上正轨):

$table1_name = "contacts;
$table1_key = "id";
$table1_fields = "*";

$table2_name = "departments";
$table2_key = "id";
$table2_fields ="department_name";


$sql_query = "SELECT t1.$table1_fields, t2.$table2_fields
  FROM $table1_name t1
  INNER JOIN $table2_name t2 
    ON t2.$table2_key = t1.$table1_key
  ORDER BY t1.$table1_key";