PHP / MySQL：在没有别名的连接查询中获取具有相同名称的多个列？ [英] PHP/MySQL: Getting Multiple Columns With the Same Name in Join Query Without Aliases?

问题描述

我有两张桌子。一个用于用户，一个用于帖子。
用户表具有以下字段：

  id，username，password，created_at，modified_at 
  

帖子表有以下字段：

  id，user_id，title，body，created_at，modified_at 
  

当我使用查询像：

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

使用PDO获取结果：

  $ sth = $ this-> $ default_connection-> prepare（$ query）; 
 $ sth-> execute（）; 
 $ sth-> fetchAll（PDO :: FETCH_ASSOC）; 
  

返回的数组覆盖所有列，名称相同，如 id，created_at ，modified_at 像这样：

 数组
（
 [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] =>只是测试... 
）
）
  

用户的 id 字段不会显示，而是被 id 覆盖该帖子。对于created_at& modified_at字段。

我相信我可以通过使用别名或类似的方式解决这个问题：

 code> 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）
  

或类似的东西，但我想知道是否有办法做到这一点，而不用手动写出每一列或别名的名称，每次我想区分同名的两列？
可以执行以下操作：

  SELECT users。* AS用户和帖子* users` LEFT OUTER JOIN ON（User.id = Post.user_id）
  

自动为每列使用别名？
还有其他更快/更方便的方法吗？
或者我可以自己定义任何别名吗？

谢谢

解决方案

我认为别名是完美的在这种情况下

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

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

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

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

The posts table has the following fields:

id, user_id, title, body, created_at, modified_at

When I use a query like:

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

And fetch the results using PDO:

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

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...
        )
)

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?

Thank you

解决方案

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

这篇关于PHP / MySQL：在没有别名的连接查询中获取具有相同名称的多个列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！