PHP/MySQL: Getting Multiple Columns With the Same

Question

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

Answer 1

U can add the same qualified name after ambiguous fields (use tildas or commas)

SELECT 
   users.id `users.id`,
...
   posts.id `posts.id`, 
...
FROM `users`
   LEFT OUTER JOIN `posts` ON (users.id=posts.id)

Answer 2

The only way to do this without defining aliases is to fetch rows indexed by column position instead of by column name:

$sth->fetchAll(PDO::FETCH_NUM);

You could also reduce the work to alias columns by aliasing only the columns that need it:

SELECT *, posts.created_at AS postCreatedAt, posts.updated_at AS postUpdatedAt
FROM `users` LEFT OUTER JOIN `posts` ON users.id=posts.user_id

However, it's generally considered a bad idea to use SELECT * in production code anyway. You don't typically need all the columns, so just fetch those that you do need. This reduces unnecessary waste of bandwidth as you fetch results.

Answer 3

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