Previous Thread
Next Thread
Print Thread
Hop To
CP Saved member Searchs error V7.7.3
#263456 02/06/2020 11:38 AM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
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:
Code
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
Re: CP Saved member Searchs error V7.7.3
Ruben #263457 02/06/2020 1:17 PM
Joined: Jun 2006
Posts: 15,890
Likes: 7
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 15,890
Likes: 7
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.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: CP Saved member Searchs error V7.7.3
Ruben #263458 02/06/2020 1:48 PM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
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:
Code
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 2:05 PM. Reason: typo

Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: CP Saved member Searchs error V7.7.3
Ruben #263459 02/06/2020 3:54 PM
Joined: Apr 2004
Posts: 1,651
Likes: 33
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,651
Likes: 33
Thanks for the bug report.

Prior to UBB.threads 7.7.3, the following was hard-coded in to all searches.
Code
{$config['TABLE_PREFIX']}USER_GROUPS as t4
USER_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.


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: CP Saved member Searchs error V7.7.3
Ruben #263460 02/06/2020 4:26 PM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
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
Re: CP Saved member Searchs error V7.7.3
Ruben #263478 02/07/2020 12:21 PM
Joined: Apr 2004
Posts: 1,651
Likes: 33
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,651
Likes: 33
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:
Code
		$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:
Code
		$extra .= "AND t4.GROUP_ID IN ($g_inlist) AND t1.USER_ID = t4.USER_ID";


around line 320, AND around line 373,
FIND:
Code
	{$config['TABLE_PREFIX']}USER_DATA as t3
	$group
REPLACE BOTH WITH:
Code
	{$config['TABLE_PREFIX']}USER_DATA AS t3,
	{$config['TABLE_PREFIX']}USER_GROUPS AS t4


around line 325, AND around line 378,
FIND:
Code
	$groups
	$removedlist
	$nomods
	$extra
REPLACE WITH:
Code
AND	t1.USER_ID = t4.USER_ID
	$removedlist
	$nomods
	$extra

Last edited by isaac; 02/09/2020 8:48 AM.

Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
1 member likes this: Gizmo
Re: CP Saved member Searchs error V7.7.3
Ruben #263480 02/09/2020 8:41 AM
Joined: Apr 2004
Posts: 1,651
Likes: 33
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,651
Likes: 33
my previous reply has been updated.


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: CP Saved member Searchs error V7.7.3
Ruben #263481 02/11/2020 2:09 PM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
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
Re: CP Saved member Searchs error V7.7.3
Ruben #263507 02/14/2020 5:46 PM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
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
Code
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
Re: CP Saved member Searchs error V7.7.3
Ruben #263508 02/14/2020 5:50 PM
Joined: Apr 2004
Posts: 1,651
Likes: 33
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,651
Likes: 33
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 5:50 PM.

Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: CP Saved member Searchs error V7.7.3
Ruben #263509 02/14/2020 6:02 PM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
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
Re: CP Saved member Searchs error V7.7.3
Ruben #263510 02/14/2020 6:08 PM
Joined: Apr 2004
Posts: 1,651
Likes: 33
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Apr 2004
Posts: 1,651
Likes: 33
i sent you a pm


Current developer of UBB.threads PHP Forum Software // 7.7.4 released!
isaac @ id242.com // my forum @ CelicaHobby.com
Re: CP Saved member Searchs error V7.7.3
Ruben #263511 02/14/2020 6:18 PM
Joined: Dec 2003
Posts: 6,089
Likes: 14
Ruben Offline OP
OP Offline
Joined: Dec 2003
Posts: 6,089
Likes: 14
Got it thanks for the superior support


Blue Man Group
There is no such thing as stupid questions. Just stupid answers

Link Copied to Clipboard
ShoutChat Box
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
SFS Login Detection Issue [NotABug]
by Ruben - 03/29/2020 1:16 PM
possible bug?
by Morgan - 03/29/2020 5:18 AM
CSS sprites
by Ruben - 03/28/2020 2:29 PM
Who's Online Now
1 members (1 invisible), 119 guests, and 42 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Amusing Terain Scenics
Amusing Terain Scenics
by isaac, August 19
Sky places
Sky places
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 7.7.4