尝试从1个表中的1列获取信息，该信息基于从1个单个SELECT语句中的另一个表中的另一列检索的信息

Question

I'm trying to get information from 1 column in 1 table based on information retrieved from another column in anouther table in 1 single SELECT statement while on a mission to simplify my code as much as posible

I have 2 tables

video

------------------------------
| id |  date  | userID | ... |
------------------------------
| 1  | <date> |   1    |     |
------------------------------
| 2  | <date> |   5    |     |
------------------------------
| 3  | <date> |   1    |     |
------------------------------
| 4  | <date> |   2    |     |
------------------------------
| 5  | <date> |   2    |     |
------------------------------
.
.
.
------------------------------
| n  |        | user-n |     |
------------------------------

(video with id 1 was uploaded by user with id 1 in the below table)
(video with id 2 was uploaded by user with id 5)
(video with id 3 was uploaded by user with id 1)
Etc.

userinfo

-----------------------------
| id | ... | username | ... |
-----------------------------
| 1  |     | Bob      |     |
-----------------------------
| 2  |     | Alice    |     |
-----------------------------
| 3  |     | Ned      |     |
-----------------------------
| 4  |     | Jill     |     |
-----------------------------
| 5  |     | John     |     |
-----------------------------
.
.
.
-----------------------------
| n  |     | name-n   |     |
-----------------------------

(user with id 1 has username Bob)
(user with id 2 has username Alice)
Etc.

What I need is to get the username from the 8 latest videos ordered by date

So if the order of videos after ordering by date is
video 5
video 1
video 3
video 4
video 2
.
.
.

I want to get the usernames of uploaders in that order too

So in this case the result should be
Alice (video id 5 uploaded by user with id 2 = Alice)
Bob (video id 1 uploaded by user with id 1 = Bob)
Bob (video id 3 uploaded by user with id 1 = Bob)
Alice...
John...
.
.
.

connection.php

try {

    $servername = "localhost";
    $username = "*****";
    $password = "*********";
    $database = "****";
    $charset = 'utf8';

    $dsn = "mysql:host=$servername;dbname=$database;charset=$charset";

    $opt = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    $pdo = new PDO ( $dsn , $username , $password , $opt );

} catch ( PDOException $e ) {

    file_put_contents ( 'PDOError.txt', $e->getMessage(), FILE_APPEND );
    echo "Failed to connect to database!";
    die ();

}

Code that works but is way to cumbersome

functions.php

try {

    $prepareVideo = $pdo->prepare( "SELECT userID FROM video WHERE 1 ORDER BY date DESC LIMIT 8" );
    $prepareVideo->execute([]);
    $geVideo = $prepareVideo->fetchAll();

} catch ( PDOException $e ) {

    file_put_contents ( 'error.txt', $e->getMessage(), FILE_APPEND );

}

if ( $geVideo ) {

    foreach ( $geVideo as $row ) {

        $userID = $row['userID'];

        try {

            $prepareUser = $pdo->prepare( "SELECT username FROM userinfo WHERE id = ? LIMIT 1" );
            $prepareUser->execute([$userID]);
            $getUser = $prepareUser->fetch();

        } catch ( PDOException $e ) {

            file_put_contents ( 'error.txt', $e->getMessage(), FILE_APPEND );

        }

        if ( $getUser ) {

            $username[] = $getUser['username'];

        }

    }

}

But this is way to much code for this

What I want to do is do the same as above but only in 1 single SELECT statement, like

functions.php

try {

    $prepareUsername = $pdo->prepare( "SELECT..." );
    $prepareUsername->execute([]);
    $getUsername = $prepareUsername->fetch();

} catch ( PDOException $e ) {

    file_put_contents ( 'error.txt', $e->getMessage(), FILE_APPEND );

}

if ( $getUsername ) {

    $username[] = $getUsername['username'];

}

Insert single SELECT statement that does the same as above

What I have tried so far

SELECT u.username FROM userinfo u WHERE u.id IN ( SELECT v.userID FROM video v )

but that just lists the usernames of those who has uploaded a video

In this case the 3 names in order of their id in the userinfo table
Bob
Alice
John

And that's not what I want

Hope someone can help

Answer 1

Select from video in the correct order, while joining userinfo.

E.g.:

SELECT v.id AS videoID, u.username
  FROM video AS v
  JOIN userinfo AS u ON v.userID = u.id -- or left join, if user might not exist
 ORDER BY v.uploadedAt DESC

Demo: https://www.db-fiddle.com/f/quQZ9Cnx14exk4BHqD5sEV/2