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

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 } ?>

Answer

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