I have the following query:
$query = <<<SQL
SELECT
year,
count(*) AS `counter`,
GROUP_CONCAT(team) AS `team_list`
FROM
team_list
WHERE year IS NOT NULL
SQL;
if (!empty($sql)) { //$sql is an array of SQL WHERE statements "a IN (a,b,c)"
$query .= ' AND ' . implode(' AND ', $sql);
}
$query .= 'GROUP BY year ORDER BY year';
/////////////////////////////
//EXECUTING THE QUERIES
/////////////////////////////
//Filter count to know how many 's' variable have to be bind to the prepared statement
$filterCount = count($teams) + count($countries) + count($years) + count($rankings); //These are my ajax elements that are also used in the $sql variable
//Data query
$queryYears = $connection->prepare($query);
$queryYears->bind_param(str_repeat('s', $filterCount), ...$teams, ...$countries, ...$years, ...$rankings);
$queryYears-> execute();
This all works very fine!
However, once I try to enter SET SESSION group_concat_max_len = 1000000;
at the beginning of my query statement I get the following error:
Fatal error: Uncaught Error: Call to a member function bind_param() on boolean
I understand that something is now wrong with my query, but when copy-pasting it to my DBMS the query can be executed without a problem.
What am I doing wrong here?
Your problem is that you are trying to execute two queries at once, and mysqli::prepare
doesn't support that, so it fails and returns false. Instead, run the variable set as a separate query first:
$connection->query("SET SESSION group_concat_max_len = 1000000;") or die($connection->error);
$queryYears = $connection->prepare($query) or die($connection->error);
// etc.
Note that you should be checking the status of your calls, as I have done in the code above.