/    Sign up×
Community /Pin to ProfileBookmark

finding top 10 pages in mysql db

Copy linkTweet thisAlerts:
Jan 22.2006

Hi, this is probably a silly question, but here goes…

I have a MySQL table containing basic site statistics (ie fields including page visited, time and date). I’d like to make a chart of the top 10 pages visited in this part of the site… Is there some kind of MySQL query that gives the total numbers of different pages?

I suppose I could do a long bit of code along the lines of ‘for each row that has a page field of “home”, increment a variable $home’ and find the value of $home afterwards. (and do the same for all other pages). But I’m sure there’s a better way…

Hope this makes sense! Any suggestions? Many thanks!! ?

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@Markbad311Jan 22.2006 — Yea something like this here

[code=php]
$count_sql = "SELECT COUNT('$page_title') FROM $table_name WHERE '$page_title' = '$page_name'";
$count_res = mysql_query($count_sql, $connection) or die (mysql_error());
global $count;
$count = @mysql_result($count_res, 0, "count('$page_title')");
[/code]

just replace it with your particular [COLOR=Blue]$variables[/COLOR] Repeat this statement and name the results to different variables and use them. but I found this to be the easiest for me. I only ever really want to see the important pages that rank number one on [URL=http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rls=GGGL,GGGL:2005-09,GGGL:en&q=erie+bars]google[/URL].
Copy linkTweet thisAlerts:
@chazzyJan 22.2006 — err actually if you want the top ten, it's done like this in mysql:

<i>
</i>SELECT count(page_visited) AS hits,page_visited FROM your_table GROUP BY page_visited ORDER BY hits DESC LIMIT 0,10;

This will give the top 10 pages, highest hits to lowest and what pages.
Copy linkTweet thisAlerts:
@nickismeauthorJan 22.2006 — Great stuff - thanks loads guys... code's a lot more efficient than I thought it would be... ?
Copy linkTweet thisAlerts:
@nickismeauthorJan 22.2006 — Hum... I've had some problems with this for some reason... I've whittled the code down to the following (in problem solving):

[CODE]
$count_page = mysql_query("SELECT page, COUNT (*) FROM user_record GROUP BY page") or die ("SQL failed: " . mysql_error());
[/CODE]

and I get the following error message:

"SQL failed: You have an error in your SQL syntax near 'FROM user_record GROUP BY page' at line 1"

'page' is a field, 'user_record' is the table... I can't see what's wrong here... Can anyone help?

Thanks again!!
×

Success!

Help @nickisme spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @nearjob,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,
)...