MySQL 5 - Get Visitors from Visits and visit time in DB

Hi there again codewalkers.
Long time no see

First of all I appologise if this is the wrong forum to be posting this on but i don't really know where to get help for this one.

I must create some scripts for a web traffic site and i am a bit puzzled by this. I am putting the time and date along with an ip and some other data from each page view (through an php script) in my db and once a day i would like to store this data in another db. I know how to get the unique visitors with SELECT DISTINCT but i have no ideea how to find out the number of VISITS. Let's say that a new visit is when an ip comes back after 30 minutes, how can i count the visits by the `data` (datetime) field.

10x in advance for any kind of help.

If I understand you correctly you want to count the individual visits to the site. Normally I would just say use COUNT() but you have the requirement of 30 minutes being between the visits.

In this case I would use an ORDER BY on the datetime field and then use the PHP to logically go through each record (first record counts as 1) looking for a minimum 30 min difference between the datetime to increment the count value.

Can you do this purely in the database - sure using a stored procedure, but why do that when you have the power and familiarity of PHP instead. I would only be concerned with doing it with PHP only if the number or records being returned by the DB becomes large enough to be problematic.

And then you have to count the bounces (one unique page view in an interval of 30 mins) and also do all this for each unique IP.

You are right lig, I ended up using the "familiarity of PHP" to do this since. Stored procedures were suggested to me by other people but to be honest it's a little out of my league.

To solve the "number or records being returned by the DB becomes large" problem I changed the db layout and i am using crons do store the data every hour.

10x yet again lig.