Outputting a MySQL Left Join In PHP When Selecting All of The Left Table Columns And Specific Right Table Columns

Question

I have an 'imageposts' table that I am doing a left join with a 'users' table so that if there is an image post it fetches the data for the user who has done that related image post.

The two tables are linked because the 'user_id' column in the 'imageposts' table is the foreign key of the 'id' column in the 'users' table.

Although the code below works, when there is an image post it currently pulls in all of the fields from the 'users' table with the MySQL query (12 columns in total) and I'm only using 2 of those columns (firstname and lastname), which seems excessive / not the best solution?

I'm trying to work out how to only pull in two fields (firstname and lastname) from 'users', whilst selecting everything from the 'imageposts' table?

Any help much appreciated.

<?php

    $stmt = $connection->query("SELECT * FROM imageposts left join users on imageposts.user_id = users.id");

    while ($row = $stmt->fetch()) {

        // from imageposts table (every column in the table is used)
        $db_image_id = htmlspecialchars($row['image_id']);
        $db_image_title = htmlspecialchars($row['image_title']);
        $db_image_tags = htmlspecialchars($row['image_tags']);
        $db_image_filename = htmlspecialchars($row['filename']);
        $db_ext = htmlspecialchars($row['file_extension']);
        $db_processed= htmlspecialchars($row['user_processed']);
        $db_username = htmlspecialchars($row['username']);
        $db_profile_image_filename = htmlspecialchars($row['profile_image']);

        // from users table (only 2 of the 12 columns returned in the query are used)
        $db_firstname = htmlspecialchars($row['firstname']);
        $db_lastname = htmlspecialchars($row['lastname']);

    ?>

    <figure>
        <!-- HMTL output goes here including the above variables  -->
    </figure>

<?php } ?>

Just list out all columns from `imagepost` plus `firstname` and `lastname` from `users` table in `SELECT`. This also works `SELECT imagepost.*, users.firstname, users.lastname FROM ...` but it's much better to specify all the columns you want — FanoFN, Sep 17 '21 at 00:52

waterloomatt · Accepted Answer · 2021-09-17T01:10:49.193

2

If you want to select all using * from the first table, and only a selected few columns from the second table you can alias both tables and then use alias_a.* and alias_b.column_1, alias_b.column_2.

Ex.

SELECT pst.*, usr.firstname, usr.lastname 
FROM imageposts pst 
INNER JOIN users usr on pst.user_id = usr.id
;

I have an 'imageposts' table that I am doing a left join with a 'users' table so that if there is an image post it fetches the data for the user who has done that related image post.

I realize your query is using an inner join and not a left join but I just wanted to point out the distinction because your comment implies a misunderstanding. An inner join requires that data in both tables is present. If a matching row is not found in the connected table, the row from the main table will not be included in the result set. A left join is similar but will still return data from your main table even if no matching rows are found in your connected table. The values from the connected table will simply be null.

So if you were to use a left join in your example and post #1 didn't have an associated user, you'd end up with a result like,

SELECT pst.*, usr.firstname, usr.lastname 
FROM imageposts pst 
LEFT JOIN users usr on pst.user_id = usr.id
;

image_id	image_title	...	firstname	lastname
1	profile_1	...	null	null
2	profile_2	...	tony	martron

But an inner join would be,

image_id	image_title	...	firstname	lastname
2	profile_2	...	tony	martron

edited Sep 17 '21 at 01:10

answered Sep 17 '21 at 00:58

waterloomatt

3,662
1
19
25

I’m so sorry that was a typo. The code should’ve said left join. I have amended the question accordingly. – pjk_ok Sep 17 '21 at 03:30
In relation to my last comment. I should actually use an inner join then? Apologies for the confusion. – pjk_ok Sep 17 '21 at 03:32
Use an _inner_ if you want to return all rows from both tables where there is a match. Use a _left_ when you want all rows in A, plus any common rows in B. – waterloomatt Sep 17 '21 at 11:05
1

To make it easy, if your application _requires_ that all posts are created by a user, then you will always have a link from a post to user, right? In that case, use an inner join. This is the default case in most situations. But if your application allow guests without a user ID to create posts (ie. if `post.user_id` can be null), then use a left join to retrieve posts and its user regardless if the user exists. Don't forget to mark an answer as "Accepted" if think it's the best answer to your question. – waterloomatt Sep 17 '21 at 13:07
I noticed your answer doesn't use the `AS` clause? Is there a reason for that or is it just your preferred style? – pjk_ok Sep 17 '21 at 18:45
1

Preferred style. I generally write as few keywords as possible for readability. Ex. I usually just write `JOIN` instead of `INNER JOIN` since an inner join is the default join if you don't specify your join type. – waterloomatt Sep 17 '21 at 19:16

Amir Rahman · Answer 2 · 2021-09-17T01:10:13.063

first thing you can do is define all column names you want from both table in selector

<?php 
    query("select IP.col1 ,IP.col2 ,U.col3 FROM imageposts IP inner join users U on IP.user_id = U.id")
?>

this way mysql will only return values from selected columns only, which is efficient and another thing you can do is make a separate view for user table and select only two columns you want then inner join that view with your image posts

score 1 · Answer 3 · answered Sep 17 '21 at 01:14

1

You are using INNER JOIN instead of LEFT JOIN.

This will let you display only 3 values from users table, if you need to display more then you need to add them in query.

SELECT imgp.*, users.id, users.firstname, users.lastname
FROM imageposts AS imgp
LEFT JOIN users ON imgp.user_id = users.id

answered Sep 17 '21 at 01:14

Sorry that was a typo, I am using LEFT JOIN in my code. Will amend the question – pjk_ok Sep 17 '21 at 03:26
In this situation is it better to use a left join or an inner join? The typo in my code example was obviously confusing but have amended it now. – pjk_ok Sep 17 '21 at 03:36
it depends what you need see this for better understanding https://stackoverflow.com/a/28719292/12232340 – Sep 17 '21 at 11:54

Outputting a MySQL Left Join In PHP When Selecting All of The Left Table Columns And Specific Right Table Columns

3 Answers3