数据库统计

数据库统计

问题描述：

I've thought of creating a MySQL table with the following structure:

id  |   date   |   page_address   |    ip_array
1   | 12-12-12 |  /page.php       | ip1, ip2, ip3, ip4

Then for each visited page in my website, it would:

check for the row containing today's date and current page (if there is none, insert one).
check if the visitor's ip is included in the field ip_array, if it's not, then include it.

After this, I'll have a statistic of how many different visitors I've had in each page and each day.

Is it an acceptable way of doing this?

我想过创建一个具有以下结构的MySQL表： p>

  id | 日期|  page_address |  ip_array 
1 |  12-12-12 |  /page.php |  ip1，ip2，ip3，ip4 
  code>  pre> 
 
 然后对于我网站中的每个访问过的页面，它将： p> 
 
 
检查包含今天的日期和当前页面的行（如果没有，请插入一行）。 li> 
 检查访问者的IP是否包含在字段 ip_array  code>中， 如果不是，那就加入它。 li> 
  ul> 
 
 在此之后，我将统计每个页面和每天有多少不同的访问者。   p> 
 
 
这样做是否可以接受？ p> 
  div>

答

NO, ip_array is not a good idea, you'd better do one record for one ip.

id  |   date   |   page_address   | ip
1   | 12-12-12 |  /page.php       | ip1
2   | 12-12-12 |  /page.php       | ip2
3   | 12-12-12 |  /page.php       | ip3
4   | 12-12-12 |  /page.php       | ip4

Then you can do a simple SELECT to count the number of different visitors for each page and each day.

SELECT COUNT(*) FROM your_table WHERE date = ? AND page_address = ?

Even you can let page_address detail store in another table, and use an page_id to relate them.

table1:

id  |   date   |  page_id  | ip
1   | 12-12-12 |  1        | ip1
2   | 12-12-12 |  1        | ip2
3   | 12-12-12 |  1        | ip3
4   | 12-12-12 |  1        | ip4

table2:

id  | page_address 
1   | /page.php      
2   | /page2.php

Then you do do a SELECT like:

SELECT COUNT(*) 
FROM table1 
JOIN table2 
ON table1.page_id = table2.id 
WHERE table1.date =? AND table2.page_address = ?

相关推荐