Show Name Instead of Id from Different Table

Show Name Instead of ID from Different Table

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

Query to display name for value instead of ID from one table

You can self-join the table twice:

select 
    t.name,
    t1.name parentName1,
    t2.name parentName2
from mytable t
left join mytable t1 on t1.personID = t.parentID1
left join mytable t2 on t2.personID = t.parentID2

---

Or you can use inline correlated subqueries:

select 
    t.name
    (select t1.name from mytable t1 where t1.personID = t.parentID1) parentName1,
    (select t1.name from mytable t2 where t2.personID = t.parentID2) parentName2
from mytable t

This assumes that there will not be more than 1 record in the table with a personID that matches a given parentID1 or parentID2 (else the query will fail because the inline subquery will return more than one record).

How to display name instead of id if records are in one table?

I have a hint (maybe this can give solution in your problem).
Try query like this :

SELECT 
t1.id , t1.firstname , t1.lastname ,t1.mobileno, 
CONCAT(t2.firstname ," ",t2.lastname ) AS createby 
FROM tbl_employee AS t1 
JOIN tbl_employee AS t2 ON t2.id = t1.created_by
WHERE t1.id = '3'

With above query you no need create temporary table or other additional tables.
I Tested it. >>>
http://sqlfiddle.com/#!9/e693cf/2/0

Show Referenced Name instead of ID from same Table

SELECT e.ID, e.name AS Employee, s.name AS Supervisor 
FROM employee e 
  INNER JOIN employee s 
  ON s.ID = e.supervisorID 
ORDER BY e.ID;

---

Here is more color on how to test this:

mysql> CREATE TABLE employee (ID INT NOT NULL AUTO_INCREMENT, supervisorID INT NOT NULL DEFAULT '1', name VARCHAR(48) NOT NULL, PRIMARY KEY (ID));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee VALUES (1, 1, "The Boss"), (2,1, "Some Manager"), (3,2, "Some Worker"), (4,2, "Another Worker");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT e.ID, e.name AS Employee, s.name AS Supervisor
FROM employee e INNER JOIN employee s
ON s.ID = e.supervisorID ORDER BY e.ID;
+----+----------------+--------------+
| ID | Employee       | Supervisor   |
+----+----------------+--------------+
|  1 | The Boss       | The Boss     |
|  2 | Some Manager   | The Boss     |
|  3 | Some Worker    | Some Manager |
|  4 | Another Worker | Some Manager |
+----+----------------+--------------+
4 rows in set (0.01 sec)

mysql> 

Laravel - Show name instead of id

There few issues with your code;

Relationship

You have 1-N relationship, a Planet belongsTo a Solar System and a Solar System hasMany Planet.

Planet.php

public function solar()
{
    return $this->belongsTo(SolarSystems::class, 'solar_systems_id');
}

SolarSystems.php

public function planets()
{
    return $this->hasMany(Planet::class, 'solar_systems_id');
}

Controller

PlanetController.php

public function show($planeet)
{
    $planets = Planet
              ::with('solar')
              ->where('name', $planeet)
              ->get();
       
    return view('welcome', ['planets' => $planets]);
}

Has you can see, I'm using with and where to optimize the query.

• with will eager load the relationship: well explained in the documentation
• where will add a criteria to the database query, which will perform more efficiently than a collection. Usage

View

Since you have loaded the relationship with the eager loading, you can directly have access to the model from the planet.

 <p>{{ ucfirst($planeten->solar->name) }}

How to get relation table data name instead of id

const sql = "SELECT h.id, h.name, u.name as universe_id FROM `hero` as h LEFT JOIN `universe` as u ON h.universe_id=u.id";

---

Related Topics