PHP/MySQL: Getting Multiple Columns With the Same Name in Join Query Without Aliases?
duplicates, join, mysql, pdo, php
Solution
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
Problem
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