Resolved: How to retrieve data from another table in MySQL while having a data from column from the first table?

Question:

Before I start, I am using Laravel 8 & PHP 7.4.29.
The effect I want to achieve is that you can get the username from another table (of users) based on column from the first table (of videos).
Let’s take a closer look on the first table. It contains such columns as title, description, uploader (having a value of UUID of the user), etc. The problem I am facing is that I am actually unable to retrieve the username I want. Instead of, I am getting such error:

Trying to get property 'username' of non-object

and the code is:
(the code is written properly, but Stack Overflow syntax highlighting is broken, I guess)

<?php
$videos = DB::select("select * from videos where title like concat(?) or description like concat(?) and public = ?", ["%".$_GET['query']."%", "%".$_GET['query']."%", 2]);
    foreach ($videos as $video) {
        $users = DB::select("select * from users where uuid = ?", [$video->uploader]);
?>
<div class="video">
    <a href="/video?id={{{ $video->videoid }}}">
        <div class="row">
            <div class="col-12 col-md-3">
                <div class="thumbnail">
                    <!-- the value below is a placeholder so far -->
                    <span class="duration-mark">4:51</span>
                </div>
            </div>
            <div class="col-12 col-md-9 my-2 my-md-0">
                <h4 class="mb-1">{{{ $video->title }}}</h4>
                <p class="viddesc mb-2">{{{ $users->username }}} ● {{{ date('Y/m/d', strtotime($video->uploaddate)); }}}</p>
                <p class="viddesc">{{{ $video->description }}}</p>
            </div>
        </div>
    </a>
</div>
<?php
    }
?>

Assigning the value to a new variable did not work, too.
I’ve browsing Stack Overflow a while ago and found the solution, that I should retrieve data from both tables in one SQL request (in my case – select * from videos, users). It somehow worked, but instead of expected result (I have 1 video information in the first table & 2 users in the second table), the results were duplicated.
I actually had some experience with PHP and Laravel back then, but I can’t recall how could this be done (I’ve took a fairly big break from programming in PHP). If there’s any solution, it would be welcome.
Thanks in advance!

Answer:

Use Joins
in your case, Use left join if you have videos without matching uploader and if you need all the videos.
also you can use inner join if you want to get videos only if the matching uploader exists in the table.

select videos.*,users.* from videos LEFT JOIN users ON users.uuid = videos.uploader where title like concat(?) or description like concat(?) and public = ?", ["%".$_GET['query']."%", "%".$_GET['query']."%", 2]

Refer : https://www.w3schools.com/sql/sql_join_left.asp
Note : If you have a common column in both tables then the value of the videos record will be overrided by users column value
Ex : videos [id,name,uploader,addedAt] users [uuid, name]

 in this case the result will be 


    videos.id,videos,uploader,videos.addeAt,users.uuid,users.name
* The videos.name column willbe overrided by users.name

To overcome this issue use the above query like this
(Example only)

select videos.*,users.*,videos.name as "videoname" from videos LEFT JOIN users ON users.uuid = videos.uploader where title like concat(?) or description like concat(?) and public = ?", ["%".$_GET['query']."%", "%".$_GET['query']."%", 2]

If you have better answer, please add a comment about this, thank you!

Resolved: How to retrieve data from another table in MySQL while having a data from column from the first table?

Resolved: Input Focus between two React Components

Resolved: linq2db throws exception when filtering by nested collection

Resolved: Table data is coming as empty in React

Leave A Reply