Is this SQL query correct?

[powerful_rogue]

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.

[Lynne]

$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']

[powerful_rogue]

Hi Lynne,

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

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'] 
    "); 


[Lynne]

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)

[Dismounted]

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
"); 


[powerful_rogue]

Originally Posted by Dismounted

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!

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"

[Lynne]

Originally Posted by powerful_rogue

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 


[powerful_rogue]

Thanks Lynne,

That helped a lot! makes sense now!

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

[RLShare]

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

[powerful_rogue]

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'];