vb.org Archive - Is this SQL query correct?

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

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

- - Is this SQL query correct? (https://vborg.vbsupport.ru/showthread.php?t=215348)

Is this SQL query correct?

PHP Code:


		
			
$wturid = $db->query_first("

SELECT userid 

FROM useractivation 

WHERE activationid = '" . $db->escape_string($vbulletin->GPC['i']) . "'

AND emailchange=0;

    ");



$username = $db->query_first("

SELECT username 

FROM user 

WHERE userid = '$wturid'

    ");

Just wondered if anyone could have a quick look at this piece of code for me. For some reason its not posting the username in the result but leaving an empty space.

$wturid['userid'] is probably correct instead of just $wturid (assuming you are getting results from the first query). And the result of that would be $username['username']

Hi Lynne,

Thanks for your reply. I changed it to below however it now comes back with the following error

Quote:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

PHP Code:


		
			
$wturid = $db->query_first("

SELECT userid 

FROM useractivation 

WHERE activationid = '" . $db->escape_string($vbulletin->GPC['i']) . "'

AND emailchange=0;

    ");



$username = $db->query_first("

SELECT username 

FROM user 

WHERE userid = $wturid['userid'] 

    ");

I don't think you can do that. It needs to be something like (you may have to play with it a bit, I'm a trial and error coder):

PHP Code:


		
			
$username = $db->query_first(" 
SELECT username  
FROM user  
WHERE userid = ".$wturid['userid']."
LIMIT 1  
    ");

(I always like to put LIMIT 1 even though it isn't needed when using query_first)

Why not just use a join?

PHP Code:


		
			
$wtuser = $vbulletin->db->query_first("
    SELECT u.userid, u.username
    FROM useractivation AS ua
    LEFT JOIN user AS u USING (userid)
    WHERE ua.activationid = '" . $vbulletin->db->escape_string($vbulletin->GPC['i']) . "'
    AND ua.emailchange = 0
    LIMIT 1
");

Quote:

Originally Posted by Dismounted (Post 1823758)

Why not just use a join?

PHP Code:


		
			
$wtuser = $vbulletin->db->query_first("

    SELECT u.userid, u.username

    FROM useractivation AS ua

    LEFT JOIN user AS u USING (userid)

    WHERE ua.activationid = '" . $vbulletin->db->escape_string($vbulletin->GPC['i']) . "'

    AND ua.emailchange = 0

    LIMIT 1

");

Hi Dismounted,

Thanks for your reply. im still a bit confused regarding the joins so need to read up on them a bit more first. Its all the "u" and "ua" bits I dont quite get.

--------------- Added [DATE]1244305258[/DATE] at [TIME]1244305258[/TIME] ---------------

Ive been told the following but was wondering if anyone would be able to help. I feel im getting so close, but its driving me mad!

Quote:

because $vbulletin->userinfo[] has an empty username as they are a guest. You need to run a check on $vbulletin->userinfo['username']. If it does not have a username than you would use the value from this query:

PHP Code:


		
			
$username = $db->query_first("

SELECT useractivation.userid, username 

FROM useractivation 

LEFT JOIN user ON (useractivation.userid = user.userid) 

WHERE activationid = '" . $db->escape_string($vbulletin->GPC['i']) . "'

AND emailchange=0;

    ");



$subjectname = $vbulletin->userinfo['username'] != '' ? $vbulletin->userinfo['username'] : $username['username'];

However when I output $subjectname as user that is not logged in, its still coming up as "Unregistered"

Quote:

Originally Posted by powerful_rogue (Post 1824515)

Hi Dismounted,

Thanks for your reply. im still a bit confused regarding the joins so need to read up on them a bit more first. Its all the "u" and "ua" bits I dont quite get.

It's just shorthand so you don't have to use the full table name all the time:

PHP Code:


		
			
useractivation AS ua

That simply means that you are allowed to use the shorthand "ua" instead of "useractivation". So for instance:

PHP Code:


		
			
AND ua.emailchange = 0

is the same as:

PHP Code:


		
			
AND useractivation.emailchange = 0

Thanks Lynne,

That helped a lot! makes sense now!

Any idea on why the $subjectname doesnt show at all?

username is set to 'unregistered' when not logged in. You should be checking if userid is set to 0 instead.

And if you want a great explanation of joins this article might be of help.
http://www.codinghorror.com/blog/archives/000976.html

Hi RLShare,

Thats really appreciated. Thank you.

Regarding checking if UserID is set to 0, is that an additional piece of code I will need, or an alteration of this code?

PHP Code:


		
			
$username = $db->query_first("

SELECT useractivation.userid, username 

FROM useractivation 

LEFT JOIN user ON (useractivation.userid = user.userid) 

WHERE activationid = '" . $db->escape_string($vbulletin->GPC['i']) . "'

AND emailchange=0;

    ");



$subjectname = $vbulletin->userinfo['username'] != '' ? $vbulletin->userinfo['username'] : $username['username'];

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (10)bbcode_php_printable (4)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01226 seconds Memory Usage 1,769KB 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 (10)bbcode_php_printable (4)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: