PHP SQL prepared statement returns false without GROUP BY clause
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 request:
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
Problem: The prepared statement returns false for some reason, although I tried to run a query to phpMyAdmin and it works fine. If I performed a prepared statement without protection for the error, I get an error: bind_param() on boolean
.
If I add to the end of my query:
GROUP BY q.question_type
Then everything works. However, this is not what I want as it returns null for counters instead of 0 and I also don't understand how it doesn't work without GROUP BY.
source to share
Using the aggregation function without the by group is discarded and is not allowed in the latest version of mysql (5.7) Check if the version is correct and try to use the agregated function anyway on a column not mentioned in the group, e.g .:
"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 = ?)"
source to share
if you want to count by question_type you need to add GROUP BY q.question_type. Otherwise, print question_type before count (*) in your select statement. try this:
SELECT 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
source to share