Menu
Problem
So, I have no idea as to how I can can do this but what I want to do is to get the first and last name's of the leader and the students using their id. After I've got the first and last names of the students and leader, I need to output it.
Teams Table
id | leaderID | studentID
1 | 123 | 123456
2 | 123 | 09
3 | 123 | 7776
4 | 233 | 80
5 | 233 | 997
Student's 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'm Currently Getting (First id is the leader's 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 names corresponding to 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>
(
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 query consists of two SELECTs combined with a UNION. The first selects only the leaders removing duplicates with GROUP BY. The second sub-query selects the rest of the students. Finally, the result set is sorted by leader ID in ascending order, and by the leader flag in descending order.
Sample Output
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 likely need to optimize the table structures in order to run this query without performance penalties. For example, you may need to add indexes for leaderID and studentID columns.
Often a few simple queries are faster than a single complex query. Also, in some cases sorting and grouping operations are faster when implemented in PHP rather than in SQL. The following is another approach which will likely run faster than the above-mentioned complex query. Make some benchmarks in order to figure out which is better 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 leaders and stores them into $result by studentID keys.
The second block collects the rest of the students and stores them in similar way into $result, if corresponding leaderID exists. (You can skip this check by passing leader IDs to WHERE clause via IN().)
Since $result is initially filled with leaders, the leaders will be stored at 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 former performs an SQL query on the database and returns cursor to the result set. The latter fetches the next result using the cursor.
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
