数据库统计

Question

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?

Answer 1

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 = ?