Is this SQL query correct?

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

Originally Posted by Lynne

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.

PHP Code:

<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

PHP Code:

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


with

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


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?