CODEWITHSUNDEEP
phpmysql
TestPRK
TestPRK

Reputation: 21

Show Name Instead of ID from Different Table

I have a 2 tables:

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

Upvotes: 2

Views: 6604

Answers (2)

Tuan Duong
Tuan Duong

Reputation: 515

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

Upvotes: 1

David
David

Reputation: 218818

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

Upvotes: 5

Related Questions