500 Response on clients-membership-requests.php

[KILLERJC]

Type

This is a:

[X] Bug report

[] Feature request

Description of the problem

Lack of english language so my response will be short.
500 Internal Server Error when try to access clients-membership-requests.php from admin panel.

List the steps to reproduce the issue

1. Access as admin
2. Try to access it by the menu

Environment

• ProjectSend version: r1053
• php version: 7.0.27-0+deb9ui
• MySQL version: 10.1.26-MariaDB-0+deb9u1

Additional information

I commented where it execute a SQL sentence and it finally the page worked (fully load). I put some echo and var_dump to see the content that use the SQL sentence too, to try to understand why is failing.

Code commented (near line 215):

	$count_sql	= $dbh->prepare( $tq );
	$tq_row		= $count_sql->execute($params);
	$tq_row		= $count_sql->fetch();
	$count_for_pagination = $tq_row['client_id'];

Dump of tq,cq and params vars:

tq:
SELECT client_id, COUNT(group_id) as amount, GROUP_CONCAT(group_id SEPARATOR ',') AS groups FROM tbl_members_requests WHERE denied='0' GROUP BY client_id LIMIT 0 UNION ALL SELECT COUNT(DISTINCT client_id) as clients, COUNT(group_id) as total, null FROM tbl_members_requests WHERE denied='0'

cq:
SELECT client_id, COUNT(group_id) as amount, GROUP_CONCAT(group_id SEPARATOR ',') AS groups FROM tbl_members_requests WHERE denied='0' GROUP BY client_id

params:
array(0) { }

PHP error:

This is the error i get on prepare() line:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1221 Incorrect usage of UNION and LIMIT in /var/www/html/projectsend/clients-membership-requests.php:223 Stack trace: #0 /var/www/html/projectsend/clients-membership-requests.php(223): PDO->prepare('SELECT client_i...') #1 {main} thrown in /var/www/html/projectsend/clients-membership-requests.php on line 223

phpmyadmin give me the same error

I hope this info will help, i use to program microcontrollers in C, no experience in SQL or PHP, so this is something out of my league.

[KILLERJC]

Maybe is not the correct solution but.... On MariaDB documentation:

ORDER BY and LIMIT
Individual SELECTs can contain their own ORDER BY and LIMIT clauses. In this case, the individual queries need to be wrapped between parentheses.

Adding parentheses on the first SELECT of the query solve the issue.

(SELECT client_id, COUNT(group_id) as amount, GROUP_CONCAT(group_id SEPARATOR ',') AS groups FROM tbl_members_requests WHERE denied='0' GROUP BY client_id LIMIT 0) UNION ALL SELECT COUNT(DISTINCT client_id) as clients, COUNT(group_id) as total, null FROM tbl_members_requests WHERE denied='0'

And now show the new request fine.

[ignacionelson]

This was solved on #571 by the user ofrzeta :)
Thank you for the report!!

[KILLERJC]

Sorry my bad, i searched on open issues and couldn't find anything similar. And im new to git, so i will look also on the code next time, to verify if you or somebody else solved it before.

Thank for your work!