|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
The initial member search works fine but a error occurs when using a new saved search. For example I saved a search for all members when I execute that saved search I get: Script: /home/sundan21/public_html/forum/admin/membersearch.php
Line: 331
SQL Error: Unknown column 't4.GROUP_ID' in 'where clause'
SQL Error: 1054
Query:
select COUNT(t1.USER_ID)
from ubbt_USERS as t1,
ubbt_USER_PROFILE as t2,
ubbt_USER_DATA as t3
where t1.USER_ID <> '1'
and t1.USER_ID = t2.USER_ID
and t1.USER_ID = t3.USER_ID
and t4.GROUP_ID IN (1) GROUP BY t1.USER_ID,
t1.USER_LOGIN_NAME,
t1.USER_MEMBERSHIP_LEVEL,
t1.USER_DISPLAY_NAME,
t2.USER_REAL_EMAIL,
t2.USER_TOTAL_POSTS,
t1.USER_REGISTRATION_IP,
t1.USER_IS_BANNED,
t1.USER_IS_UNDERAGE,
t2.USER_BIRTHDAY,
t1.USER_REGISTERED_ON,
t3.USER_LAST_POST_TIME,
t3.USER_LAST_IP,
t3.USER_LAST_VISIT_TIME,
t3.USER_LAST_POST
ORDER BY t1.USER_ID ASC
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Jun 2006
Posts: 16,370 Likes: 126
|
|
Joined: Jun 2006
Posts: 16,370 Likes: 126 |
It looks like the query isn't referencing what t4 is supposed to be; do you recall what the options for your saved search were? Did you have a group selected? Does the group exist still?
I ask as, if you've removed a group that an old search is looking for, it will not be populated in the query; I believe this would result in the error you're experiencing.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
I get the same error regardless of any criteria in the saved search only.. The first post as I said was no criteria when saved. If I just fat finger it in and run it works. Save the same criteria and run from saved It always says: Unknown column 't4.GROUP_ID Does not matter if I select a group or not. If you look in membersearch.php you will find a Difference in run or save query. The save query portion has a extra variable. One is $group and the other is $groups And it happens on old and net new saved queries. Here is one I selected just one new group I just added while opting to save I got results. But when I go back and run again from the saved I get: Script: /home/sundan21/public_html/forum/admin/membersearch.php
Line: 331
SQL Error: Unknown column 't4.GROUP_ID' in 'where clause'
SQL Error: 1054
Query:
select COUNT(t1.USER_ID)
from ubbt_USERS as t1,
ubbt_USER_PROFILE as t2,
ubbt_USER_DATA as t3
where t1.USER_ID <> '1'
and t1.USER_ID = t2.USER_ID
and t1.USER_ID = t3.USER_ID
and t4.GROUP_ID IN (16) GROUP BY t1.USER_ID,
t1.USER_LOGIN_NAME,
t1.USER_MEMBERSHIP_LEVEL,
t1.USER_DISPLAY_NAME,
t2.USER_REAL_EMAIL,
t2.USER_TOTAL_POSTS,
t1.USER_REGISTRATION_IP,
t1.USER_IS_BANNED,
t1.USER_IS_UNDERAGE,
t2.USER_BIRTHDAY,
t1.USER_REGISTERED_ON,
t3.USER_LAST_POST_TIME,
t3.USER_LAST_IP,
t3.USER_LAST_VISIT_TIME,
t3.USER_LAST_POST
ORDER BY t1.USER_ID ASC So it is the same error, always missing the group value no matter is you select any criteria or not. It When selecting a group it is correct meaning in the query "t4.GROUP_ID IN (16)" in this example. I wonder if it is the extra line feeds causing a issue
Last edited by Ruben; 02/06/2020 1:05 PM. Reason: typo
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156 |
Thanks for the bug report. Prior to UBB.threads 7.7.3, the following was hard-coded in to all searches. {$config['TABLE_PREFIX']}USER_GROUPS as t4USER_GROUPS is now only used in the query if you perform a live query with the "Advanced Search Options" and have selected a group. Your saved search also contains USER_GROUPS in its filter. This is what's triggering the error. And I am able to reproduce the error. Give me a day or so to push a fix in to the code, and post a temp fix for it to this thread. This error only affects version UBB.threads 7.7.3.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
Thanks.
Thank GOD, I thought I was going crazy.
if you did not find a bug I was out of options to check.
Have not used saved searches like forever.
Since I have enabled the subscriptions I was creating saved searches for testing. subscriptions
And I thought maybe we hosed something because there are two hands in the PIE(admins)
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156 |
EDIT 2020-02-09: THIS FIX HAS BEEN UPDATED. THE ORIGINAL FIX WORKED, BUT IT USED A POORLY CRAFTED SQL QUERY, WHICH WOULD OVERWHELM LARGE DATABASES WHEN SEARCHING WITHOUT A GROUP SELECTED. THE FOLLOWING CODE IS THE UPDATED FIX. --- For UBB.threads 7.7.3 only, in admin/membersearch.php around line 280, FIND:
$extra .= " and t4.GROUP_ID IN ($g_inlist) ";
$group = ",{$config['TABLE_PREFIX']}USER_GROUPS as t4";
$groups = "and t1.USER_ID = t4.USER_ID";
REPLACE WITH:
$extra .= "AND t4.GROUP_ID IN ($g_inlist) AND t1.USER_ID = t4.USER_ID";
around line 320, AND around line 373, FIND:
{$config['TABLE_PREFIX']}USER_DATA as t3
$group
REPLACE BOTH WITH:
{$config['TABLE_PREFIX']}USER_DATA AS t3,
{$config['TABLE_PREFIX']}USER_GROUPS AS t4
around line 325, AND around line 378, FIND:
$groups
$removedlist
$nomods
$extra
REPLACE WITH:
AND t1.USER_ID = t4.USER_ID
$removedlist
$nomods
$extra
Last edited by isaac; 02/09/2020 7:48 AM.
|
|
1 member likes this:
Gizmo |
|
|
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156 |
my previous reply has been updated.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
I used the revised version change and it works flawless.
Thank you for posting the correction now instead of releasing it on the next version.
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
1 member likes this:
isaac |
|
|
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
Oops, I may have been premature with all good. Saved searches work fine now. But initial new searches by groups have a error now.Below is a member search selecting administrators for a group Script: /home/sundan21/public_html/forum/admin/membersearch.php
Line: 329
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY t1.USER_ID,
t1.USER_LOGIN_NAME,
t1.USER_MEMBERSHIP_LEVEL,
t1.USER_DISPLAY_' at line 12
SQL Error: 1064
Query:
select COUNT(t1.USER_ID)
from ubbt_USERS as t1,
ubbt_USER_PROFILE as t2,
ubbt_USER_DATA AS t3,
ubbt_USER_GROUPS AS t4
where t1.USER_ID <> '1'
and t1.USER_ID = t2.USER_ID
and t1.USER_ID = t3.USER_ID
AND t1.USER_ID = t4.USER_ID
AND t4.GROUP_ID IN (1) AND t1.USER_ID = t4.USER_IDGROUP BY t1.USER_ID,
t1.USER_LOGIN_NAME,
t1.USER_MEMBERSHIP_LEVEL,
t1.USER_DISPLAY_NAME,
t2.USER_REAL_EMAIL,
t2.USER_TOTAL_POSTS,
t1.USER_REGISTRATION_IP,
t1.USER_IS_BANNED,
t1.USER_IS_UNDERAGE,
t2.USER_BIRTHDAY,
t1.USER_REGISTERED_ON,
t3.USER_LAST_POST_TIME,
t3.USER_LAST_IP,
t3.USER_LAST_VISIT_TIME,
t3.USER_LAST_POST
ORDER BY t1.USER_ID ASC
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156 |
if its an old saved search, have you tried to recreate it using the UPDATED code posted?
i am unable to recreate your issue with current code.
Last edited by isaac; 02/14/2020 4:50 PM.
|
|
|
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
Well I updated the file with your changes.
The saved searches work fine now.
But when I start a net new search and select a group I get the error I posted.
I will upload a fresh membersearch.php file and edit again just to make sure of no typo errors.
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156
|
|
Joined: Apr 2004
Posts: 1,982 Likes: 156 |
|
|
|
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85
|
|
Joined: Dec 2003
Posts: 6,633 Likes: 85 |
Got it thanks for the superior support
Blue Man Group
There is no such thing as stupid questions. Just stupid answers
|
|
|
|
1 members (Gizmo),
333
guests, and
146
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
