Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I am writing page statistic script and I have samo questions:
Surrent mysql db design is:
visit_id (unique id for each visitor) | page_id | ip | browser | op_system | etc.. | pagevisit (1 on first page view) | bounce (1 if more than one page viewed) | timeonpage (time in seconds) | time (timestap of pageview)
Row is inserted for each pageview
Example:
1234 | page_1 | 123.123.123.123 | Firefox | etc... | 1 | 0 | 210 | 1187189415
1234 | page_1 | 123.123.123.123 | Firefox | etc... | 0 | 0 | 0 | 1187189430
Why I need inserts for each view?
Because i need url and title of pageview
Currently I create new tables based on timestap (so there are tables that are small enaugh):
timestap: 1187189415
so table is: stats_1187
From one table I get data with this query:
[syntax="sql"]SELECT
browser,
SUM(pagevisit) AS visits,
ROUND(COUNT(*) / SUM(pagevisit), 2) AS ppv,
TRUNCATE((SUM(timeonpage) / SUM(pagevisit)), 0) AS time,
ROUND(((SUM(pagevisit) - SUM(bounce)) / SUM(pagevisit)) * 100, 0) AS bounce
FROM estats_1180
GROUP BY browser
ORDER BY visits DESCMy question is: Is there any better solution for this problem ? (and I am shure there is one )
The second one is how to get data from multiple tables? query for each one and then "merge" in php? (user can select any date range)
Yes there is a lot to read but I hope someone would know how to help.
feyd | Please use[/syntax]
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]