php 检索结果时如何解决不明确的列名？

How to resolve ambiguous column names when retrieving results?

提问by Dan Burzo

I have two tables in my database:

我的数据库中有两个表：

NEWS table with columns:

带有列的 NEWS 表：

• id- the news id
• user- the user id of the author)

• id- 新闻编号
• user- 作者的用户名）

USERS table with columns:

带有列的 USERS 表：

• id- the user id

• id- 用户 ID

I want to execute this SQL:

我想执行这个 SQL：

SELECT * FROM news JOIN users ON news.user = user.id 

When I get the results in PHP I would like to get associative array and get column names by $row['column-name']. How do I get the news ID and the user ID, having the same column name?

当我在 PHP 中获得结果时，我想获得关联数组并通过$row['column-name']. 如何获取具有相同列名的新闻 ID 和用户 ID？

回答by CMS

You can set aliases for the columns that you are selecting:

您可以为您选择的列设置别名：

$query = 'SELECT news.id AS newsId, user.id AS userId, [OTHER FIELDS HERE] FROM news JOIN users ON news.user = user.id'

回答by Greg

You can either use the numerical indices ($row[0]) or better, use ASin the MySQL:

您可以使用数字索引 ( $row[0]) 或更好的方法，AS在 MySQL 中使用：

SELECT *, user.id AS user_id FROM ...

SELECT *, user.id AS user_id FROM ...

回答by Eab

I just figured this out. It's probably a bad practice but it worked for me in this case.

我只是想通了这一点。这可能是一个不好的做法，但在这种情况下它对我有用。

I am one of the lazy people who doesn't want to alias or write out every column name with a table prefix.

我是懒惰的人之一，不想使用表前缀别名或写出每个列名。

You can select all of the columns from a specific table by using table_name.*in your select statement.

您可以通过table_name.*在 select 语句中使用来选择特定表中的所有列。

When you have duplicated column names, mysql will overwrite from first to last. The data from the first duplicated column name will be overwritten when it encounters that column name again. So the duplicate column name that comes in last wins.

当您有重复的列名时，mysql 会从头到尾覆盖。当再次遇到该列名时，第一个重复列名中的数据将被覆盖。所以最后出现的重复列名获胜。

If I am joining 3 tables, each containing a duplicated column name, the order of the tables in the select statement will determine what data I am getting for the duplicate column.

如果我要加入 3 个表，每个表都包含一个重复的列名，则 select 语句中表的顺序将决定我为重复列获取哪些数据。

Example:

例子：

SELECT table1.* , table2.* , table3.* FROM table1 LEFT JOIN table2 ON table1.dup = table2.dup LEFT JOIN table3 ON table2.dup = table3.dup;

In the example above, the value of dupI get will be from table3.

在上面的示例中，dupI get的值将来自table3.

What if I want dupto be the value from table1?

如果我想dup成为 的价值table1怎么办？

Then I need to do this:

然后我需要这样做：

SELECT table3.* , table2.* , table1.* FROM table1 LEFT JOIN table2 ON table1.dup = table2.dup LEFT JOIN table3 ON table2.dup = table3.dup;

Now, table1comes last, so the value of dupwill be the value from table1.

现在，排table1在最后，因此 的值dup将是 table1 中的值。

I got the value I wanted for dupwithout having to write out every single freaking column and I still get all of the columns to work with. Yay!

我得到了我想要的价值，dup而不必写出每一个该死的专栏，而且我仍然可以使用所有专栏。好极了！

I know the value of dupshould be the same in all 3 tables, but what if table3doesn't have a matching value for dup? Then dupwould be blank in the first example, and that would be a bummer.

我知道dup3 个表中的值应该相同，但是如果table3没有匹配的值dup怎么办？然后dup在第一个示例中将是空白的，这将是一个无赖。

回答by Dirk

Another tip: if you want to have cleaner PHP code, you can create a VIEW in the database, e.g.

另一个提示：如果你想拥有更干净的 PHP 代码，你可以在数据库中创建一个 VIEW，例如

For example:

例如：

CREATE VIEW view_news AS
SELECT
  news.id news_id,
  user.id user_id,
  user.name user_name,
  [ OTHER FIELDS ]
FROM news, users
WHERE news.user_id = user.id;

In PHP:

在 PHP 中：

$sql = "SELECT * FROM view_news";

回答by GoTo

@Jason. You are correct except that php is the culprit and not mysql. If you put your JOINin Mysql Workbench you will get three columns with the exact same name (one for each table) but not with the same data (some will be nullif that table has no match for the JOIN).

@杰森。你是对的，除了 php 是罪魁祸首，而不是 mysql。如果您将您的数据JOIN放入 Mysql Workbench，您将获得三列名称完全相同（每个表一个）但数据不同（null如果该表与 不匹配，则有些列JOIN）。

In php if you use MYSQL_NUMin mysql_fetch_array()then you will get all columns. The problem is when you use mysql_fetch_array()with MYSQL_ASSOC. Then, inside that function, php is building the return value like so:

在 php 中，如果您使用MYSQL_NUMinmysql_fetch_array()那么您将获得所有列。问题是当您使用mysql_fetch_array()with 时MYSQL_ASSOC。然后，在该函数中，php 正在构建返回值，如下所示：

$row['dup'] = [value from table1]

and later on...

后来……

$row['dup'] = [value from table2]

...

...

$row['dup'] = [value from table3]

So you will get only the value from table3. The problem is that a result set from mysql can contain columns with the same name but associative arrays in php don't allow duplicate keys in arrays. When the data is saved in associative arrays, in php, some information is silently lost...

因此，您将仅从 table3 中获得值。问题是来自 mysql 的结果集可以包含具有相同名称的列，但 php 中的关联数组不允许数组中有重复的键。当数据保存在关联数组中时，在php中，一些信息被默默地丢失了......

回答by Paolo Bergantino

You can do something like

你可以做类似的事情

SELECT news.id as news_id, user.id as user_id ....

And then $row['news_id']will be the news id and $row['user_id']will be the user id

然后$row['news_id']将是新闻 ID 和$row['user_id']用户 ID

回答by haknick

I had this same issue with dynamic tables. (Tables that are assumed to have an id to be able to join but without any assumption for the rest of the fields.) In this case you don't know the aliases before hand.

我对动态表有同样的问题。（假设表具有能够加入的 id 但对其余字段没有任何假设。）在这种情况下，您事先不知道别名。

In such cases you can first get the table column names for all dynamic tables:

在这种情况下，您可以首先获取所有动态表的表列名称：

$tblFields = array_keys($zendDbInstance->describeTable($tableName));

Where $zendDbInstance is an instance of Zend_Db or you can use one of the functions here to not rely on Zend php pdo: get the columns name of a table

其中 $zendDbInstance 是 Zend_Db 的一个实例，或者您可以使用此处的函数之一来不依赖 Zend php pdo：获取表的列名

Then for all dynamic tables you can get the aliases and use $tableName.* for the ones you don't need aliases:

然后对于所有动态表，您可以获得别名并使用 $tableName.* 对于不需要别名的表：

$aliases = "";
foreach($tblKeys as $field)
    $aliases .= $tableName . '.' . $field . ' AS ' . $tableName . '_' . $field . ',' ;
$aliases = trim($aliases, ',');

You can wrap this whole process up into one generic function and just have cleaner code or get more lazy if you wish :)

您可以将整个过程包装到一个通用函数中，如果您愿意，只需拥有更简洁的代码或变得更懒惰:)

回答by SchizoDuckie

If you don't feel like aliassing you can also just prefix the tablenames.

如果您不想使用别名，您也可以在表名前加上前缀。

This way you can better automate generation of your queries. Also, it's a best-practice to not use select * (it is obviously slower than just selecting the fields you needFurthermore, only explicitly name the fields you want to have.

通过这种方式，您可以更好地自动生成查询。此外，最好的做法是不使用 select * （这显然比只选择您需要的字段要慢 此外，只明确命名您想要的字段。

SELECT
    news.id, news.title, news.author, news.posted, 
    users.id, users.name, users.registered 
FROM 
    news 
LEFT JOIN 
    users 
ON 
    news.user = user.id

回答by GAG and Spice

Here's an answer to the above, that's both simple and also works with JSON results being returned. While the SQL query will automatically prefix table names to each instance of identical field names when you use SELECT *, JSON encoding of the result to send back to the webpage, ignores the values of those fields with a duplicate name and instead returns a NULL value.

这是上述问题的答案，它既简单又适用于返回的 JSON 结果。虽然当您使用 SELECT * 时，SQL 查询会自动为相同字段名称的每个实例添加表名前缀，但将结果的 JSON 编码发送回网页时，会忽略具有重复名称的那些字段的值，而是返回 NULL 值.

Precisely what it does is include the first instance of the duplicated field name, but makes its value NULL. And the second instance of the field name (in the other table) is omitted entirely, both field name and value. But, when you test the query directly on the database (such as using Navicat), all fields are returned in the result set. It's only when you next do JSON encoding of that result, do they have NULL values and subsequent duplicate names are omitted entirely.

确切地说，它所做的是包含重复字段名称的第一个实例，但使其值为 NULL。并且字段名称的第二个实例（在另一个表中）被完全省略，包括字段名称和值。但是，当您直接在数据库上测试查询时（例如使用 Navicat），结果集中会返回所有字段。只有当您接下来对该结果进行 JSON 编码时，它们是否具有 NULL 值并且随后的重复名称被完全省略。

So, an easy way to fix that problem is to first do a SELECT *, then follow with aliased fields for the duplicates. Here's an example, where both tables have identically named site_name fields.

因此，解决该问题的一种简单方法是先执行 SELECT *，然后对重复项使用别名字段。这是一个示例，其中两个表都具有相同命名的 site_name 字段。

SELECT *, w.site_name AS wo_site_name FROM ws_work_orders w JOIN ws_inspections i WHERE w.hma_num NOT IN(SELECT hma_number FROM ws_inspections) ORDER BY CAST(w.hma_num AS UNSIGNED);

Now in the decoded JSON, you can use the field wo_site_name and it has a value. In this case, site names have special characters such as apostrophes and single quotes, hence the encoding when originally saving, and the decoding when using the result from the database.

现在在解码的 JSON 中，您可以使用字段 wo_site_name 并且它有一个值。在这种情况下，站点名称具有特殊字符，例如撇号和单引号，因此在最初保存时进行编码，在使用数据库结果时进行解码。

...decHTMLifEnc(decodeURIComponent( jsArrInspections[x]["wo_site_name"]))

You must always put the * first in the SELECT statement, but after it you can include as many named and aliased columns as you want, as repeatedly selecting a column causes no problem.

您必须始终将 * 放在 SELECT 语句中，但在它之后您可以根据需要包含任意数量的命名和别名列，因为重复选择列不会导致任何问题。

回答by Your Common Sense

When using PDO for a database interacttion, one can use the PDO::FETCH_NAMEDfetch mode that could help to resolve the issue:

当使用 PDO 进行数据库交互时，可以使用PDO::FETCH_NAMED获取模式来帮助解决问题：

$sql = "SELECT * FROM news JOIN users ON news.user = user.id";
$data = $pdo->query($sql)->fetchAll(PDO::FETCH_NAMED);
foreach ($data as $row) {
    echo $row['column-name'][0]; // from the news table
    echo $row['column-name'][1]; // from the users table
    echo $row['other-column']; // any unique column name
}