在mysql中获取不同列名的简单方法

Question

Following is the query I am running in mysql which is working fine-

SELECT * 
FROM `events` e 
LEFT JOIN venues v 
ON e.venue_id = v.id 
WHERE e.artist_id = 16

In my event and artist table I have some common naming attributes like id , lat, long etc

So on getting the resultant output from the query I need to do something like this to make id, lat, long distinguish is to specify all column names with respect to its table-

SELECT e.id as e_id, v.id as v_id .........AND LOT MORE
FROM `events` e 
LEFT JOIN venues v 
ON e.venue_id = v.id 
WHERE e.artist_id = 16

So you see for only 3-4 things I need to write a lot instead of simple * ...so being from the lazy race of developers, I am looking for someway to shorten this instead of writing and specifying everything in SELECT .......

Please let me know if there is a way with which I can make it short.

Answer 1

You can automatically enumerate columns in a way similar to this:

$cols = [];

$stmt = $db->exec("SHOW COLUMNS FROM events");
foreach($stmt as $r)
    $cols []= "e.{r['Field']} as e_{r['Field']}";

// the same for the second table, then

$cols = implode(',', $cols);

"SELECT $cols FROM ..."

If you're doing many queries like this, you can cache generated column lists somewhere to avoid excessive "SHOW COLUMNS" queries (although those are very fast).