How can you access two identically-named columns in a MySQL LEFT JOIN query?

Question

I have two tables.

table_x:

id    INT(11)
tag   INT(11)

table_tags:

id    INT(11)
name  VARCHAR(255)

Then I use PHP to perform the following query:

SELECT * FROM table_x LEFT JOIN table_tags ON table_x.tag = table_tags.id

The only problem is: how do I access table_x.id and table_tags.id in the results?

Here is the PHP code:

$query = "SELECT * FROM table_x LEFT JOIN table_tags ON table_x.tag = table_tags.id";
$results = mysql_query($query);

while($row = mysql_fetch_array($results))
{
    // how do I now access table_x.id and table_tags.id ???
}

Answer 1

You name the columns in your column list:

SELECT 
  table_x.id AS x_id, 
  table_tags.id AS tag_id,
  other, columns, here
FROM table_x LEFT JOIN table_tags 
ON table_x.tag = table_tags.id

In general, it's considered good form to name the columns you want returned explicitly rather than relying on * (which may cause the actual number, order, and names of columns to change if the underlying table structure changes). At the same time, you can alias the column with new names in the result set for ease of use (the AS keyword is optional in most SQL dialects).

Update: OP states in a comment that he must use "*". Although this is not recommended, you can do this:

SELECT 
  table_x.id AS x_id, 
  table_tags.id AS tag_id,
  *
FROM table_x LEFT JOIN table_tags 
ON table_x.tag = table_tags.id

which combines both the named columns you want and the (bad) "*" technique. Your columns will be included in the result set twice, in the first two column positions and in their regular positions in the column list. The values will be the same.

Answer 2

Use it like this

$query = "SELECT (table_x.id) AS xid, (table_tags.id) AS tid FROM table_x LEFT JOIN table_tags ON table_x.tag = table_tags.id";

$results = mysql_query($query);

while($row = mysql_fetch_array($results)) {

**//fetch id of table_x with xid**

 **echo "table_x id: ".$row['xid'];**

//fetch id of table_tags with tid

 **echo "table_tags id: ".$row['tid'];**

}