Previous Thread
Next Thread
Print Thread
Hop To
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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
Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
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!
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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
Joined: Apr 2004
Posts: 1,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145
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
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145
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
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
1 member likes this: Gizmo
Joined: Apr 2004
Posts: 1,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145
my previous reply has been updated.


Current developer of UBB.threads PHP Forum Software
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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
Joined: Apr 2004
Posts: 1,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145
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
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145
i sent you a pm


Current developer of UBB.threads PHP Forum Software
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
0 members (), 396 guests, and 110 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)