error exporting to excel when I include the GROUP BY clause in the SELECT.

Questionserror exporting to excel when I include the GROUP BY clause in the SELECT.
Francisco José Segura Noya asked 5 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.room

FROM 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_id

WHERE 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 replied 5 years ago

link to the complete code: codeshare.io/5PQRqY

5 Answers
Francisco José Segura Noya answered 5 years ago

link to the complete code: codeshare.io/5PQRqY

Abu Ghufran Staff replied 5 years ago

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 5 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 5 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 5 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.

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Francisco José Segura Noya answered 5 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

Your Answer

8 + 12 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?