Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving data about most frequently visited page

    Hi!

    I have a database (MS Access) filled with data about web pages that were visited. How can I get the data about what page was most frequently visited - if possible without checking the number of visits on each page and than comparing those numbers.


    Thanks,
    Bostjan

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    I'm a trifle lost. Your access database contains a record of hits logged on various .html pages, or your access database is functioning as a content repository with rows containing the html for individual 'pages' which is pulled in by some ASP?

    And you want to know which number out of a group of numbers is highest, without comparing those numbers?? Umm, that's going to be awfully tricky.

    I think I've missed something here.

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My table looks like this
    PHP Code:
    PageName        Date       Time
    ================================
    page1.asp       ...        ...
    home.htm        ...        ...
    back.htm        ...        ...
    page1.asp       ...        ... 
    In this example page named "page1.asp" was visited most frequenty. I was just wandering if there is a function I coud use to get the most frequent vaue in specific column.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    select PageName, count(PageName) as hits group by PageName order by hits desc
    I am the luckiest man in the world

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just a thought:
    do you realy need a record for each hit? can't you get that from a logfile and analyse it with some log-analyse-tool?

    I always use one page-table where i store the pages title, description, keywords, css, creationtime, last udatetime and the number of hits. If a page is requested, i select the title etc to build the html-header and increment the countervariable. Then it's easy to instantly get the hits/page.

    the select group by might take up some resources if you have some trafic over time (10 pages x 100 visitors a day x 100 days = 100k records ...)

  • #6
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It could of course be analysed with log-analyser, but my boss doesn't want to do it that way - especialy because traffic analysis must include per-registered-user analysis.

    Besides, the MDB file will be always downloaded to a local machine for analysis.

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if i were your boss, i would open access, go to querys, open new, hit querywizard and then choos crossab-wizard. You can there define what you need in the column-headers (pages?) and what you need in the rows (users) and what agregation you want in the fields (hits) and presto !

    it's a jet-sql specific query that is used (something like transform + group by select + pivot). you could run through the wizard and afterwards look at the generated sql and use that inside your ASP-page (or simply use the access query as such.

  • #8
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I were my boss...

  • #9
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    So what's the problem with Roelf's post? It's the textbook answer to your question!
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #10
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Roelf's post is perfect - it does exactly what I wanted.

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by M@rco
    So what's the problem with Roelf's post? It's the textbook answer to your question!
    It may be the perfect sollution for un unnecesary problem.

    If you would frequently run this sort of query online, with a 'group by page' or ' group by page, user' on a intensely used site, then it would cause an unnescecary performance drop. Not the query as such, but the tablelocking which would prevent new inserts from being processed (aka requested pages to be fully processed and responded to).

    If you run it offline, then there are better alternatives like generating the crosstab, which decimates you code to proces the returned recordset.

    It's also reinventing the wheel and making your server doing double work since the logfiles can give you the same info (you only need to keep a session-table with 1 record with the IP number - UserID per session) Using the logs will be safer + will save you an insert in the page-table for each pagerequest.

  • #12
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    I totally agree.

    I was merely prompting bostjank to reply and acknowledge Roelf's post, since it seemed that it had gone unnoticed.

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I see

    I suppose that the more experienced you are, the more sollutions you know to each problem, but the less problems you have to solve because you've learned to effectively use all build in features.

  • #14
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Absolutely.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #15
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do not agree with the "reinventing the whell..." - why would I (or our customers for whom the solution is intended) use (buy) specialized software for analyzing web logs is all we need for our purpose is to retrieve the name of most frequenty used page.

    Especially becuase specialized software cannot operate with the retrieved value and use it in the functions our information already has implemented...

    But once again - I'm very grateful for your help.

    Bostjan


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •