mysql join相同表_MySQL JOIN表具有重复的列名

After joining two tables I have duplicate column names. How can I differentiate between the names and extract the data in PHP.

Services table:

+----+-------+--------+

| id | price | userID |

+----+-------+--------+

| 1 | 435 | 33 |

| 2 | 543 | 32 |

| 3 | 7646 | 33 |

| 4 | 7966 | 31 |

| 5 | 394 | 31 |

| 6 | 569 | 31 |

| 7 | 203 | 32 |

| 8 | 439 | 32 |

| 9 | 329 | 33 |

| 10 | 998 | 31 |

+----+-------+--------+

Customers table:

+----+-------+-------+

| id | name | zip |

+----+-------+-------+

| 30 | Joe | 45698 |

| 31 | Bill | 87848 |

| 32 | Cris | 56879 |

| 33 | Sarah | 35411 |

| 34 | Nova | 59874 |

| 35 | Lo | 99874 |

+----+-------+-------+

Join them using this query:

SELECT *

FROM services AS s, customers AS c

WHERE s.userID=c.id

Joined table:

+----+-------+--------+----+-------+-------+

| id | price | userID | id | name | zip |

+----+-------+--------+----+-------+-------+

| 1 | 435 | 33 | 33 | Sarah | 35411 |

| 2 | 543 | 32 | 32 | Cris | 56879 |

| 3 | 7646 | 33 | 33 | Sarah | 35411 |

| 4 | 7966 | 31 | 31 | Bill | 87848 |

| 5 | 394 | 31 | 31 | Bill | 87848 |

| 6 | 569 | 31 | 31 | Bill | 87848 |

| 7 | 203 | 32 | 32 | Cris | 56879 |

| 8 | 439 | 32 | 32 | Cris | 56879 |

| 9 | 329 | 33 | 33 | Sarah | 35411 |

| 10 | 998 | 31 | 31 | Bill | 87848 |

+----+-------+--------+----+-------+-------+

When I run this script, I want to get the two results in the id columns (eg. 1 and 33 in the first row):

$query = "SELECT *

FROM services AS s, customers AS c

WHERE s.userID=c.id";

$result = $link->query($query);

while($var = mysqli_fetch_array($result)) {

print_r($var);

//I would like to get them similar to this...

//$id1 = $var['id'];

//$id2 = $var['id'];

}

Result (Notice how there's no key for both id columns. The first one is [0] => 1 and has no named key):

Array ( [0] => 1 [id] => 33 [1] => 435 [price] => 435 [2] => 33 [userID] => 33 [3] => 33 [4] => Sarah [name] => Sarah [5] => 35411 [zip] => 35411 )

Is there anyway to associate a key to the different id's without doing the following for each column with a duplicate name:

$query = "SELECT *, c.id AS myNewID

FROM services AS s, customers AS c

WHERE s.userID=c.id";

解决方案

The only way you can differentiate between the two is using AS to apply an alias. For example, something like this:

SELECT s.id AS serviceID, c.id AS customerID

FROM services s

JOIN cusomters c ON c.id = s.userID;

This way you can reference the columns later on using serviceID or customerID, depending on implementation.