Page 1 of 1

Really Simple Link Exchange Points System

Posted: Mon Sep 05, 2005 4:47 pm
by cs-web
Hi, Im just making the last part of my site its going to be a "link exchange" where my sites members submit their link to the exchange and they get a referal link to my site.

There will be only one page displaying all the sites in the link exchange but the list will be ordered with the sites having the most unique IP hits on their referal link at the top and doing down in order of unique IP links.

I was thinking of just having a database table called refferalhits that tracks all referals but I was unsure of how to calculate the order of sites relative to the unique ip hits there referal link got....

Please help

Chris

Posted: Mon Sep 05, 2005 5:25 pm
by feyd
it's a simple query:

Code: Select all

SELECT sites.*, COUNT(referrals.*) AS `referralcount` FROM sites LEFT JOIN referrals ON( sites.id = referral.site_id ) GROUP BY referrals.site_id ORDER BY `referralcount`
or similar, I believe.

This requires one thing through, either the referral table only has uniques, or better yet, counts duplicate ip referrals in a field. You can then see who has the highest repeats.. :)

Posted: Tue Sep 06, 2005 1:46 am
by cs-web
I know its a bit cheeky but could you explain that query? I've never seen a query like that and now I'm kind of worried :?

Posted: Tue Sep 06, 2005 1:58 am
by feyd
sure.. basically.. it says (in plain english)

return all fields of matching records from table 'sites' plus the count of matching referrals (stored as 'referralcount') from table 'sites', linked with table 'referrals' using 'sites' id field and 'referrals' site_id field. Group the results by 'referrals' site_id field, while sorting all results by 'referralcount'


you may have to fiddle with the query a bit, since I botched a little bit:

Code: Select all

SELECT `sites`.*, COUNT(`referrals`.*) AS `referralcount` FROM `sites` LEFT JOIN `referrals` ON( `sites`.`id` = `referrals`.`site_id` ) GROUP BY `referrals`.`site_id` ORDER BY `referralcount` DESC