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)

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

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.

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?

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!

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

Quote:

Originally Posted by Lynne (Post 1825703)

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 .

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

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>

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.

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

Quote:

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?

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 (3)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (3)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.02489 seconds Memory Usage 1,790KB 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 (3)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (3)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: