vb.org Archive - Error with SQL, Help?

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - Error with SQL, Help? (https://vborg.vbsupport.ru/showthread.php?t=159494)

The-Ensemble

10-04-2007 08:07 PM

Error with SQL, Help?

I have a hack that is meant to show a certain usergroups's members and it keeps giving SQL errors, first it was a table didn't exist then something else after that, I can't get it to work.

The Original SQL Query

PHP Code:


		
			
$users = $db->query_read("

    SELECT $locationfieldselect user.*, user.options

    FROM " . TABLE_PREFIX . "user AS user

    LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)

    WHERE " . TABLE_PREFIX . "user.usergroupid = 12

");

I'm using VB 3.6.7 if that helps at all.

Thanks in advance.

Paul M

10-04-2007 08:11 PM

You need to post the actual error message - "something else" isn't much use. :)

The-Ensemble

10-04-2007 08:15 PM

Ok well here is the first one that says the table doesn't exist.

Database error in vBulletin 3.6.7:

Quote:

Invalid SQL:

SELECT user.*, user.options
FROM vb_user AS user
LEFT JOIN vb_userfield AS userfield ON(userfield.userid = user.userid)
WHERE vb_user.usergroupid = 12;

MySQL Error : Unknown table 'vb_user' in where clause
Error Number : 1109
Date : Thursday, October 4th 2007 @ 04:14:07 PM
Script : /showgroups.php?do=contributors
Referrer :
IP Address : ----------
Username : ---------
Classname : vb_database

Paul M

10-04-2007 08:23 PM

It should be user.usergroup = 12

The-Ensemble

10-04-2007 08:26 PM

Whoops that was the error message from after I tried editing it to get it to work,
here the error message from when Its using the original query.

Quote:

Database error in vBulletin 3.6.7:

Invalid SQL:

SELECT user.*, user.options
FROM vb_user AS user
LEFT JOIN vb_userfield AS userfield ON(userfield.userid = user.userid)
WHERE vb_user.usergroupid = 8;

MySQL Error : Unknown table 'vb_user' in where clause
Error Number : 1109

Paul M

10-04-2007 08:29 PM

Nothing has changed - the answer is the same ;

The WHERE statement should be user.usergroupid, not vb_user.usergroupid.

The-Ensemble

10-04-2007 08:30 PM

thats the table_prefix thingy *points to query in first*

Paul M

10-04-2007 08:33 PM

Yes, I know what it is, that's the point - you need to remove it from the where line, it's not needed. :)

The-Ensemble

10-04-2007 08:38 PM

Oh :o
It works now! :o Thank you! :)

All times are GMT. The time now is 03:35 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01196 seconds Memory Usage 1,726KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (9)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: