vb.org Archive - Display top referrer on forum index

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

- vBulletin 2.x Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=4)

- - Display top referrer on forum index (https://vborg.vbsupport.ru/showthread.php?t=25170)

This was requested by PeterNRG and actually hacked by Freddie (but it's my thread :p).

1. In index.php find

PHP Code:


		
			
// get newest member

$getnewestusers=$DB_site->query_first("SELECT userid,username FROM user WHERE userid=$numbersmembers[max]");

$newusername=$getnewestusers['username'];

$newuserid=$getnewestusers['userid'];

and above it add

PHP Code:


		
			
// get top referrer

if ($usereferrer) {

  $ref = $DB_site->query_first("SELECT COUNT(*) AS referrals, user.username, user.userid FROM user AS users

                   LEFT JOIN user ON (users.referrerid = user.userid)

                   WHERE users.referrerid <> 0 AND

                   user.userid NOT IN (1,5,7)

                   GROUP BY users.referrerid

                   ORDER BY referrals DESC

                   LIMIT 1");



  eval("\$topreferrer = \"".gettemplate('topref')."\";");

}

2. Create a new template called topref. In this template, you can use $ref[referrals] (number of referrals), $ref[username] (for the top referrar's username) and $ref[userid] (for top referrar's user ID).
For example:

Code:

Top referrer of this forum: <b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$ref[userid]">$ref[username]</a></b> with <b>$ref[referrals]</b> referrals

3. Add an index to field referrerid in table user.

4. Place $topreferrer wherever you want the topref template to be displayed.

That's it. Demo can be found here.

Good luck, and thank freddie! :)

i just applied this hack, as it's something i want on my board, but doing so caused the page to timeout and about 80 e-mails similiar to

Database error in vBulletin: Invalid SQL: SELECT count(*) AS count FROM user WHERE
referrerid = '350'
mysql error: Lost connection to MySQL server during query
mysql error number: 2013
Date: Sunday 12th of August 2001 01:11:17 PM
Script: /forum/index.php?s=
Referer:

to drop into my inbox.

could you advise whether this a problem with the query (inefficient or no indexes on the correct columns) or whether this is more a problem that you've tested this on a site with low qty of users and haven't been able to find out what would happen on a site with 1000+ members.

i would like the hack, but not at the expense of the homepage ;)

cheers

david k

Sorry, I forgot to mention that a new index is needed (I added it about a week ago, not related to this hack at all). Sorry. :(
I updated the first post. Also, please re-do step 2, as I forgot a = there. ;)

Can you elaborate on this?

3. Add an index to field referrerid in table user.

Take a look here. :)

You do not want to loop through every user and count their referrers! If you have 10,000 users, that would be 10,000 queries!

Why don't you work this into your hack instead:

Code:

SELECT COUNT(*) AS count, user.username, user.userid FROM user AS users

                   LEFT JOIN user ON (users.referrerid = user.userid)

                   WHERE users.referrerid <> 0

                   GROUP BY users.referrerid

                   ORDER BY count DESC

                   LIMIT 1

Referrals are done the way it is so that you can figure out what users a person has referred.

Also your code would appear to not work as you intended it to. You are trying to loop through all of the users and are using numbermembers but that will not work for people with > 999 users as it will have commas added to it. You should use $numbersmembers[user] if you really want to do that. Your code took 40 seconds to execute on my site. My suggestion took .03 seconds.

Code:

// get top referrer

if ($usereferrer) {

  $ref = $DB_site->query_first("SELECT COUNT(*) AS referrals, user.username, user.userid FROM user AS users

                   LEFT JOIN user ON (users.referrerid = user.userid)

                   WHERE users.referrerid <> 0

                   GROUP BY users.referrerid

                   ORDER BY referrals DESC

                   LIMIT 1");



  eval("\$topreferrer = \"".gettemplate('topref')."\";");

}



And template:



Top referrer of this forum: <b><a href="member.php?s=$session[sessionhash]&action=getinfo&userid=$ref[userid]">$ref[username]</a></b> with <b>$ref[referrals]</b> referrals.

I wasn't trying to make you look bad. You can not loop through the entire user table querying each user. That would be a very bad thing (tm) to do. The timeout problems the above poster had were because of that.

Heh, I know you weren't. :)
But I'm new with this, so it's ok to mess up sometimes, eh? ;)

Can we start over and put up the right code & explanations?

FireFly: I don't see #4 working on your board...
vB developers: get this hack optimized and included in the next vB version! :cool:

Is there a way to exclude a user from this list? For example, the admin?

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_code_printable (2)bbcode_php_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.01225 seconds Memory Usage 1,747KB 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_code_printable (2)bbcode_php_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: