PHP：当添加GROUP BY时获取错误尝试获取非对象的属性 [英] PHP: When add GROUP BY i get error Trying to get property of non-object

问题描述

Hello我的代码工作完美，但当我添加GROUP BY我得到错误，THANKS IN ADVANCE：

Hello my code work perfect but when i add GROUP BY i get error, THANKS IN ADVANCE:

$sql_query = $connection->query("SELECT * FROM `chat` WHERE `sent_to` = '1'");-Work perfect
$sql_query = $connection->query("SELECT * FROM `chat` WHERE `sent_to` = '1' GROUP BY `sent_by`");- Not Work
Notice: Trying to get property of non-object

代码：

$sql_query = $connection->query("SELECT * FROM `chat` WHERE `sent_to` = '1' GROUP BY `sent_by`");

if($sql_query->num_rows > 0) {
    while ($fetch_data = $sql_query->fetch_array(MYSQLI_ASSOC)) {
        echo $fetch_data["text"]. "</br>";
    }
}

表：这里

CREATE TABLE IF NOT EXISTS `chat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sent_by` int(11) NOT NULL DEFAULT '0',
  `sent_to` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `text` text COLLATE utf8mb4_general_ci NOT NULL,
  `seen` enum('0', '1') NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;

推荐答案

mysqli在失败时返回false。这意味着您的按查询分组失败。这反过来暗示您正在使用 mysql 5.7 a>和

mysqli returns false on failure. Which means your group by query has failed. Which in turn implies that you are using mysql 5.7 and

MySQL 5.7.5及更高版本实现了函数依赖的检测。如果
ONLY_FULL_GROUP_BY SQL模式被启用（默认情况下），
MySQL拒绝那些选择列表HAVING条件或
ORDER BY列表引用非聚集列的查询在
中命名为GROUP BY子句，也不在功能上依赖于它们。 （在
5.7.5之前，MySQL不检测函数依赖性，默认情况下没有启用ONLY_FULL_GROUP_BY）有关
5.7之前的行为的描述，请参见MySQL 5.6参考手册。）

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

换句话说，您尝试的查询是无效的SQL。 Mysql允许你执行这样的查询，直到5.7，但不再。但您仍然可以使用 ANY_VALUE 函数或禁用 FULL_GROUP_BY 运行它。但是，仍然会使您的查询无效的SQL，如Fred指出。

In other words the query you are trying is invalid SQL. Mysql allowed you to execute such queries until 5.7 but no longer. But you can still run it with the ANY_VALUE function or disabling FULL_GROUP_BY. However that would still make your query invalid SQL as pointed out by Fred.

唯一真正的解决方案是让您更改查询。

The only real solution is for you to change your query. For example the following will work.

SELECT sent_by FROM `chat` WHERE `sent_to` = '1' GROUP BY `sent_by`

你显然试图获取文本列。因此，对于符合ANSI SQL的查询，您需要一些工作

You are apparently trying to get the text column. So for an ANSI SQL compliant query you need a bit of work

SELECT a.sent_by, a.`text` FROM chat a INNER JOIN (
    SELECT sent_by FROM `chat` WHERE `sent_to` = '1' GROUP BY `sent_by`) AS B ON a.sent_by = b.sent_by and a.sent_to = 1

这篇关于PHP：当添加GROUP BY时获取错误尝试获取非对象的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！