What Would Be The Best SQL For A Referral Site?

[Sussex_Chris]

The site I am creating will have a referral program and I also thought of having a referral contest each month.

So my idea for the contests page, it will display the current months stats where a sql query takes place that does the following:

$sql=mysql_query("SELECT * FROM `members` WHERE `referrer`>0 AND `signupdate`>$start");

But obviously that code will not count the rows for each member.

So, I thought that the only ways to do it would be to:

1) Add an additional column into the table of monthrefs where at the 1st of each month a cron job would reset it to 0 and for each referral that they get the value of the cell will increase by 1;

2) Run an sql query for every member in the database to search every member in the database to check if they were referred to the site by that member but obviously that is a massive query and will take a long time to load.

Those 2 ways were the only ways that I could think of, is there a better way to do this?

[Coyote6]

SELECT `referer_id`, COUNT (*) as referrals FROM `members` WHERE `referrer`>0 AND `signupdate`>$start GROUP BY `referer_id`

[Sussex_Chris]

Hmm, I tried the following but it would not echo anything out:

$result=mysql_query("SELECT `referrer`, COUNT (*) as referrals FROM `members` WHERE `referrer`>'0' GROUP BY `referrer`");
echo $result['referrals'];

Inside my DB I have got 2 rows.

Format:

CREATE TABLE `members` (
`id` bigint(20) NOT NULL auto_increment,
`username` varchar(255) NOT NULL,
`signupdate` varchar(255) NOT NULL,
`referrer` varchar(255),
`referrals` varchar(255) NOT NULL,
PRIMARY KEY (`id`, `username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The referrals field is added +1 each time a person signs up under them.

Inside this table I have got 2 users:
1) 1-admin-9999-(blank)-1
2) 2-testuser-9999-1-0

So testuser(ID 2) was referred by admin (ID 1)

But it is not echoing out anything?

My idea was to echo out just the top 10 referrers of the month but I have removed the date part from the sql and it still isn't echoing out any data :S

[Coyote6]

I think the referrer and referrals fields should be integers and your primary key should just be the id and create a unique index on the username.

CREATE TABLE `members` (
`id` bigint(20) NOT NULL auto_increment,
`username` varchar(255) NOT NULL,
`signupdate` varchar(255) NOT NULL,
`referrer` bigint(20) NOT NULL UNSIGNED DEFAULT '0',
`referrals` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

That way you will have a single unique key for you primary key.

Your way may allow this.
id | username
1 | test
1 | no test
2 | no test

Even though your auto increment should prevent this from happening on an unique username insert, an update may corrupt your id field or if the username is not unique it would still be considered unique because the auto increment would give the id a different number... Someone please correct me if I am wrong. If you do not care if your usernames are unique... (not a good idea in my opinion) then do not give them a unique key.

Anyways as for the referrer it should be the same data type as the foreign key in this case the id field... Since auto increment is on you can give the field an unsigned value unless you intentionally want to go into the negatives with you auto increment (I don't know why you would want to on an id field). Set the default at 0 so there is always a value in it... Not a must but seems to work better for me. And since the referrals field is just a count start it at 0.

May see if this solves the issue.

[Sussex_Chris]

I think the referrer and referrals fields should be integers and your primary key should just be the id and create a unique index on the username.

CREATE TABLE `members` (
`id` bigint(20) NOT NULL auto_increment,
`username` varchar(255) NOT NULL,
`signupdate` varchar(255) NOT NULL,
`referrer` bigint(20) NOT NULL UNSIGNED DEFAULT '0',
`referrals` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

That way you will have a single unique key for you primary key.

Your way may allow this.
id | username
1 | test
1 | no test
2 | no test

Even though your auto increment should prevent this from happening on an unique username insert, an update may corrupt your id field or if the username is not unique it would still be considered unique because the auto increment would give the id a different number... Someone please correct me if I am wrong. If you do not care if your usernames are unique... (not a good idea in my opinion) then do not give them a unique key.

Anyways as for the referrer it should be the same data type as the foreign key in this case the id field... Since auto increment is on you can give the field an unsigned value unless you intentionally want to go into the negatives with you auto increment (I don't know why you would want to on an id field). Set the default at 0 so there is always a value in it... Not a must but seems to work better for me. And since the referrals field is just a count start it at 0.

May see if this solves the issue.
I check the columns for existing username on the signup form that I have created.

The problem that I was having was more thinking of the best way to do this:

Calculate the user that has had the most referrals this month.

So I search MySQL for users WHERE date>1st of this month AND WHERE referrer>0

Then I want to display the top 20 referrers, so the top 20 users with the most referrals this month.

What would be the easiest way to display this?

I have changed my DB over a bit from your suggestions now :)

[Old Pedant]

*CLEARLY* YOU SHOULD NOT put the referrer in the MEMBERS table!!!

That makes no sense AT ALL! You don't want to create a new record in the Members table for each "hit" on a URL!

CLEARLY you need *THREE* tables:

Table: Members
memberid int primary key (and probably autonumber)
username varchar
password varchar
signupdate datetime -- NEVER store dates or times in a VARCHAR field!!!

Table: urlsForReferral
urlid int primary key (probably autonumber)
url varchar

Table: memberReferrals
memberid int foreign key to Members(memberid) NULL
urlid int foreign key to urlsForReferral(urlid)
whenreferred DATETIME

As each referral occurs, you add a record to the memberReferrals table. I would normally expect that you wouldn't record a "hit" here if no member was involved, but if you want to, that's okay. In which case, with no member involved, you leave the memberid field NULL.

Then:

SELECT memberid, COUNT (*) as referrals
FROM memberReferrals
WHERE memberid IS NOT NULL
AND YEAR(whenreferred) = YEAR(curdate())
AND MONTH(whenreferred) = MONTH(curdate())
GROUP BY memberid
ORDER BY COUNT(*) DESC
LIMIT 20

Or you could join that back to the members table to get the usernames as well as the ids:

SELECT M.memberid, M.username, COUNT (*) as referrals
FROM members AS M, memberReferrals AS MR
WHERE M.memberID = MR.memberid
AND YEAR(whenreferred) = YEAR(curdate())
AND MONTH(whenreferred) = MONTH(curdate())
GROUP BY M.memberid, M.username
ORDER BY COUNT(*) DESC
LIMIT 20

(You don't need to check for null memberid here, because the join ensures those records will be ignored.)

[Coyote6]

I missed this part too... change the `signupdate` to some type of date field like TIMESTAMP or DATETIME.

Test this an see if you get any results... You should get all of the results each member has thus far. This would eliminate you having to keep track inside another field for the most all time referrals.

SELECT `referrer`, COUNT (*) as referrals
FROM `members`
WHERE `referrer`>0 GROUP BY `referrer`


If you get results with this than it is your date field that is causing the problems. Then add the date field back in with the correct data type.


SELECT `referrer`, COUNT (*) as referrals
FROM `members`
WHERE `referrer`>0 && `signupdate`>SUBDATE(NOW,INTERVAL 1 MONTH)
GROUP BY `referrer`



Kinda... forgot where I was going with this reply... went to lunch and lost track.. o well hopefully you get the point.

[Coyote6]

The referral field is a reference back to the member id and not a url correct?

[Old Pedant]

The referral field is a reference back to the member id and not a url correct?

If so, then what is the referrer field????

*I* assumed that in his naming, "referrer" and "memberid" are fk/pk and that "referral" is the URL. But agreed it's not clear.

But I still think he *clearly* needs the three table design I proposed.

[Coyote6]

I believe the referrer is just giving credit to the individual who referred them to using the site. So as a new user you are only allowed to have one referrer (the foreign key of the member id who referred you)... which could be a separate table I guess. I do not know what exactly the standards say about having a foreign key inside the same table that it refers to. Is that a "no no" in db design?

[Sussex_Chris]

Quick clearup of referrals, referrer field before I read through the rest of your guys awesome comments:

Referrer field: The ID of the member that referred them to the site;
Referrals Field: Instead of using MySQL to count referrals, for each signup I was adding 1 to the referrer field.

The only adding up that needs to be done is within the referral contest. The actual referrer will only ever see the number of referrals that signed up under him and his earnings for them, so I thought that by adding this field it would reduce the stress on the server by not having to count through every member ID each time the member visited the stats page as it would only have to retrieve 1 field value?

[Coyote6]

Hmmm... maybe... just seems like an extra hassle to me when you could limit the results just to that current user...

So when a user goes to their stats page it would retrieve something like these two queries...



// Retrieve member's referral numbers for this month.
$q = "SELECT `referrer`, COUNT (*) as referrals " .
"FROM `members` " .
"WHERE `referrer`={$_SESSION['member_id']} && `signupdate`>SUBDATE(NOW,INTERVAL 1 MONTH) " .
"GROUP BY `referrer`";

// Retrieve member's total referral numbers.
$q = "SELECT `referrer`, COUNT (*) as referrals " .
"FROM `members` " .
"WHERE `referrer`={$_SESSION['member_id']} " .
"GROUP BY `referrer`";

[Old Pedant]

Okay, simpler than I thought. Sorry!

So indeed only one table, and only need the referrer field. No reason for the referral field.

Yes, it's find to have a FK to a PK in the same table.

Ignore all my ramblings.

[Sussex_Chris]

Hmmm... maybe... just seems like an extra hassle to me when you could limit the results just to that current user...

So when a user goes to their stats page it would retrieve something like these two queries...



// Retrieve member's referral numbers for this month.
$q = "SELECT `referrer`, COUNT (*) as referrals " .
"FROM `members` " .
"WHERE `referrer`={$_SESSION['member_id']} && `signupdate`>SUBDATE(NOW,INTERVAL 1 MONTH) " .
"GROUP BY `referrer`";

// Retrieve member's total referral numbers.
$q = "SELECT `referrer`, COUNT (*) as referrals " .
"FROM `members` " .
"WHERE `referrer`={$_SESSION['member_id']} " .
"GROUP BY `referrer`";

This is what I plan on doing per user. But I also plan on having a "Contests" page. On this page I will have contests (Competitions) where there will be:
1) An earnings contest;
2) A referrals contest:

The referral contests will be, the user with the most referrals this month wins XXXXX. But the trouble that I was originally having was choosing the correct SQL to work that out without causing a massive load on the server as that page will be accessible to every user. This was the reason that I was thinking of my 1st option of having a cron job that clears the monthlyrefs field each month and starting back over each month and just using that column for that month? But then it would be pretty much open to fraudulant signups which was why I was then thinking of :

Contest for monthly referrals where the referral has earnt over $1. Which would require an SQL to search the DB. Possibly a cron job could update the records every XXX which would obviously take a lot of stress from the server?

[Old Pedant]

How many, total, users do you expect on the site? If it's less than a couple of thousand, then I think your performance worries are unfounded.

[Sussex_Chris]

How many, total, users do you expect on the site? If it's less than a couple of thousand, then I think your performance worries are unfounded.
I am not sure at the moment, but I am taking it from the worst point of view (Best for the website) and in the case that it does grow large then for the SQL to be ok from the start.

I'm fine with the SQL per user to calculate their own referrals but if I do add the contest then it will need to count every users referrals to work out the top 10 referrers. What I think I will do is run a cron job on the hour every hour to re-calculate the stats and put the results into a new table called monthlyrefs where I can just sort by referrals and display on the page limiting to 10/20/whatever number I want. This seems to me like the best way to do this considering the server load that would be placed if every user had to do a large calculation like this for every user. Am I right in thinking this?

[Old Pedant]

Well, if you are going to do that, then why store more than the top 10 (or 20) in the "top winners" table??? No point in storing all of them, unless you want to be able to tell Joey that he is in 417th place.

[Sussex_Chris]

What would be the easiest way to calculate the top referrers within PHP like the following:

$usercount = count * from members
$i=0;
while ($i<=$usercount){
Select * from members where referrer=$i and date>xxxx
$i++;
}

I was planning on having something like the above code run through all the members and then insert the details into the DB. How could I use PHP/MySQL to calculate the top 10/20 referrers without having written the data to a table, can it cache it?

[Coyote6]

Would creating a VIEW work better?


CREATE VIEW `monthlyrefs` AS
SELECT `referrer`, COUNT (*) as referrals
FROM `members`
WHERE `referrer`>0 && `signupdate`>DATEFORMAT(NOW(),'%Y-%m-01 00:00:00')
GROUP BY `referrer`
ORDER BY referrals
LIMIT 10

[Sussex_Chris]

Would creating a VIEW work better?


CREATE VIEW `monthlyrefs` AS
SELECT `referrer`, COUNT (*) as referrals
FROM `members`
WHERE `referrer`>0 && `signupdate`>DATEFORMAT(NOW(),'%Y-%m-01 00:00:00')
GROUP BY `referrer`
ORDER BY referrals
LIMIT 10

That looks more like it, I havent heard of views before lol. I'm pretty sure there are some big gaps in this guide I'm going through :S

How would I echo out data like that and limit it to the top 10 too?

[Coyote6]

Don't feel bad I just learned about them a few weeks ago... query it just like any other table, from what I believe I know about them is it just keeps itself up to date based on your actual table data.

Since we limited the original view to 10 records... (if it worked correctly) then we just need to select the records from the view.


SELECT `referrer`, `referrals` FROM `monthlyrefs`

[Sussex_Chris]

Hmm, I tried the following but got an error message:

<?php
include ("config.php");
// Error Reporting Turned On For Testing Purposes
error_reporting(E_ALL);
ini_set("display_errors", 1);



mysql_query("CREATE VIEW `monthlyrefs` AS SELECT `referrer`, COUNT (*) as referrals FROM `members` WHERE `referrer`>0 && `signupdate`>0 GROUP BY `referrer` ORDER BY referrals LIMIT 10");

$result = mysql_query("SELECT `referrer`, `referrals` FROM `monthlyrefs`");
while($row=mysql_fetch_array($result)){
echo "Referrer: ".$row['referrer']."<br />";
echo "Referrals: ".$row['referrals'];
}
?>

Error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\sqlsite\test.php on line 12

[Old Pedant]

Try this in a DB tool, *first*.

Don't just dump it into your PHP code without testing.

The VIEW is wrong. Because you do ORDER BY COUNT(*) you will get the *LOWEST* 10 counts. You need to use
ORDER BY COUNT(*) DESC LIMIT 10

But you know, a VIEW means the query must be executed every time the view is used.

So it's not one whit better than making an ad hoc query.

If you want to cache the results, you need to do as you suggested: create a separate table and fill it with records.

That's easy to do:

DROP TABLE TopReferrers IF EXISTS;

CREATE TABLE TopReferrers (memberid INT, referralCount INT );

INSERT INTO TopReferrers( memberid, referralCount )
SELECT referrer, COUNT(*)
FROM Members
GROUP BY referrer
ORDER BY COUNT(*) DESC
LIMIT 10;

[Sussex_Chris]

Thanks Old Pedant. All of that code worked apart from PHPMYADMIN did not like the IF EXISTS part so I just changed it to drop once I had created the table for the first time.

Would it be a good idea to run this query every time a user goes onto the referral contest page or to run it myself hourly using a cron job?

[Old Pedant]

Oh, definitely NOT each time a user hits the page.

Hourly or even less often.

[Coyote6]

Well if the script is going to have to update the list inside the new table or create a new table every time, then you need to go back to the original idea of having it in a field inside the member's table and adding one to it every time a new user is referred. Then just selecting the top ten based on the number of referrals that user has.

If not creating a new table then repopulating it with the same query seems like it would take even longer.

SELECT memberId, referrals FROM Members ORDER BY referrals DESC LIMIT 10;


Gee anyone else feel like we are just spinning around in circles chasing our tails.
:D

[Coyote6]

Running it every so often would not be so bad... Won't be exactly up to date all the time but close enough. Instead of deleting it could you just truncate the table?

[Sussex_Chris]

I think that by creating the temporary table it leaves it open to a lot more options/choice if I wish to change it again in the future. I will be running a cron job every 30 mins to update the list so it should not be too much of a hassle to the user.

Thanks a lot for your help guys :)

Until next time ;)