Get the first and last name matching their ID
Problem
So, I have no idea how I can do this, but what I want to do is get the first and last name of the leader and students using their ID. After I have the names and names of the students and the leader, I need to bring him out.
Command table
id | leaderID | studentID
1 | 123 | 123456
2 | 123 | 09
3 | 123 | 7776
4 | 233 | 80
5 | 233 | 997
Student table
studentID | firstname | lastname | teacherID
----------|-----------|----------|----------
123 | Dave | Jackson | 23
123456 | Jessie | Roberts | 23
09 | Rick | Rustels | 24
7776 | Blake | Jackson | 25
80 | Ashly | Kenson | 23
233 | Lilly | Street | 25
997 | Billy | Street | 24
What I am currently getting (the first id is the leader id)
123 123456 09 7776 233 80 997
What I want
Dave Jackson
Jessie Roberts
Rick Rustels
Blake Jackson
Lilly Street
Ashly Kenson
Billy Street
So, basically I want to get the first and last name that match their ID.
PHP Code
<?php
require '../connect.php';
$team_data = $link->prepare("SELECT leaderID, studentID, CONCAT(firstname, ' ', lastname) as firstlast FROM teams, students Order by leaderID, studentID");
$team_data->execute();
$team_data = $team_data->fetchAll();
if(!$team_data) {
header("Location: ../../admin.php?msg=Sorry we could not get the data");
}
$data = [];
foreach ($team['firstlast'] as $team) {
$leader_id = $team['leaderID'];
$student_id = $team['studentID'];
$data[$leader_id][] = $student_id;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Teams</title>
</head>
<body>
<?php foreach ($data as $leader_id => $students): ?>
<ul>
<li><strong><?php echo $leader_id; ?></strong></li>
<?php foreach ($students as $student_id): ?>
<li><?php echo $student_id; ?></li>
<?php endforeach; ?>
</ul>
<?php endforeach; ?>
</body>
</html>
Single request
(
SELECT 1 AS leader, s.studentID AS leaderID, s.studentID,
s.firstname, s.lastname
FROM teams AS t
JOIN students AS s ON s.studentID = t.leaderID
GROUP BY t.leaderID
) UNION (
SELECT 0 AS leader, t.leaderID, s.studentID, s.firstname, s.lastname
FROM teams AS t
JOIN students AS s ON s.studentID = t.studentID
) ORDER BY 2, 1 DESC;
The request consists of two SELECT
combined with UNION
. The first one selects only leaders who remove duplicates with GROUP BY
. The second subquery selects the rest of the students. Finally, the result set is sorted by presenter ID in ascending order and by icon leader
in descending order.
Output example
leader leaderID studentID firstname lastname
1 123 123 Dave Jackson
0 123 9 Rick Rustels
0 123 7776 Blake Jackson
0 123 123456 Jessie Roberts
1 233 233 Lilly Street
0 233 80 Ashly Kenson
0 233 997 Billy Street
Note. You will probably need to optimize your table structures to run this query without performance penalties. For example, you might want to add indexes on the columns leaderID
and studentID
.
Two requests + PHP
Several simple queries are often faster than one complex query. In addition, in some cases, sorting and grouping operations are faster than PHP rather than SQL. Below is a different approach that is likely to be faster than the aforementioned complex query. Do some tests to find out what's best for your case.
<?php
$result = [];
// Collect only leaders
$q = 'SELECT s.studentID, s.firstname, s.lastname
FROM students AS s, teams AS t
WHERE t.leaderID = s.studentID
GROUP BY 1';
$cursor = Db::query($q);
while ($row = Db::fetchNext($cursor)) {
$result[$row['studentID']][] = $row;
}
$cursor->free();
// Collect the rest
$q = 'SELECT s.studentID, t.leaderID, s.firstname, s.lastname
FROM students AS s, teams AS t
WHERE t.studentID = s.studentID';
$cursor = Db::query($q);
while ($row = Db::fetchNext($cursor)) {
$leader_id = $row['leaderID'];
if (isset($result[$leader_id])) {
$result[$leader_id][] = $row;
}
}
$cursor->free();
The first block selects only the leaders and stores them in the $result
keys studentID
.
The second block collects the rest of the students and stores them in a similar way $result
, if a corresponding one leaderID
exists. (You can skip this check by passing leader IDs in WHERE
via IN()
.)
Since it is $result
initially filled with leaders, the leaders will be kept in the first positions:
Array
(
[123] => Array
(
[0] => Array
(
[studentID] => 123
[firstname] => Dave
[lastname] => Jackson
)
[1] => Array
(
[studentID] => 123456
[leaderID] => 123
[firstname] => Jessie
[lastname] => Roberts
)
(skipped...)
)
[233] => Array
(
[0] => Array
(
[studentID] => 233
[firstname] => Lilly
[lastname] => Street
)
(skipped...)
)
)
Db::query()
and Db::fetchNext()
are imaginary functions. The first one executes the SQL query on the database and returns the cursor to the result set. The latter selects the next result with the cursor.
source to share
Using sql CONCAT(firstname, ' ', lastname)
::
Edit:
$team_data = $link->prepare("SELECT leaderID, studentID FROM teams order by leaderID, studentID");
To:
$team_data = $link->prepare("SELECT leaderID, studentID, CONCAT(firstname, ' ', lastname) as firstlast FROM teams, students Order by leaderID, studentID");
Then inside foreach
you can use$team['firstlast']
source to share
Since your first / last names are in the student table, you need to join that table. Thus, your request should look like this:
$team_data = $link->prepare("SELECT a.leaderID, a.studentID, CONCAT(b.firstname, ' ', b.lastname) AS full_name FROM teams AS a LEFT JOIN stundent_table AS b ON a.studentID = b.studentID ORDER BY a.leaderID, a.studentID");
Whatever the name of your student table. Change this accordingly.
source to share