PHP/MySQL: Getting Multiple Columns With the Same Name in Join Query Without Aliases? -

i have 2 tables. 1 users , 1 posts. users table has following fields:

id, username, password, created_at, modified_at 

the posts table has following fields:

id, user_id, title, body, created_at, modified_at 

when use query like:

select * `users` left outer join `posts` on users.id=posts.user_id 

and fetch results using pdo:

$sth = $this->$default_connection->prepare($query); $sth->execute(); $sth->fetchall(pdo::fetch_assoc); 

the returned array overwrites columns same names id, created_at, modified_at 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] => testing...         ) ) 

the id field user not shown, instead overwritten id of post. same goes created_at & modified_at fields.

i believe can solve problem either using aliases or this:

    select         users.id,         users.username,         users.password,         users.created_at,         users.modified_at,         posts.id postid,         posts.user_id,         posts.title,         posts.created_at postcreatedat,         posts.modified_at postmodifiedat    `users`        left outer join `posts` on (users.id=postid) 

or similar, wondering if there way without manually writing out name of every single column or alias every time want distinguish between 2 columns of same name? possible like:

select users.* user , posts.* post `users` left outer join on (user.id=post.user_id) 

and have automatically use aliases every column? or there other faster/more convenient way this? or can without having define aliases myself?

thank you

i think alias perfect in case

select table1.column column1, table2.column column2 table1 left join table2 on table1.column = table2.column 

to save typing time, use table aliases:

select t1.column column1, t2.column column2 table1 t1 left join table2 t2 on t1.column = t2.column