PHP:当添加 GROUP BY 时出现错误 Trying to get property of non-object

Question

您好，我的代码运行完美，但是当我添加 GROUP BY 时出现错误，在此先感谢您:

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

表:HERE

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;

Answer 1

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

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 来运行它。但是，正如 Fred 指出的那样，这仍然会使您的查询无效 SQL。

唯一真正的解决方案是让您更改查询。例如，以下将起作用。

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

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

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