Show Name Instead of ID from Different Table

Question

I have a 2 tables:

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

Note: Category_id now displays ID correctly

I want to show Name instead of ID for output from 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'];

I think something in this question is mixed up. Please review and edit it to make it clearer. Perhaps provide an example of the desired output. — Matt, Mar 09 '16 at 16:15
How ID fields of category and employee are related with each other? If they are related then you can use JOIN function of MySQL query to get and display category_id from employee table. — K Ahir, Mar 09 '16 at 16:16

score 5 · Accepted Answer · edited May 23 '17 at 11:45

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

Or, more readably:

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

(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'];

yes, this is correct answer, I just need to clarify the issue and to make it display the category for the correct id `WHERE id = '" . $_GET['id'] . "'`, as when I add "where" an error appears **mysql_fetch_assoc() expects parameter 1 to be resource,**. _I have read your note regarding 'where'_, now I need to find an alternative. — TestPRK, Mar 10 '16 at 12:27
@TestPRK: That error means the query is failing. Use `mysql_error()` to get the error message from the database. The alternative is to use prepared statements with query parameters. Currently your code is basically executing user input *as code*, so users can execute any code they want on your server. Prepared statements with query parameters treat user input as *data*, not as *code*. — David, Mar 10 '16 at 13:05

score 1 · Answer 2 · answered Mar 09 '16 at 16:15

You need to join category table

$categ = mysql_query("
SELECT employee.*, category.name as category_name FROM employee 
INNER JOIN category on category.id = employee.category_id
WHERE id = '" . $_GET['id'] . "'");

Then output with $website_cat['category_name']

Show Name Instead of ID from Different Table

2 Answers2

Linked

Related