How to count occurrences within a table column using MySQL.

[JsteRmX]

I have a table called user_extended in my DB, and one of the fields is user_referredby.

I also have a table called user, which holds the user_id and user_name.

Each record of the user_extended table will have some number in the user_referredby column. This number would be the user_id of whoever referred this person to the site.

What I want to do, is count the occurance of each number in the user_referredby column, and then have this data displayed to me so that I can see which user_id referred the most people.

Then somehow I would like to pull up the user_name that corresponds with the user_id, so that I will know how many people each user has referred without having to look up their username from the user_id.

So in the end of all this, I want to be able to display on a page how many people each person has referred. And order it so that the person who referred the most will be on the top. Something like this:

Bob has referred 150 people
Jane has referred 99 people
Mary has referred 40 people
Jack has referred 4 people
Jess has referred 0 people



Is there a way to do this?

Many Thanks!

 

[Dan]

Make a "num_referrals" column and increment it each time they refer someone.

I don't understand your user_extended table, so that's the best way I can tell you.

 

[JsteRmX]

thanks dan, yeah i was trying to do something like that before, but i couldnt get the code to work. i actually had a thread started here and someone came back with some code to use, but i am confused and dont really know how to get it to work.

You are saying make a num_referrals column, so each time someone uses that persons referral link it will update the column. I get that, but i have no clue how to make each member of my site have a unique referral link. and if i did give each member a link, how does it know to update this num_referral column each time someone signs up using their link?

so that is why i was trying a different approach, one that doesnt involve each member having their own referral link.

thanks for your help though.

 

[Dan]

If you don't know who was referring them before, how could you do it?

If they type in their username, the code would be something like:

mysql_query("UPDATE users SET num_referrals = num_referrals + 1 WHERE username = '$username'");

 

[Kate]

This could be done using COUNT and GROUP BY functions in mySQL. IMO no need for adding a tracker field as the info is already there.
Could you post the structure of your two tables

 

[JsteRmX]

"user_extended" table
Field Type Attributes Null
user_extended_id int(10) UNSIGNED No
user_hidden_fields text No
user_location varchar(255) Yes NULL
user_homepage varchar(255) No
user_referredby varchar(255) Yes NULL

"user" table
user_id int(10) UNSIGNED No auto_increment
user_name varchar(100) No
user_loginname varchar(100) No
user_customtitle varchar(100) No
user_password varchar(32) No
user_sess varchar(100) No
user_email varchar(100) No
user_signature text No
user_image varchar(100) No
user_timezone char(3) No
user_hideemail tinyint(3) UNSIGNED No 0
user_join int(10) UNSIGNED No 0
user_lastvisit int(10) UNSIGNED No 0
user_currentvisit int(10) UNSIGNED No 0
user_lastpost int(10) UNSIGNED No 0
user_chats int(10) UNSIGNED No 0
user_comments int(10) UNSIGNED No 0
user_forums int(10) UNSIGNED No 0
user_ip varchar(20) No
user_ban tinyint(3) UNSIGNED No 0
user_prefs text No
user_new text No
user_viewed text No
user_visits int(10) UNSIGNED No 0
user_admin tinyint(3) UNSIGNED No 0
user_login varchar(100) No
user_class text No
user_perms text No
user_realm text No
user_pwchange int(10) UNSIGNED No 0
user_xup varchar(100) No
referrals

Well I have tried a couple different things, but clearly I am doing something wrong. Each one of these codes returns some error:

$query = "SELECT user_referredby, COUNT(*) FROM e107_user_extended GROUP BY user_referredby"; 
	 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo "$row";

This gives me the error: parse error, unexpected $ on the last line of the file, which is just </html>

$sql = "SELECT user_extended_id COUNT(user_referredby) FROM e107_user_extended GROUP BY user_referredby "; 
	 
$result = mysql_query($sql) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo "$row";
	}
?>

gives this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(user_referredby) FROM e107_user_extended GROUP BY user_re.

I obviously don't know what I'm doing. I just started playing around with PHP and MySQL a couple weeks ago, so I am learning. Thanks for any help.

 

[Kate]

Well for the 1st bit of PHP code did you close the while block with a trailing } ?
2nd block: you need to separate the fields with a comma in your select statement

I assume that in your table user_extended you have several records per user and that user_extended_id will match user_id in table user.
So you have something like this:

Select user.user_name,
count(user_extended.user_referredby) as referals
From
user_extended
Inner Join user ON user.user_id  = user_extended.user_extended_id 
group by user.user_name
order by referals desc

This should give you a list of top referers (sorted in decreasing order), note that I have aliased the count too.