Referral question

[Ohiosweetheart]

Is there a way to reset members' referral counts back to 0?

I ask because we're planning a referral contest, and need an accurate count of how many referrals each member gets.

[MickDoneDee]

You'll probably have to run a database query such as:

DELETE `referrerid`
FROM `user`
WHERE referrerid >0

Before running any DELETE query, I recommend you run a SELECT query first to make sure the correct data will be selected for deletion. eg:

SELECT `referrerid`
FROM `user`
WHERE referrerid >0

[Ohiosweetheart]

Thank you Mick... now i have to figure out how to do this.
Do I simply type what you have above, in the Manual Query in vB?

[MickDoneDee]

Admin CP - Import & Maintenance - Execute SQL Query - Paste query in Manual Query edit box - click Continue

Copy and Paste exactly as below:

SELECT `referrerid`
FROM `user`
WHERE referrerid >0

If you're happy that the correct data has been called then do another query. This time, copy and paste the DELETE version which should call the same data and, hopefully, give you the option of deleting it.

DELETE `referrerid`
FROM `user`
WHERE referrerid >0

Later, you can do another query to determine who received the most referrals.

[Ohiosweetheart]

I'm afraid this isn't working. I typed in exactly what you said:

SELECT `referrerid`
FROM `user`
WHERE referrerid >0

and got this:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'exquisi_vB.user' doesn't exist

Ok, I did this one....

DELETE `referrerid`
FROM `user`
WHERE referrerid >0

and got this:
This query may modify data in your database. If this change is done in error, it is possible that you will not be able to recover from this change. Are you sure you wish to continue?

is that right?

well, I continued, with it... and got another error:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1109
error desc: Unknown table 'referrerid' in MULTI DELETE

[MickDoneDee]

What is your user table prefix?

[Ohiosweetheart]

would that be the vb_ from my config.php file?

[MickDoneDee]

Your config.php file should have something like:

// Prefix that your vBulletin tables have in the database.
// For example: $tableprefix = 'vb3_';
$tableprefix = '';

That is from my config file which shows my tables don't use a prefix. Please paste the same section from your config file so I can see whether your database is using table prefixes.

[Ohiosweetheart]

Ok this is what mine has:

// Prefix that your vBulletin tables have in the database.
$config['Database']['tableprefix'] = 'vb_';

also I went into repair/optimize tables in the acp... all the tables have the vb_ in front of them

[MickDoneDee]

Okay, run this query which should show which user currently has the most referrals.

SELECT referrerid, COUNT( username )
FROM vb_user
WHERE referrerid > '0'
GROUP BY referrerid
ORDER BY `COUNT( username )` DESC

If that works, run this query which will update the vb_user table by adding the value '0' in all referrerid fields that currently hold a value greater than '0'. (This is preferable to using the DELETE function.)

UPDATE vb_user
SET referrerid='0'
WHERE referrerid>'0'

After that, run the first query again which should produce no results because all referrerid fields will have the value '0'.

[Ohiosweetheart]

OK! I'm off to try this right now.


EDIT: That did it! Mick, thank you for being so patient with me, and so willing to help.
You're wonderful https://vborg.vbsupport.ru/

[MickDoneDee]

I'm glad it worked. If you have any further mysql related questions or need a query then vBulletin.com has a MySql section (http://www.vbulletin.com/forum/forumdisplay.php?f=18).

[Ohiosweetheart]

I noticed that a little while ago. Since I rarely go there, I didn't even think about posting it there. plus I wasn't sure that it was going to be a mysql question, to start with.

thanks again!

[ResaleBroker]

Is there a way to reset members' referral counts back to 0?

I ask because we're planning a referral contest, and need an accurate count of how many referrals each member gets.You don't have to reset the member's referral counts. Simply run a report from the UserCP.

[MickDoneDee]

Could you explain the steps to do this, please?

[ResaleBroker]

Could you explain the steps to do this, please?

AdminCP >> Users >> Referrals >> Enter the start data and end date.

[MickDoneDee]

Excellent! I never noticed that menu before. Thanks for telling us about it.

[ResaleBroker]

Happy to be of help. :)

[Ohiosweetheart]

Well that info is good news! :D

[newforum]

Admin CP - Import & Maintenance - Execute SQL Query - Paste query in Manual Query edit box - click Continue

Copy and Paste exactly as below:

SELECT `referrerid`
FROM `user`
WHERE referrerid >0

If you're happy that the correct data has been called then do another query. This time, copy and paste the DELETE version which should call the same data and, hopefully, give you the option of deleting it.

DELETE `referrerid`
FROM `user`
WHERE referrerid >0

Later, you can do another query to determine who received the most referrals.

If i click execute sql query it gives an error message as "you are not authorized to execute sql queries". I am admin of this site, so what should i do to remove this error message.

[MickDoneDee]

In your includes folder is the config.php file. Open that file in a text editor and find:
// ****** USERS WITH QUERY RUNNING PERMISSIONS ******
// The users specified here will be allowed to run queries from the control panel.
// See the above entries for more information on the format.
// Please note that the ability to run queries is quite powerful. You may wish
// to remove all user IDs from this list for security reasons.
$config['SpecialUsers']['canrunqueries'] = '1';Enter your userid between the two single quote marks and save. Now you should be able to run queries from the admin cp.