PHP/MySQL:在无别名的联接查询中获取多个同名列?

2022-01-10 00:00:00 join duplicates php mysql pdo

我有两张桌子.一个用于用户，一个用于帖子.users 表有以下字段:

I have two tables. One for users and one for posts. The users table has the following fields: 

id, username, password, created_at, modified_at

posts 表有以下字段:

The posts table has the following fields:

id, user_id, title, body, created_at, modified_at

当我使用如下查询时:

SELECT * FROM `users` LEFT OUTER JOIN `posts` ON users.id=posts.user_id

并使用 PDO 获取结果:

And fetch the results using PDO:

$sth = $this->$default_connection->prepare($query);
$sth->execute();
$sth->fetchAll(PDO::FETCH_ASSOC);

返回的数组会覆盖所有具有相同名称的列，例如 id、created_at、modified_at，如下所示:

The returned array overwrites all the columns with the same names like id, created_at, modified_at like this:

 Array
(
    [0] => Array
        (
            [id] => 1
            [username] => johnSmith
            [password] => 2348r7edf8s79aa0230
            [created_at] => 0000-00-00 00:00:00
            [modified_at] => 0000-00-00 00:00:00
            [user_id] => 18
            [title] => First Post
            [body] => Just testing...
        )
)

用户的 id 字段未显示，而是被帖子的 id 覆盖.created_at & 也是如此.modified_at 字段.

The id field for the user is not shown, instead overwritten by the id of the post. Same goes for the created_at & modified_at fields.

我相信我可以通过使用别名或类似的东西来解决这个问题:

I believe I can solve this problem by either using aliases or something like this:

    SELECT 
       users.id, 
       users.username, 
       users.password, 
       users.created_at, 
       users.modified_at, 
       posts.id AS postId, 
       posts.user_id, 
       posts.title, 
       posts.created_at AS postCreatedAt, 
       posts.modified_at AS postModifiedAt
   FROM `users`
       LEFT OUTER JOIN `posts` ON (users.id=postId)

或类似的东西，但我想知道是否有一种方法可以做到这一点，而无需每次我想区分两个同名的列时手动写出每一列的名称或别名?是否可以做类似的事情:

Or something similar, but I was wondering if there was a way to do this without manually writing out the name of every single column or an alias for every time I want to distinguish between two columns of the same name? Is it possible to do something like:

SELECT users.* AS User and posts.* AS Post from `users` LEFT OUTER JOIN ON (User.id=Post.user_id)

让它自动为每一列使用别名?或者有没有其他更快/更方便的方法来做到这一点?或者我可以这样做而无需自己定义任何别名吗?

And have it automatically use aliases for every column? Or is there any other faster/more convenient way to do this? Or can I do this without having to define any aliases myself?

谢谢

推荐答案

我觉得在这种情况下别名是完美的

I think alias is perfect in this case

SELECT table1.column AS column1, table2.column AS column2
FROM table1
LEFT JOIN table2
ON table1.column = table2.column

为了节省一些打字时间，请使用表格别名:

To save you some typing time, use table aliases:

SELECT t1.column AS column1, t2.column AS column2
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.column = t2.column

相关文章