This query seems to work perfect on my older machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25 it seems to throw an error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pixel_perfect.users.id'; this is incompatible with sql_mode=only_full_group_by' in C:\wamp64\www
Here is what my query looks like:
$sql="SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email LIMIT 1";
$stmt=$db->prepare($sql);
$stmt->bindValue(':email', $email);
$stmt->execute();
Why am I getting this error now and what do I do to resolve it painlessly.
A change was made in version 5.7-ish where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.
You have two options:
Option 2 would look something like:
SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1
Its a little late but I just ran into this error.
This command might be useful for anyone else who runs into the same error
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
More information about this can be found at Table Plus and other links quoted above by JNevill.
Hope it helps someone else.
Easiest answer, in config/database.php make sure to set strict => true to strict => false for the mysql settings.
This will allow for less than strict queries at a cost of security for normal well-formed sql calls (still not 100% secure), but will allow for the use of other sql calls that could be in-secure if written improperly.