准备好的PHP SQL语句在没有GROUP BY子句的情况下返回false

Question

I have a prepared statement:

if ( $statement = $this->connection->prepare("SELECT question_type, count(*) AS `count` FROM (SELECT question.*, left(question_body, locate('between', question_body)-2) AS question_type FROM question) q WHERE (q.question_type = ? AND q.response_value_id = ?)") ) {
    $statement->bind_param("si", $question_type, $response_value_id);
    $statement->execute();
    return $statement->get_result()->fetch_assoc();
} else {
    var_dump($this->db->error);
}

Here is the query:

SELECT question_type, count(*) AS `count` FROM 
(SELECT question.*, left(question_body, locate('between', question_body) - 2) 
AS question_type FROM question) 
q WHERE q.question_type = 'Did you return home' AND q.response_value_id = 4 

The problem: For some reason the prepared statement is returning false although I have tried to run the query on phpMyAdmin and it works perfectly. If I execute the prepared statement without a guard for an error, I get the error: bind_param() on boolean.

If I add to the end of my query:

GROUP BY q.question_type

Then everything works. However, that is not what I want since it returns null for the counts instead of 0, and I also do not understand how it is not working without the GROUP BY.

Answer 1

The use of aggregation function without group by is depracted and in the most recent version of mysql (5.7) is not allowed Check for you right versione and anyway try use ever agregated function for column not mentioned in group by eg :

  "SELECT question_type, count(*) AS `count` 
    FROM (
      SELECT question.*, left(question_body, locate('between', question_body)-2) AS question_type 
      FROM question ) q 
  WHERE q.question_type = ? AND q.response_value_id = ?
  GROUP BY question_type)"

or

  "SELECT min(question_type), count(*) AS `count` 
    FROM (
      SELECT question.*, left(question_body, locate('between', question_body)-2) AS question_type 
      FROM question ) q 
  WHERE (q.question_type = ? AND q.response_value_id = ?)"