Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 06-26-2006, 06:45 PM
LBSources LBSources is offline
 
Join Date: Nov 2005
Location: New York
Posts: 329
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Error .. 1054 MYSQL .. Where Clause seems correct .. ideas?

This is a simple statement to draw some info on a home page regarding user stats .. right now the error i get is
Quote:
Invalid SQL:
SELECT userid, username FROM myprefix_user WHERE myprefix_userid=2;

MySQL Error : Unknown column 'myprefix_userid' in 'where clause'
Error Number : 1054
I changed the prefix in this example, but the correct prefix is in the error. What exactly is it complaining about?

Code:
// forum stats start  
$numbersmembers = $db->query_first("SELECT COUNT(*) AS users,MAX(userid) AS max FROM " . TABLE_PREFIX . "user");  
$numbermembers = number_format($numbersmembers['users']);  
$counter = $db->query_first("SELECT COUNT(postid) AS posts, COUNT(threadid) AS threads FROM " . TABLE_PREFIX . "post"); 
$totalposts=number_format($counter['posts']);  
$countthreads = $db->query_first("SELECT COUNT(*) AS threads FROM " . TABLE_PREFIX . "thread"); 
$totalthreads=number_format($countthreads['threads']);  
// forum stats end  

// total online start  
$datecut = TIMENOW - $vbulletin->options['cookietimeout'];  
$headerguests=$db->query_first("SELECT COUNT(*) AS count FROM " . TABLE_PREFIX . "session WHERE userid=0 AND lastactivity>$datecut");  
$headerusers=$db->query_first("SELECT COUNT(DISTINCT(userid)) AS count FROM " . TABLE_PREFIX . "session WHERE " . TABLE_PREFIX . "session.userid>0 AND " . TABLE_PREFIX . "session.lastactivity>$datecut");  
$headerguests=$headerguests[count];  
$headerusers=$headerusers[count];  
$totalonline=$headerguests+$headerusers;  
// total online end 

// get newest member name and userid start  
$getnewestmember=$db->query_first("SELECT userid, username FROM " . TABLE_PREFIX . "user WHERE " . TABLE_PREFIX . "userid=$numbersmembers[max]");  
$newusername = $getnewestmember['username'];  
$newuserid = $getnewestmember['userid'];  
// get newest member name and userid end
Reply With Quote
  #2  
Old 06-27-2006, 10:56 PM
LBSources LBSources is offline
 
Join Date: Nov 2005
Location: New York
Posts: 329
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

bump ..
Reply With Quote
  #3  
Old 06-27-2006, 11:45 PM
Adrian Schneider's Avatar
Adrian Schneider Adrian Schneider is offline
 
Join Date: Jul 2004
Posts: 2,528
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

[sql]SELECT userid, username FROM myprefix_user WHERE userid=2;[/sql]
Reply With Quote
  #4  
Old 06-27-2006, 11:56 PM
LBSources LBSources is offline
 
Join Date: Nov 2005
Location: New York
Posts: 329
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

ive seen the page before i was more hoping for somone to point out the error if it jumped out at them ..

I see the section you are pointing out, but how can i also set that syntax in this code.

cause it seems the problem is at this section

Code:
$headerguests=$db->query_first("SELECT COUNT(*) AS count FROM " . TABLE_PREFIX . "session WHERE userid=0 AND lastactivity>$datecut");
Reply With Quote
  #5  
Old 06-28-2006, 03:16 AM
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Location: Nottingham, UK
Posts: 23,748
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

This is the same error as this thread ;

https://vborg.vbsupport.ru/showthread.php?t=119719

The broken line is this one ;

PHP Code:
$getnewestmember=$db->query_first("SELECT userid, username FROM " TABLE_PREFIX "user WHERE " TABLE_PREFIX "userid=$numbersmembers[max]"); 
The table name is missing from the WHERE section after the table prefix.

i.e. TABLE_PREFIX . "userid should be TABLE_PREFIX . "user.userid

However, table prefix should not be used in WHERE clauses anyway, instead you should us an AS after the initial table selection, like this ;

PHP Code:
$getnewestmember=$db->query_first("SELECT userid, username FROM " TABLE_PREFIX "user AS user WHERE user.userid=$numbersmembers[max]"); 
Finally, since only one table is actually used in this select, the table references can be dropped completely as no ambiguity is possible.

PHP Code:
$getnewestmember=$db->query_first("SELECT userid, username FROM " TABLE_PREFIX "user WHERE userid=$numbersmembers[max]"); 
Reply With Quote
  #6  
Old 06-28-2006, 03:25 AM
LBSources LBSources is offline
 
Join Date: Nov 2005
Location: New York
Posts: 329
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Paul, thanks this did help me also
Reply With Quote
  #7  
Old 06-28-2006, 03:31 AM
VBUsers's Avatar
VBUsers VBUsers is offline
 
Join Date: Aug 2004
Posts: 830
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Thank you for the help much appreciated.
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 10:40 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04436 seconds
  • Memory Usage 2,230KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_code
  • (3)bbcode_php
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (7)post_thanks_box
  • (7)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (7)post_thanks_postbit_info
  • (7)postbit
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete