Groups Issue

[keukapeteb]

Hi there,

phpScheduleIt has been up and working okay for a few days now, but someone just mentioned that when they try to configure a group, they get this error message:

There was an error executing your query:
DB Error: no such table SELECT g.groupid, g.group_name, u.fname, u.lname, cnt.user_count FROM ets1_groups g LEFT JOIN ets1_user_groups ug ON g.groupid = ug.groupid AND ug.is_admin = 1 LEFT JOIN ets1_login u ON ug.memberid = u.memberid LEFT JOIN ( SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM user_groups ug GROUP BY ug.groupid ) cnt ON cnt.groupid = g.groupid ORDER BY group_name LIMIT 0, 25 [nativecode=1146 ** Table 'edt_schedule.user_groups' doesn't exist]

At first glance, it looks to me that it's not adding the table prefix to the FROM table. Does anyone know where I'd be able to go in and repair this?

[Nick]

The bug fix for this is to change lines 862-868 to:

Code:

$query = 'SELECT g.groupid, g.group_name, u.fname, u.lname, cnt.user_count FROM '
. $this->get_table(TBL_GROUPS) . ' g LEFT JOIN '
. $this->get_table(TBL_USER_GROUPS) . ' ug ON g.groupid = ug.groupid AND ug.is_admin = 1 LEFT JOIN '
. $this->get_table(TBL_LOGIN) . ' u ON ug.memberid = u.memberid LEFT JOIN ( SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM '
. $this->get_table(TBL_USER_GROUPS) . ' ug GROUP BY ug.groupid) cnt ON cnt.groupid = g.groupid ORDER BY group_name';

[keukapeteb]

The bug fix for this is to change lines 862-868 to:

Code:

$query = 'SELECT g.groupid, g.group_name, u.fname, u.lname, cnt.user_count FROM '
. $this->get_table(TBL_GROUPS) . ' g LEFT JOIN '
. $this->get_table(TBL_USER_GROUPS) . ' ug ON g.groupid = ug.groupid AND ug.is_admin = 1 LEFT JOIN '
. $this->get_table(TBL_LOGIN) . ' u ON ug.memberid = u.memberid LEFT JOIN ( SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM '
. $this->get_table(TBL_USER_GROUPS) . ' ug GROUP BY ug.groupid) cnt ON cnt.groupid = g.groupid ORDER BY group_name';

Hi Nick -- Thanks for the fix.

My only question now is ... lines 862-868 in which file?  

[Nick]

My apologies, I keep for getting file names lately.  Anyway, its in lib/db/AdminDB.class.php.

[baldomax]

The bug fix for this is to change lines 862-868 to:

Code:

$query = 'SELECT g.groupid, g.group_name, u.fname, u.lname, cnt.user_count FROM '
. $this->get_table(TBL_GROUPS) . ' g LEFT JOIN '
. $this->get_table(TBL_USER_GROUPS) . ' ug ON g.groupid = ug.groupid AND ug.is_admin = 1 LEFT JOIN '
. $this->get_table(TBL_LOGIN) . ' u ON ug.memberid = u.memberid LEFT JOIN ( SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM '
. $this->get_table(TBL_USER_GROUPS) . ' ug GROUP BY ug.groupid) cnt ON cnt.groupid = g.groupid ORDER BY group_name';

Hi Nick -- Thanks for the fix.

My only question now is ... lines 862-868 in which file?  

I've installed this fix but.....

C'è stato un errore eseguendo questa interrogazione:
DB Error: syntax error SELECT g.groupid, g.group_name, u.fname, u.lname, cnt.user_count FROM groups g LEFT JOIN user_groups ug ON g.groupid = ug.groupid AND ug.is_admin = 1 LEFT JOIN login u ON ug.memberid = u.memberid LEFT JOIN ( SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM user_groups ug GROUP BY ug.groupid) cnt ON cnt.groupid = g.groupid ORDER BY group_name LIMIT 0, 25 [nativecode=1064 ** 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 'SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM user_g]

 :cry:

[Nick]

What version of MySQL is this?  It may not support sub-selects.

[alattke]

Hi Nick,

I get:

SELECT g.groupid, g.group_name, u.fname, u.lname, cnt.user_count
FROM groups g LEFT JOIN user_groups ug ON g.groupid = ug.groupid
 AND ug.is_admin = 1
LEFT JOIN login u ON ug.memberid = u.memberid
LEFT JOIN ( SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM user_groups ug GROUP BY ug.groupid ) cnt ON cnt.groupid = g.groupid
ORDER BY group_name LIMIT 0, 25
[nativecode=1064 ** 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 '
SELECT ug.groupid, COUNT(ug.memberid) as user_count FROM user_g]

I am using MySQL server version 4.0.27 and Client version 4.1.10. I don't think that MySQL 4 handles sub-queries... Can you help me re-write this query so it works with MySQL 4.0 ?

BTW - You have a fantastic piece of code here... I love it!

Thank you for this awesome contribution to the open source community!

[Nick]

If you dont care about getting the count of users in the group, then all you would need is this in lines 862-868 in lib/db/AdminDB.class.php:

$query = 'SELECT g.groupid, g.group_name, u.fname, u.lname, 0 as user_count FROM '
. $this->get_table(TBL_GROUPS) . ' g LEFT JOIN '
. $this->get_table(TBL_USER_GROUPS) . ' ug ON g.groupid = ug.groupid AND ug.is_admin = 1 LEFT JOIN '
. $this->get_table(TBL_LOGIN) . ' u ON ug.memberid = u.memberid ORDER BY group_name';

[alattke]

Thanks, Nick!

That fix worked great.

[baldomax]

Thanks, Nick!

That fix worked great.

Also for me! Thanks a lot Nick!