0

MySQL query not working on live server mysql version 10 MyCode:

SELECT *, MIN(tbl_room_inventory.rin_number_of_rooms) AS min_room, MAX(tbl_room_inventory.rin_temp_number_of_rooms) AS max_temp_room
FROM tbl_room_type,tbl_room_inventory
WHERE tbl_room_type.room_id=tbl_room_inventory.room_id AND
      tbl_room_type.room_status=1 AND
      tbl_room_inventory.rin_date BETWEEN 1551117600 AND 1551204000
GROUP BY tbl_room_type.room_id
HAVING MIN(tbl_room_inventory.rin_number_of_rooms)>0
ORDER BY tbl_room_type.room_tariff ASC

Same code working properly on the local server. But not working on the live server.

Here is the error of live server: enter image description here

And here is the result of mysql 5 its working

enter image description here

  • Your current query makes no sense, and won't run on any database, in general. Please include sample data which explains what you are trying to do here. – Tim Biegeleisen Feb 26 '19 at 05:31
  • Possible duplicate, [Isn't in Group By](https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by) – Ravi Gaudani Feb 26 '19 at 05:33
  • I edited and added the same query and showing the result of two servers. one of getting results other not. –  Feb 26 '19 at 05:35
  • @RaviGaudani its not duplicate question please check my full question –  Feb 26 '19 at 05:40

1 Answers1

0

You can try by disabling only_full_group_by

set global 

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Aman Kumar
  • 4,533
  • 3
  • 18
  • 40
  • @Sabbir try to disable "only_full_group_by" first by executing above query using PHPMyAdmin – Aman Kumar Feb 26 '19 at 05:46
  • 1
    I using this on php code `$sql = 'here is my sql query'; $results = mysqli_query($con, $sql);` –  Feb 26 '19 at 05:48
  • Yes you can try as you shown : please follow : https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html – Aman Kumar Feb 26 '19 at 05:50
  • 1
    I execute on phpmyadmin and its works fine. But how to add this on php page using variables; N.B: I have using this on live server cpanel. –  Feb 26 '19 at 06:03
  • @Sabbir you can set this globally: try to search "Set only_full_group_by globally" – Aman Kumar Feb 26 '19 at 06:08
  • see the error on image https://ibb.co/tPRZ4DP Access denied; you need (at least one of) the SUPER privilege(s) for this operation –  Feb 26 '19 at 06:29