php - Error Query in old MySQL

949

I am create query to Count table.

$Month = $_POST['Month'];
$query = "SELECT ANY_VALUE(AD) AS ad,
              COUNT(*) AS `Click`,
              DATE_FORMAT(ANY_VALUE(TIME), '%Y-%m')  AS `Month` 
          FROM `clicks` 
          WHERE DATE_FORMAT(TIME, '%Y-%m')='$Month' 
          GROUP BY ad ASC ";

It's work in MySql version is 5.7.10 (Localhost). Then Upload on webserver, the server use MySql version5.1.66-cll, but i am find ERROR:

"Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION server_dbname.ANY_VALUE does not exist' in...."

If I removeANY_VALUE, can work in MySQL 5.1.66, but didn't work in MySQL 5.7.10 How to make Query work in Both Version ( New version and old version )?

Thank's a lot for answer. ( I am sory, My English isn't good )

640

Answer

Solution:

TheANY_VALUE function was added in MySQL 5.7, to allow you to override the behavior of theONLY_FULL_GROUP_BY SQL mode for individual queries. The default for this mode was changed to enabled in 5.7, so this allows you to modify old queries that don't list all the columns.

If you removeANY_VALUE and want it to work in 5.7, you have two choices:

Disable the mode, so it will act like earlier versions.

Change the query so that all the non-aggregated columns are listed in theGROUP BY clause.

    SELECT ad,
        COUNT(*) AS `Click`,
        DATE_FORMAT(TIME, '%Y-%m')  AS `Month` 
    FROM `clicks` 
    WHERE DATE_FORMAT(TIME, '%Y-%m')='$Month' 
    GROUP BY ad ASC, Month

125

Answer

Solution:

TheANY_VALUE function was only added in MySQL 5.7 and therefore will not work if you are using a version that is any lower than MySQL 5.7. As @mkaatman in the comments stated, update your MySQL version to solve this issue.

Answer

Solution:

Try below query as it should work in both versions without disabling ONLY_FULL_GROUP_BY-

SELECT ad,
              COUNT(*) AS 'Click',
              DATE_FORMAT(max(TIME), '%Y-%m')  AS 'Month' 
          FROM `clicks` 
          WHERE DATE_FORMAT(TIME, '%Y-%m')='$Month' 
          GROUP BY ad ASC

People are also looking for solutions to the problem: php - Database help needed i'm stumped

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question