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

Question

我的数据库中有两个表：

NEWS 表，其中列：

• id - 新闻 ID
• user - 作者的用户 ID）

USERS 表，其中列：

• id 的用户 ID

-我想要执行此 SQL

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

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

Answer 1

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

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

Answer 2

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

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

Answer 3

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

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

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

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

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

示例：

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

在上面的示例中，我获得的 dup 值将来自 table3。

如果我希望 dup 成为 table1 中的值怎么办？

然后我需要这样做：

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

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

我得到了我想要的 dup 值，而不必写出每一个奇怪的列，而且我仍然可以使用所有列。 耶！

我知道 dup 的值在所有 3 个表中应该相同，但是如果 table3 没有 dup 的匹配值怎么办？ 那么在第一个示例中，dup 将是空白，这将是一个糟糕的结果。

Answer 4

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

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

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

稍后......

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

因此

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

您将仅获得 table3 中的值。 问题是 mysql 的结果集可以包含同名的列，但 php 中的关联数组不允许数组中出现重复的键。 当数据保存在关联数组中时，在 php 中，一些信息会悄然丢失......

Answer 5

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

：

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;

在 PHP 中：

$sql = "SELECT * FROM view_news";

Answer 6

您可以执行类似的操作

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

，然后 $row['news_id'] 将是新闻 id，$row['user_id'] 将是用户 id

Answer 7

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

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

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

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

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

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

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

Answer 8

使用 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
}

Answer 9

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

这样您就可以更好地自动生成查询。 另外，最好不要使用 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

Answer 10

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

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

因此，解决该问题的一个简单方法是首先执行 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);

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

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

您必须始终将 * 放在 SELECT 语句的前面，但在其后面可以包含任意数量的命名列和别名列，因为重复选择列不会出现问题。

Answer 11

有两种方法：

1. 使用别名； 在此方法中，您为各个列指定新的唯一名称 (ALIAS)，然后在 PHP 检索中使用它们。
   例如

   选择学生 ID AS FEES_LINK，学生班级 AS CLASS_LINK  
     来自students_fee_tbl  
     左连接student_class_tbl ON Students_fee_tbl.student_id = Student_class_tbl.student_id 
     

   然后用 PHP 获取结果：

   $query = $PDO_stmt->fetchAll(); 
   
     foreach($query as $q) { 
         回声 $q['FEES_LINK']; 
     } 
     

2. 使用位置位置或结果集列索引； 在此，数组位置用于引用重复的列名称。 由于它们出现在不同的位置，因此将使用的索引号始终是唯一的。
   然而，索引定位编号从0开始。例如

   选择学生 ID、学生班级  
     来自students_fee_tbl  
     左连接student_class_tbl ON Students_fee_tbl.student_id = Student_class_tbl.student_id 
     

   然后用 PHP 获取结果：

   $query = $PDO_stmt->fetchAll(); 
   
     foreach($query as $q) { 
         回声 $q[0]; 
     }