PHP中MySQL左连接如何选取左表全部字段与右表指定部分字段

核心修改方案 #

你只需要调整SELECT子句的字段声明，明确指定要拉取的字段即可：

1. 用imageposts.*拉取imageposts表的全部字段
2. 单独声明users.firstname、users.lastname获取users表需要的两个字段

修改后的查询语句如下：

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

对应的PHP代码修改后为：

<?php
    // 仅修改查询语句部分即可，后续逻辑完全不用动
    $stmt = $connection->query("SELECT imageposts.*, users.firstname, users.lastname 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 } ?>

额外注意 #

如果两张表存在同名字段，可以给users表的字段起别名避免覆盖，比如：

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

后续取值对应改为$row['user_firstname']即可。

内容的提问来源于stack exchange，提问作者pjk_ok