Is this SQL query correct?

[powerful_rogue]

$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

$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):
$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?
$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]

Why not just use a join?
$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 1244305258 at 1244305258 ---------------

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:

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

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:
useractivation AS ua
That simply means that you are allowed to use the shorthand "ua" instead of "useractivation". So for instance:
AND ua.emailchange = 0
is the same as:
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?

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

[Dismounted]

What exactly are you trying to do? Why are you dumping the username you just fetched?

[powerful_rogue]

Im trying to get the members name in the "subject" line, however as they are not logged in when they verify their email address, its coming up as "Hello unregistered".

Im trying to grab their username by the email activation id thats stored in the database, and then use that to place their username in the subject line.

[Dismounted]

It looks like you are trying to send an email upon successful activation? If so, why not just use the built-in vBulletin feature to do so?

[powerful_rogue]

Ive created a mod to create a thread to welcome the member when the register. It works fine when email verficiation is switched off, however the problem occurs when its switched on.

It will create the thread, but instead of saying "Welcome Username" it says "Welcome Unregistered"

This is because when you click on the email verification link it dosent sign you into the forum. The only way around this would be to check the email verification ID thats assisgned to the userID and then get the members username from that - however thats where im really struggling at the moment.

Hope that makes sense!

[Lynne]

Have you tried using other hook locations? Maybe the one you are picking is not the best one for this.

[powerful_rogue]

Have you tried using other hook locations? Maybe the one you are picking is not the best one for this.

Hi Lynne,
Thanks for your reply./
The only hook location I could find to do this was register_activate_process .

[Lynne]

And exactly what do you have in the plugin located at that hook location?

[powerful_rogue]

Hi Lynne,
This is what ive currently got at the moment. Ive been trying to work with the SQL queries in this thread, but no luck.

<plugin active="1">
<title>welcome thread upon registration</title>
<hookname>register_activate_process</hookname>
<phpcode><![CDATA[if ($vbulletin->options['wtur_active'] AND $vbulletin->options['wtur_activewhen'] == 0)
{

// Backend Files
require_once('./global.php');
require_once('./includes/class_dm.php');
require_once('./includes/class_dm_threadpost.php');
require_once('./includes/functions_databuild.php');

$threaddm =& datamanager_init('Thread_FirstPost', $vbulletin, ERRTYPE_ARRAY, 'threadpost');
$foruminfo = fetch_foruminfo($foruminfo['forumid']);
$threadinfo = array();
$vboptions =& $vbulletin->options;
$bbuserinfo =& $vbulletin->userinfo;
$username = htmlspecialchars_uni($vbulletin->userinfo['username']);
$forumid = $vbulletin->options['wtur_fid'];
eval('$title1 = "' . addslashes($vbulletin->options['wtur_title1']) . '";');
eval('$title2 = "' . addslashes($vbulletin->options['wtur_title2']) . '";');
eval('$title3 = "' . addslashes($vbulletin->options['wtur_title3']) . '";');
eval('$title4 = "' . addslashes($vbulletin->options['wtur_title4']) . '";');
$title = array("$title1", "$title2", "$title3", "$title4");
$rand_title = array_rand($title, 4);
$wtur_userids_get = explode(",", $vbulletin->options['wtur_postinguserid']);
$wtur_userids = $wtur_userids_get[array_rand($wtur_userids_get)];
$wtur_userid = fetch_userinfo($wtur_userids);
eval('$pagetext1 = "' . addslashes($vbulletin->options['wtur_content1']) . '";');
eval('$pagetext2 = "' . addslashes($vbulletin->options['wtur_content2']) . '";');
eval('$pagetext3 = "' . addslashes($vbulletin->options['wtur_content3']) . '";');
eval('$pagetext4 = "' . addslashes($vbulletin->options['wtur_content4']) . '";');
$pagetext = array("$pagetext1", "$pagetext2", "$pagetext3", "$pagetext4");
$rand_text = array_rand($pagetext, 4);
$wtur_posticons_get = explode(",", $vbulletin->options['wtur_posticon']);
$wtur_posticons = $wtur_posticons_get[array_rand($wtur_posticons_get)];
$allowsmilie = '1';
$visible = $vbulletin->options['wtur_moderate'];

$threaddm->set_info('forum', $foruminfo);
$threaddm->set_info('thread', $threadinfo);
$threaddm->setr('forumid', $forumid);
$threaddm->setr('userid', $wtur_userid['userid']);
$threaddm->setr('pagetext', $pagetext[$rand_text[0]]);
$threaddm->setr('title', $title[$rand_title[0]]);
$threaddm->set('iconid', $wtur_posticons['iconid']);
$threaddm->set('allowsmilie', $allowsmilie);
$threaddm->set('visible', $visible);


$threaddm->pre_save();
if(count($threaddm->errors) < 1)
{
$threadid = $threaddm->save();
unset($threaddm);
build_thread_counters($threaddm);
}
build_forum_counters($forumid);

// update post count for user
$posts = $vbulletin->db->query_first("
SELECT posts
FROM " . TABLE_PREFIX . "user
WHERE userid = ".$wtur_userid['userid']."
");

$newpostcount = $posts['posts'] + 1;

$vbulletin->db->free_result($posts);

$vbulletin->db->query_write("
UPDATE " . TABLE_PREFIX . "user
SET posts = ".$newpostcount."
WHERE userid = ".$wtur_userid['userid']."
");

]]>}</phpcode>
</plugin>
</plugins>

[Lynne]

First off, I'm not sure if you need to include global.php again. Does that cause you any problems at all? I don't even see where you are putting the queries from this thread into that plugin. What is the result of your plugin at this point?

Also, have you looked at register.php? It looks to me like they delete the activation id a few lines above that hook.

[powerful_rogue]

Hi Lynne,

I tried replacing

$username = htmlspecialchars_uni($vbulletin->userinfo['username']);

with

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

and it just left an empty space where the username should have been. It seems it does grab their useractivation id as I made an error in one of my sql statements and it threw this back

SELECT useractivation.userid, user.userid FROM useractivation

WHERE activationid = '9ffcacf1f2f6e1776f23d90fa62a1f0219aaf368'
AND emailchange=0;;

MySQL Error : Unknown column 'user.userid' in 'field list'

and the activationid was exactly the same as the one received in the email asking for verification.

Regarding including global.php again, would I just place "require_once('./global.php');" above the sql statement?

[Lynne]

Of course it's an unknown column 'user.userid' in the field list - you aren't joining to the user table, so how would it get that field? But in the query you wrote, you use "$vbulletin->GPC['i']" which is a variable that is passed. That doesn't mean that line is still in the database. Have you looked at that table which testing this process and seen whether the row you want is still there?

What I was saying about global.php is that it's called in the register.php page, so I'm not sure it needs to be called again.

[Dismounted]

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

$username = $userdata['username'];

[powerful_rogue]

Of course it's an unknown column 'user.userid' in the field list - you aren't joining to the user table, so how would it get that field? But in the query you wrote, you use "$vbulletin->GPC['i']" which is a variable that is passed. That doesn't mean that line is still in the database. Have you looked at that table which testing this process and seen whether the row you want is still there?

What I was saying about global.php is that it's called in the register.php page, so I'm not sure it needs to be called again.

Hi Lynne,
I knew that about the unknown column, I was just showing how I got a database error that confirmed the correct activationID was being associated with the user.

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

$username = $userdata['username'];

Hi Discountinued.

I replaced

$username = htmlspecialchars_uni($vbulletin->userinfo['username']);

with the above query, however it then throws up this error after confirming the email address

Fatal error: Call to a member function save() on a non-object in /home/dc/public_html/testsite/register.php on line 1079

[Lynne]

Try removing the global.php line and see what happens.

[powerful_rogue]

Hi Lynne,

Still comes back with the same error.

[Lynne]

Again I'll suggest that you take a look at the useractivation table during this process. I'm still not convinced that that row isn't deleted and thus you wouldn't get any result from that query.

[powerful_rogue]

Hi Lynne,

Looking through register.php it looks like your right.

Line 1023
// delete activationid
$db->query_write("DELETE FROM " . TABLE_PREFIX . "useractivation WHERE userid=$userinfo[userid] AND type=0");

Line 1068
($hook = vBulletinHook::fetch_hook('register_activate_proce ss')) ? eval($hook) : false;

It deletes the activationid before it gets to the hook I need to use.

Looks like I might be a bit stuffed! I dont suppose you know of any other ways I may be able to resolve this.

[Dismounted]

Catch and hold the username at an earlier hook?

[powerful_rogue]

Thanks dismounted.
Is this an easy thing to do?

[Lynne]

Thanks dismounted.
Is this an easy thing to do?
Again, look at register.php. That is the best thing to do. Find this right around line 1000:
$userinfo = verify_id('user', $vbulletin->GPC['u'], 1, 1);

($hook = vBulletinHook::fetch_hook('register_activate_start ')) ? eval($hook) : false;

You really need to look at the page you are using plugins for to see what is already going on.

[powerful_rogue]

Again, look at register.php. That is the best thing to do. Find this right around line 1000:
$userinfo = verify_id('user', $vbulletin->GPC['u'], 1, 1);

($hook = vBulletinHook::fetch_hook('register_activate_start ')) ? eval($hook) : false;

You really need to look at the page you are using plugins for to see what is already going on.

Hi Lynne,
Thank you ever so much. I have learnt a very valuable lesson. By changing the hook it now works a treat.
They say you learn by your mistakes and this has certainly taught me to check hooks and the code before and after.

One very final thing if it would be possible to pick your brains once more! In the code ive got the following line
$bbuserinfo =& $vbulletin->userinfo;

So I can use the following code to place a link to the new members profile in the text of the post

$bbuserinfo[username] ($vboptions[bburl]/member.php?u=$userid)

At present its still coming up with unregistered within the text of the post and the link has no userid at the end. Ive tried having a play around with this but at the moment am drawing a blank.

Would you be able to suggestions on how to get this to reflect the name shown in
$username = $userdata['username']; along with the userid?

As this is happening before the member is signed in, am I correct in thinking it would be a case of adding more database queries to get this information?

[Lynne]

I really can't say since I have no clue where you placed that link - is that in the plugin? In a template? Seeing that line alone tells me nothing about the variables. What plugin code are you currently using an which hook location?

[powerful_rogue]

Hi Lynne,

In the pluggin I have this line
$bbuserinfo =& $vbulletin->userinfo;

Within the ACP you can type

$bbuserinfo[username] ($vboptions[bburl]/member.php?u=$userid)

So when the thread is created within the text it will place the new members username linking it to their profile. The only issue I have now is getting this to work with the "register_activate_start" hook as its comes up as "Unregistered" and the link has "userid=" on the end.

[Lynne]

You need to follow the trail of what happens when - when during this whole process is the thread being created? And how do you know what variables are available at that time - have you looked at the code around where the thread is created? Also, $bbuserinfo gets used at other places and perhaps it is getting written over. So, perhaps try using a different variable name.

[powerful_rogue]

Hi Lynne,
Im almost there, just got one final hurdle!

I created a query to get the userid of the member. The code below works fine in linking to their profile

Hi $username, welcome to the forum!

Click Here ($vboptions[bburl]/member.php?u=$userid)

However im having problems trying to get the username to show!


"$username
" ($vboptions[bburl]/member.php?u=$userid)

shows "powerful_rogue"

'$username
' ($vboptions[bburl]/member.php?u=$userid)

shows \'powerful_rogue\'


$username
($vboptions[bburl]/member.php?u=$userid)

throws the following error

Parse error: syntax error, unexpected '/', expecting T_STRING or T_VARIABLE or T_NUM_STRING

[Lynne]

How come you are using bbcode and not html? I don't know where that code is being used.

[powerful_rogue]

Hi Lynne,
Thanks for your reply.

Im using it in the ACP

[Lynne]

Then you need to look at the code that is parsing the field. I've always created fields in the ACP that allow html to be used so I'm not sure what else to suggest.