Francisco José Segura Noya asked 6 years ago
It gives an error when exporting to excel when I include the GROUP BY clause in the SELECT.
Without the GROUP BY clause, it is normally exported
……..
$grid[“sortname”] = ‘surname ASC, name’;
……….
SELECT
COUNT(*),
residents.resident_id ,
residents.name,
residents.surname,
residents.asesor,
residents.n_ropa,
residents.tarjeta_acceso,
residents.codigo_impresora,
DATE_FORMAT(residents.date_of_birth, ‘%d/%m/%y’) as aniversario,
residents.email,
residents.mobile,
residents.course,
residents.subject,
residents.city,
countries.country,
YEAR( residents.arrival ) as llegada,
YEAR(CURDATE()) as hoy,
bookings.resident_id as resident_id_b,
YEAR(CURDATE()) – YEAR( residents.arrival ) as anos,
rooms.roomFROM residents
LEFT JOIN bookings ON bookings.resident_id=residents.resident_id
LEFT JOIN rooms ON bookings.room_id=rooms.room_id
LEFT JOIN countries ON residents.country_id=countries.country_idWHERE name!= ”
AND bookings.arrival > ‘$fecha_inicio’ AND bookings.planned_departure >= ‘$today’
AND DATEDIFF(bookings.departure,bookings.arrival) > 28 OR residents.estancia = ‘larga’GROUP BY residents.resident_id
Thank you
Francisco José Segura Noya answered 6 years ago
link to the complete code: codeshare.io/5PQRqY
Can you send query error details as well? I tested similar group by query and unable to generate this issue.
Also try testing the printed query of error message in sql ide.
Francisco José Segura Noya answered 6 years ago
Hello Abu,
The query error is:
Couldn’t execute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘HAVING ORDER BY surname ASC, name asc’ at line 1 – SELECT COUNT(*), residents.resident_id , residents.name, residents.surname, residents.asesor, residents.n_ropa, residents.tarjeta_acceso, residents.codigo_impresora, DATE_FORMAT(residents.date_of_birth, ‘%d/%m/%y’) as aniversario, residents.email, residents.mobile, residents.course, residents.subject, residents.city, countries.country, YEAR( residents.arrival ) as llegada, YEAR(CURDATE()) as hoy, bookings.arrival, YEAR(CURDATE()) – YEAR( residents.arrival ) as anos, rooms.room FROM residents INNER JOIN bookings ON residents.resident_id = bookings.resident_id LEFT JOIN rooms ON bookings.room_id=rooms.room_id LEFT JOIN countries ON residents.country_id=countries.country_id WHERE name!= ” AND bookings.arrival > ‘2018-08-01 00:00:00’ AND bookings.planned_departure >= ‘2018-10-09’ AND DATEDIFF(bookings.departure,bookings.arrival) > 28 OR residents.estancia = ‘larga’ GROUP BY residents.resident_id HAVING COUNT(*) > 0 HAVING ORDER BY surname ASC, name asc
Thanks
Francisco José Segura Noya answered 6 years ago
sorry, the correct one is: Couldn\’t execute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \’ORDER BY surname ASC, name asc\’ at line 1 – SELECT residents.resident_id , residents.name, residents.surname, residents.asesor, residents.n_ropa, residents.tarjeta_acceso, residents.codigo_impresora, DATE_FORMAT(residents.date_of_birth, \’%d/%m/%y\’) as aniversario, residents.email, residents.mobile, residents.course, residents.subject, residents.city, countries.country, YEAR( residents.arrival ) as llegada, YEAR(CURDATE()) as hoy, bookings.arrival, YEAR(CURDATE()) – YEAR( residents.arrival ) as anos, rooms.room FROM residents INNER JOIN bookings ON residents.resident_id = bookings.resident_id LEFT JOIN rooms ON bookings.room_id=rooms.room_id LEFT JOIN countries ON residents.country_id=countries.country_id WHERE name!= \’\’ AND bookings.arrival > \’2018-08-01 00:00:00\’ AND bookings.planned_departure >= \’2018-10-09\’ AND DATEDIFF(bookings.departure,bookings.arrival) > 28 OR residents.estancia = \’larga\’ GROUP BY residents.resident_id HAVING ORDER BY surname ASC, name asc
Abu Ghufran Staff answered 6 years ago
I’ve emailed you latest build. Please re-check updating it.
If issue persist, you can send me related database schema to generate this grid here to verify the issue.
Francisco José Segura Noya answered 6 years ago
Abu, I have Override the jqgrid_dist.php emailed in lib / inc folder. The error that appears now when exporting to excel in all grids, even those that do not have GROUP BY, is: This page does not work The localhost page can not process this request now. HTTP ERROR 500 I\’ve emailed you the tables and files that are involved in the process. Thank you
link to the complete code: codeshare.io/5PQRqY