PDA

View Full Version : Error .. 1054 MYSQL .. Where Clause seems correct .. ideas?


LBSources
06-26-2006, 06:45 PM
This is a simple statement to draw some info on a home page regarding user stats .. right now the error i get is

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?

// 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

LBSources
06-27-2006, 10:56 PM
bump ..

Adrian Schneider
06-27-2006, 11:45 PM
SELECT userid, username FROM myprefix_user WHERE userid=2;

LBSources
06-27-2006, 11:56 PM
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

$headerguests=$db->query_first("SELECT COUNT(*) AS count FROM " . TABLE_PREFIX . "session WHERE userid=0 AND lastactivity>$datecut");

Paul M
06-28-2006, 03:16 AM
This is the same error as this thread ;

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

The broken line is this one ;

$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 ;

$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.

$getnewestmember=$db->query_first("SELECT userid, username FROM " . TABLE_PREFIX . "user WHERE userid=$numbersmembers[max]");

LBSources
06-28-2006, 03:25 AM
Paul, thanks this did help me also :)

VBUsers
06-28-2006, 03:31 AM
Thank you for the help much appreciated.