JOIN tables but prepend table name to column name to distinguish them

Question

I want to select all columns from a table with one or more joins, but I want to be able to distinguish which table the columns belong to (prepend the column names with the table name). I won't know what columns are in each table so I can't list them manually.

users:
--------------------------------
| id | name | age | contact_id |
--------------------------------
| 18 | Foo  | 21  | 1          |
| 19 | Bar  | 32  | 2          |

contacts:
----------------------------
| id | address  | phone    |
----------------------------
| 1  | 123 Main | 867-5309 |
| 2  | 987 Wall | 555-5555 |

I want to something with:

SELECT * FROM users
JOIN contacts on users.contact_id = contacts.id
WHERE users.id = 18

And get a result like:

---------------------------------------------------------------------
| users.id | users.name | ... | contacts.id | contacts.address | ...
---------------------------------------------------------------------
| 18       | Foo        | ... | 1           | 123 Main         | ...

Attempts

Thus far I've found this to get the column names for each table:

SELECT concat('contacts.', `COLUMN_NAME`) AS 'contacts'
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='myDb' 
  AND `TABLE_NAME`='contacts'

But I don't know how to put that back into my selects or if that's even the right path to go.

Success: My implementation of @Bernd Buffen's answer:

I'm using PHP's PDO class to get my results, so here's how I did it. (I'm ignoring the contacts table for this example as it's easy enough to extrapolate.)

$pdo = new PDO(...);
$stmt = $pdo->prepare(<<< EOT
SELECT group_concat(" ", COLUMN_NAME, " AS 'users.", COLUMN_NAME, "'") as 'columns'
    FROM `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE `TABLE_SCHEMA`='myDb'
        AND `TABLE_NAME`='users'
EOT);
$stmt->execute();
$columns = $stmt->fetch()['columns'];

$stmt2 = $pdo->prepare("SELECT $columns FROM users WHERE id = 18 LIMIT 1");
$stmt2->execute();
$result = $stmt2->fetch();

Answer 1

Here is a sample with prepared statement

Look at the tables

MariaDB [bernd]> SELECT * FROM users;
+----+------+------+------------+
| id | name | age  | contact_id |
+----+------+------+------------+
| 18 | Foo  |   21 | 1          |
| 19 | Bar  |   32 | 2          |
+----+------+------+------------+
2 rows in set (0.01 sec)

MariaDB [bernd]> SELECT * from contacts;
+----+----------+----------+
| id | address  | phone    |
+----+----------+----------+
|  1 | 123 Main | 867-5309 |
|  2 | 987 Wall | 555-5555 |
+----+----------+----------+
2 rows in set (0.00 sec)

MariaDB [bernd]>

Generate a fieldlist with AS

MariaDB [bernd]> SELECT GROUP_CONCAT(TABLE_NAME,'.',`COLUMN_NAME`," AS '",  
TABLE_NAME,'.', `COLUMN_NAME`,"'" SEPARATOR ', ') 
FROM `INFORMATION_SCHEMA`.`COLUMNS`  
WHERE `TABLE_SCHEMA`='bernd'    
AND `TABLE_NAME` IN ('users','contacts') INTO @allfields;
Query OK, 1 row affected (0.00 sec)

MariaDB [bernd]>

You must change the TABLE_SCHEMA and the tablenames in the IN Claus

Result is stored in @allfields

Now build your query

MariaDB [bernd]> select CONCAT('SELECT ', @allfields, ' FROM users JOIN contacts on users.contact_id = contacts.id WHERE users.id = 18') into @sql;
Query OK, 1 row affected (0.00 sec)

Prepare and execute the statement

MariaDB [bernd]> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [bernd]> EXECUTE stmt;
+-------------+------------------+----------------+----------+------------+-----------+------------------+
| contacts.id | contacts.address | contacts.phone | users.id | users.name | users.age | users.contact_id |
+-------------+------------------+----------------+----------+------------+-----------+------------------+
|           1 | 123 Main         | 867-5309       |       18 | Foo        |        21 | 1                |
+-------------+------------------+----------------+----------+------------+-----------+------------------+
1 row in set (0.00 sec)

MariaDB [bernd]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

MariaDB [bernd]>