Really Simple Link Exchange Points System

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
cs-web
Forum Commoner
Posts: 27
Joined: Fri Mar 11, 2005 11:57 am

Really Simple Link Exchange Points System

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. :)
cs-web
Forum Commoner
Posts: 27
Joined: Fri Mar 11, 2005 11:57 am

Post 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 :?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Post Reply