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
Really Simple Link Exchange Points System
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
it's a simple query: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..
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`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..
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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:
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