SQL query help

Anon

Hey all,

I'm having a problem with an SQL query. I'm writing some banner exchange scripts, and in the client area I want to show the top 5 referer sites.

Now, the problem is I'm not 100% sure how to do this. I read the MySQL manual and came up with:

$query = "SELECT referer, bannerid, COUNT(*) FROM bannerclick WHERE bannerid='$user_id' GROUP BY referal, bannerid";

'referer' is the referer variable stored when somebody clicks on a banner. 'bannerid' is an id corresponding to the account the banner belongs to. '$user_id' is the account id of the person currently looking at the page (so I only got the referers for his account).

According to the MySQL manual, this should return each referer with the amount of bannerid (= the number of click throughs) in the table.

Then I try to use:

sort($row, SORT_NUMERIC)

and then echo the first 5 rows. The problem is I'm not sure how the Query results will be stored in $row.

Does (After sort) $row[0]["referer"] contain the referer with the most clicks? This doesn't seem like the best way to do it, so any input is appreciated. Thanks in advance

mcrbids

You can try to change the SQL to say "sort by count desc limit 5" so you get ONLY the top five people in descending order, obviating the middle step.

Anon

Well the problem is the database doesn't contain a certain referer with the number of times it's been clicked, but it contains an entry for each click.

This means I need to find out how many entries there are for a certain referer that match the specified bannerid. I'm thinking of doing something like this, but apparently I can't use COUNT(*) with ORDER BY.

$query = "SELECT referer, bannerid FROM bannerclick WHERE bannerid='$user_id' GROUP BY referer ORDER BY COUNT(*) DESC LIMIT 5";

I hope this gives a better idea of what I'm trying to accomplish. Thanks in advance for any help/suggestions.

steved

Try this one:

$query = "SELECT referer, bannerid, COUNT(*) as TotalCount FROM bannerclick WHERE bannerid='$user_id' GROUP BY referal, bannerid ORDER BY TotalCount desc";

Anon

Works like a charm, thanks Steve D!