Showing referrals in $postbit template

[LanciaStratos]

I want to add the number of referrals under my member's avatar, alongside the join date, number of posts, etc. I can edit the postbit template to add "Referrals:", but what do I need to place after that to show the actual number (as is shown in the profile)?

[LanciaStratos]

Anyone...?

[Chen]

In functions.php, add this code

    $getref=$DB_site->query_first("SELECT count(*) AS count FROM user WHERE referrerid='$post[userid]'");
    $post[referrals]=$getref[count]; 

right after this

    if ($wordwrap!=0) {
        $post[title]=dowordwrap($post[title]);
    } 

now you can use $post[referrals] anywhere in your postbit template.
(tested)

[Freddie Bingham]

That will add one group by query for each post on a page so I would suggest not doing that.

You need to add a new field and store an user's referral total and reference it. You would need to update this field in the registration form to incremement the total for each new referree.

[Steve Machol]

Thanks FireFly! Any place in particular in functions.php that this code should be added?

[Chen]

Yeah, after

if ($wordwrap!=0) {
        $post[title]=dowordwrap($post[title]);
    } 

but basically anywhere in the getpostbit function.

But freddie says don't use it, I don't understand why though.

[Freddie Bingham]

Add the code and then add &explain=1 on to your showthread template. I thought we had went over this before but it is not a good idea to put a query into a loop when you can find other ways to do it.

btw this won't work unless you globalize $DB_site

Query: SELECT count(*) AS count FROM user WHERE referrerid='4'
Time before: 0.1417590379715
Time after:  0.15468001365662

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT smilietext,smiliepath FROM smilie
Time before: 0.15805995464325
Time after:  0.15937495231628

table type possible_keys key key_len ref rows Extra 
smilie  ALL          85    


--------------------------------------------------------------------------------

Query: SELECT bbcodetag,bbcodereplacement,twoparams FROM bbcode
Time before: 0.1652489900589
Time after:  0.16588401794434

table type possible_keys key key_len ref rows Extra 
bbcode  ALL          11    


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='825'
Time before: 0.18114805221558
Time after:  0.19452905654907

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='970'
Time before: 0.20991897583008
Time after:  0.22340404987335

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1170'
Time before: 0.24443197250366
Time after:  0.25725698471069

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1078'
Time before: 0.27910697460175
Time after:  0.29200005531311

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1079'
Time before: 0.31239795684814
Time after:  0.32569801807404

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='399'
Time before: 0.34961700439453
Time after:  0.36233603954315

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='4'
Time before: 0.38368904590607
Time after:  0.39635705947876

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='399'
Time before: 0.41198098659515
Time after:  0.42525005340576

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='456'
Time before: 0.44133102893829
Time after:  0.45439994335175

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1083'
Time before: 0.47580301761627
Time after:  0.48880398273468

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1334'
Time before: 0.51404798030853
Time after:  0.52691495418549

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='233'
Time before: 0.54774105548859
Time after:  0.56100499629974

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1212'
Time before: 0.58145999908447
Time after:  0.59433102607727

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT count(*) AS count FROM user WHERE referrerid='1571'
Time before: 0.61116695404053
Time after:  0.62411403656006

table type possible_keys key key_len ref rows Extra 
user  ALL          1185  where used  


--------------------------------------------------------------------------------

Query: SELECT usergroupid FROM user WHERE userid=1
Time before: 0.64509701728821
Time after:  0.64580905437469

[LanciaStratos]

Eek! I may not want to add this after all... I'll be content with FireFly's referrer list hack.

[Chen]

Ok freddie, I (finally) understand what you're talking about.

But I'm doing in a new hack I'm writing, these are the 2 queries:

    $var1=$DB_site->query_first("SELECT count(*) AS count FROM postrate WHERE userid='$post[userid]'");
    $var2=$DB_site->query("SELECT rate FROM postrate WHERE userid='$post[userid]'");

how can I add these somewhere else, so they won't be executed every time?
Thanks for helping.

Just making this clearer.