显示名称而不是来自不同表的 ID [英] Show Name Instead of ID from Different Table

问题描述

我有 2 张桌子:

• Category 带有主键 ID 和列 Name
• Employee 带有主键 ID 和列 Category_id

• Category with Primary Key ID and column Name
• Employee with Primary Key ID and column Category_id

注意:Category_id 现在可以正确显示 ID

Note: Category_id now displays ID correctly

我想为 Employee 的输出显示 Name 而不是 ID.

I want to show Name instead of ID for output from Employee.

尝试:

$categ = mysql_query("SELECT * FROM employee WHERE id = '" . $_GET['id'] . "'");
$rows = array();

while ($row = mysql_fetch_assoc($categ)) {
  $website_cat = $row;
}

Category 表格:

+----+----------------+
| ID | Name           |
+----+----------------+
| 23 | Manager        |
| 10 | Boss           |
| 14 | Worker         |
| 41 | Another        |
+----+----------------+

Employee 表格:

+----+----------------+
| ID | Category_id    |
+----+----------------+
|  1 | Manager        |
|  2 | Boss           |
|  3 | Worker         |
|  4 | Another        |
+----+----------------+

输出:

echo $website_cat['category_id'];

推荐答案

您要查找的 SQL 关键字是 JOIN.您的查询可能是这样的:

The SQL keyword you're looking for is JOIN. Your query could be something like this:

SELECT * FROM employee INNER JOIN category ON employee.category_id = category.id WHERE id = ...

或者，更易读:

SELECT
  *
FROM
  employee
  INNER JOIN category
    ON employee.category_id = category.id
WHERE
  id = ...

(注意:我故意删除了 WHERE 子句的最后一点，因为我不习惯将 SQL 注入漏洞放在答案中.请阅读本文以了解正确执行涉及用户输入的 SQL 查询的一些基础知识.目前您的代码是完全开放的一种非常常见的攻击形式.)

(Note: I removed that last bit of the WHERE clause on purpose because I'm not comfortable putting SQL injection vulnerabilities in an answer. Please read this to learn some of the basics of properly executing SQL queries involving user input. Currently your code is wide open to a very common form of attack.)

由于您的某些列具有相同的名称，您甚至可能想要更明确地请求它们:

Since some of your columns share the same name, you may even want to more explicitly request them:

SELECT
  employee.id AS employee_id,
  category.id AS category_id,
  category.name AS category_name
FROM
  employee
  INNER JOIN category
    ON employee.category_id = category.id
WHERE
  id = ...

然后在您的代码中您可以访问这些字段:

Then in your code you'd have access to these fields:

employee_id, category_id, category_name

所以你可以输出你想要的值:

So you could output the value you want:

echo $website_cat['category_name'];

这篇关于显示名称而不是来自不同表的 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！