MySQL JOIN表具有重复的列名 [英] MySQL JOIN tables with duplicate column names

问题描述

连接两个表后，我有重复的列名.如何区分名称并提取PHP中的数据.

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

服务表:

+----+-------+--------+
| 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 |
+----+-------+--------+

客户表:

+----+-------+-------+
| id | name  |  zip  |
+----+-------+-------+
| 30 | Joe   | 45698 |
| 31 | Bill  | 87848 |
| 32 | Cris  | 56879 |
| 33 | Sarah | 35411 |
| 34 | Nova  | 59874 |
| 35 | Lo    | 99874 |
+----+-------+-------+

使用此查询加入他们:

SELECT *
FROM services AS s, customers AS c
WHERE s.userID=c.id

已加入表格:

+----+-------+--------+----+-------+-------+
| 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 |
+----+-------+--------+----+-------+-------+

运行此脚本时，我想在id列中获得两个结果(例如，第一行中的1和33):

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'];
}

结果(注意两个id列都没有键.第一个是[0] => 1且没有命名键):

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 )

总有一种方法可以将键与不同的ID关联起来，而不必对具有重复名称的每一列执行以下操作:

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";

推荐答案

您可以区分两者的唯一方法是使用AS来应用别名.例如，如下所示:

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;

这样，您可以稍后根据实现使用serviceID或customerID引用列.

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

这篇关于MySQL JOIN表具有重复的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！