PHP: When add GROUP BY i get error Trying to get p

2019-06-11 22:21发布

问题:

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

Code:

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

Table: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;

回答1:

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 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.)

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`

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