显示不同表中的名称而不是 ID

我有 2 张桌子：

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

Note: Category_id现在显示ID正确地

我想展示Name代替ID对于输出Employee.

Attempt:

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

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

Category Table:

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

Employee Table:

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

Output:

echo $website_cat['category_id'];

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

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 注入漏洞放入答案中。请阅读这个 https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php了解正确执行涉及用户输入的 SQL 查询的一些基础知识。目前，您的代码很容易遭受一种非常常见的攻击形式。）

由于某些列共享相同的名称，您甚至可能需要更明确地请求它们：

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 = ...

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

employee_id, category_id, category_name

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

echo $website_cat['category_name'];