php 在网站上存储/跟踪用户活动

Storing/Tracking user activity on a website

提问by Kenneth .J

I was wondering what would be the best way to go about storing/tracking user activity on a website. I am thinking of user activity along the lines of which webpage a user visited,how many times has she visited,when(i.e timestamp) of his/her vists.

我想知道在网站上存储/跟踪用户活动的最佳方法是什么。我正在考虑用户访问的网页，她访问了多少次，他/她访问的时间（即时间戳）的用户活动。

I am currently thinking of creating multiple tables in my database for each webpage on my website, each of which would track an aspect of user activity on my site. E.g 1 table containing timestamp and userid of each user who visited that webpage, another table storing their actions done on the webpage(e.g liked something)

我目前正在考虑在我的数据库中为我网站上的每个网页创建多个表，每个表都会跟踪我网站上用户活动的一个方面。例如 1 个包含访问该网页的每个用户的时间戳和用户 ID 的表，另一个表存储他们在网页上完成的操作（例如喜欢的东西）

Would this be a good way of tracking user activity or are there other, better methods of doing so?

这是跟踪用户活动的好方法还是有其他更好的方法？

PS: Im using PHP and MYSQL for the website, and am doing this as a means of improving my coding so i won't be using google analytics or anything like it.

PS：我在网站上使用 PHP 和 MYSQL，我这样做是为了改进我的编码，所以我不会使用谷歌分析或类似的东西。

采纳答案by Jonathon

Yep, store everything in a database. Have a table for user visits, one for user interactions etc.

是的，将所有内容存储在数据库中。有一个用户访问表，一个用户交互表等。

To start with have a table page_visitsthat contains an ID for the user (This is your first problem to solve - how to uniquely identify a visitor. Identify them using their IP and or User agent? Create a cookie that refers to a unique ID? There are different methods depending on your goal - none are 100% fool-proof) and the page ID or URL they visited along with a timestamp.

首先有一个page_visits包含用户 ID的表（这是您要解决的第一个问题 - 如何唯一地识别访问者。使用他们的 IP 和/或用户代理识别他们？创建一个引用唯一 ID 的 cookie？那里是不同的方法，具体取决于您的目标——没有一个是 100% 万无一失的）和他们访问的页面 ID 或 URL 以及时间戳。

Add a table to track interactions. How you capture the event is up to you, do you fire an AJAX call using Javascript or jQuery to log an event like a click? This table could contain fields such as user_id, timestamp, action_performed, source_page_id.

添加一个表来跟踪交互。如何捕获事件取决于您，您是否使用 Javascript 或 jQuery 触发 AJAX 调用以记录单击等事件？此表可能包含诸如user_id、timestamp、action_performed、 之类的字段source_page_id。

The way you decide to implement is entirely up to you but a database would probably be the way to go.

您决定实施的方式完全取决于您，但数据库可能是您要走的路。

回答by Kevlar

try creating tables in your database to store the necessary information.

尝试在您的数据库中创建表来存储必要的信息。

Off the top of my head I could think to store their IP address

在我的脑海里，我可以想到存储他们的 IP 地址

get hold of some GEO information and you can use that IP address to obtain and store the country they are in.

获取一些 GEO 信息，您可以使用该 IP 地址来获取和存储它们所在的国家/地区。

You can store the webpage they were on before coming to your website.

您可以存储他们在访问您的网站之前所在的网页。

How many times they have came to your website and the pages they have visited and how many times on each page.

他们访问您网站的次数和访问过的页面以及每个页面上的访问次数。

IP address can be obtained using

IP地址可以使用

$_SERVER['REMOTE_ADDR']

and

和

$_SERVER['HTTP_X_FORWARDED_FOR']

geo information to obtain their country can be obtained from many websites...Usually you have to pay for it if you want up to date info...some people off free older info which is still quite useful.

可以从许多网站获得获取他们国家/地区的地理信息...通常，如果您想要最新信息，您必须付费...有些人关闭了免费的旧信息，这仍然非常有用。

Here is a good provider of GEO information that you pay for but it is very cheap

这是一个很好的 GEO 信息提供商，您可以付费购买，但价格非常便宜

http://dev.maxmind.com/geoip/geoip2/web-services/

http://dev.maxmind.com/geoip/geoip2/web-services/

to do the amount of visits just grab their IP address when they arrive, search your database for that IP and if it exists then increment the number of visits by 1. If not create a new visitor. Do the same incrementation on each individual page visit too.

要计算访问量，只需在他们到达时获取他们的 IP 地址，在您的数据库中搜索该 IP，如果存在，则将访问次数增加 1。如果没有，则创建一个新访问者。对每个单独的页面访问也做相同的增量。

use this to obtain the URL they were on before coming to your site

使用它来获取他们在访问您的站点之前所在的 URL

$_SERVER['HTTP_REFERER']

So a table like this:

所以像这样的表：

userId | userIP | userCountry | visits | webpage1Visits | webpage2Visits | webpage3Visits

Assuming you dont have thousands of pages this could work for a dozen or so pages.

假设您没有数千页，这可能适用于十几页。

There a tonne of other stuff you can store like average time on site etc etc but this is the basic stuff.

您可以存储大量其他内容，例如现场平均时间等，但这是基本内容。

attempt it and when you come across problems along the way ask more questions and people will help :)

尝试一下，当您在此过程中遇到问题时提出更多问题，人们会提供帮助:)

回答by Navdeep Verma

you can use flag for each webpage and save the incremented flag value if he clicks on the webpage to the respective users login database to track which pages (s)he is clicking (this is only for the webpages who has the user database).

您可以为每个网页使用标志并将增加的标志值保存到相应的用户登录数据库中，以跟踪他点击了哪些页面（这仅适用于具有用户数据库的网页）。

回答by John Demian

Without going into the whole GDPR debate, although you may want to take that into consideration before going further, here's how I'd proceed.

无需进入整个 GDPR 辩论，尽管您可能想在进一步讨论之前考虑到这一点，但我将继续进行。

I'd have a separate table for storing user activity, although I may want to look into using something else to store this data in. Perhaps mongoDB and have all the details for each session stored into a json file. but for the purpose of this excersise you could just use MySQL.

我有一个单独的表来存储用户活动，尽管我可能想考虑使用其他东西来存储这些数据。也许 mongoDB 并将每个会话的所有详细信息存储到一个 json 文件中。但是为了这个练习的目的，你可以只使用 MySQL。

I'd suggest adding entries based on sessions so the user activity tablewould look something along the lines of:

我建议添加基于会话的条目，以便用户活动表看起来类似于：

 1. usserId
 2. location or ip
 3. referrer
 4. timestamp
 5. sessionToken 

The second thing I'd do is have another table for just the session tableand have all the details stored there with timestamps.

我要做的第二件事是为会话表创建另一个表，并将所有详细信息与时间戳一起存储在那里。

 1. sessionToken
 2. url
 3. timestamp

Now you can query the last x users that visited your site from the user activity table and when you select one of them in the interface you can see all the details of his visit. This is probably the less taxing method of viewing this information as far as your MySQL queries go.

现在，您可以从用户活动表中查询最近访问过您网站的 x 个用户，当您在界面中选择其中一个时，您可以看到他访问的所有详细信息。就您的 MySQL 查询而言，这可能是查看此信息的最省力的方法。

This is a juvenile look at a very complex process that over the years A LOT of companies started using and it's called Real User Monitoring.

这是一个非常复杂的过程的少年视角，多年来很多公司开始使用它，它被称为真实用户监控。