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'.