How could I achieve this using with MySQL + ajax and or php?

badwolfff
badwolfff used Ask the Experts™
on
I need to show a counter on some pages on my website in this format:

"X people are currently viewing this page."

My thoughts were:
Since I have access to phpmyadmin I could manually create a table in my DB.
When a person visit a page on my site I would like to add the number 1 in a mysql cell for that page in the table I created above.
When he leaves I would like to do a -1 on the same cell.
Naturally this would have to take in account all visitors on the site so, if, when visitor A enters, there are already 30 visitors, it should show 31 as the number "x" above.

It would be great if this were in AJAX and could update in real time.
Alternatively at the very least I could make do with a pure jquery or php solution to start with but I will ideally want to do it in AJAX.

Any thoughts, links or code help would be greatly appreciated.
I need
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
The increment part is the easy part, the leave is the difficulty.
The process should be to use an event/transactional type of a table, including an entry for when the visit began, while updating a column that keeps time when the last update occurred. The combination will give you the duration a person was on each page.
Initial/updated within a 5 second interval or the entry is seen as invalid.

Where your output is the count/group by the URL.

Author

Commented:
Hi,

thanks. Any code suggestions on achieving this? I would love to learn.
Distinguished Expert 2017

Commented:
No, usually, seen those pages on an interactive site that uses something like a chat, etc. that has to maintain a connection as data flows back to the user.
Are you looking to include this on any page just as a way to indicate the number of users logged in.

You would likely have to use a JavaScript/client side scripting ...

On the backend side you would use something similar to insert into tablename () values () on duplicate key keepalive=now0(

If the page is interactive, you will have that when your client side checks in for an update.

It has to be fully worked out

Is access anonymous or some will have to login and those or all will be counted?
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Author

Commented:
Access is anonymous. I would like to use this feature on all products pages on my site.
Distinguished Expert 2017
Commented:
Presumably each page will have a counter that will either increment or decrement as user view or leave the page.
You will use a common include JavaScript file? That will use a loop to call an process that updates while returning the current count.

I.e. Check_status (page,cookie_identifier) )the ip of the request is determinable.

The check_status will also have the cleanup duties.removing entries that have not been updated within the specified time I.e. Tge interval your client side scripts use to check, I.e. If the check is triggered every five seconds your delete interval will be that last update was more than 5 seconds ago.

Author

Commented:
Thanks for all the help arnold. The thing is, I do understand the theory and most of what you have said I already knew more or less. I was really hoping for code help or at least pointing towards any public domain websites or even commercial pieces of code that did just that. No offence, but I need to get this to work so I have to decide if I need to start from scratch or reuse something that already exists out there.
Owner (Aidellio)
Most Valuable Expert 2015
Commented:
There will be several ways to do this, with varying levels of accuracy.  Given this is anonymous, the only information you've have to identify a "user" is an IP address, and a session (that utilises a cookie on the client).

You will need to keep track of the Session ID, IP Address, page viewed, time viewed.

Initially you could "assume" that a user has stopped viewing that page after X minutes.

On the client side, your ajax script would run every minute? to just get the updated value.  There are more complicated plugins you can use for "push" notifications e.g. https://pusher.com/tutorials/realtime-notifications#/lang=php that you could use also.
Most Valuable Expert 2011
Top Expert 2016
Commented:
Here's the problem with the question "how many people are viewing my site"  There is no notification that can tell you when a client leaves.  Your server will know when a client enters, because there will be an HTTP request, but there may be only one request (or a few) at the initial entry to the site.  For a better understanding of the client/server relationship, this article may be helpful:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

To illustrate the issue, ask yourself whether I am looking at this page now.  Obviously I looked at it once, at least, because I posted some information.  But am I still here?  Is my browser window still open?  Or did I close my browser?  Or did I leave and go get coffee?  Or did I leave my browser window open, but switched to another page in another browser tab?  None of these "leave" events triggers any kind of HTTP request to the server.  I am like Schroedinger's Cat as far as you can tell.  As a result, my counsel is, "have fun developing the app, but do not rely on it for accurate information."

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial