track when the visitor arrives to my website and when he leaves

Let me try to explain, please be patience with my english.

I have this table
| date       |  hour    |      IP     |   reference        |  local_id  |
| 2015-11-11 | 04:54:50 |  ipadrres 1 |    |  page id 1 |
| 2015-11-11 | 04:56:29 |  ipadrres 1 |  |  page id 2 |
| 2015-11-11 | 05:00:50 |  ipadrres 1 |  |  page id 4 |
| 2015-11-12 | 06:54:50 |  ipadrres 2 |    |  page id 18 |
| 2015-11-12 | 06:54:50 |  ipadrres 2 |  |  page id 18 |
| 2015-11-13 | 05:00:50 |  ipadrres 1 |  |  page id 4 |

Open in new window

On that table I catch some date from the visitor to my website, in reference I have the previous page where the visitor was, and I use local_id for internal identificator of the pages that visitor clicks.

I know when the reference says that means that the visitors is browsing on my server and when it says something diferent than that means the visitor came from outside my site.

I looking a query to trace from the first click to the last one that the visitors do but I have no ideas

I hope you can give me some advices
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would probably not try to reinvent the wheel. Look into google analytics. It's free and will give you much more information.
altariamx2003Author Commented:

mankowitz, thanks a lot for take time to answer me

You know, I know and everybody knows that google analytics can do what I need, but because particular requirements for this project I cannot use google analytics.
In that case, you could probably do something like this

select `date`, `ip`, timediff(max(`hour`),min(`hour`)) from table group by `date`, `ip`

This arbitrarily assumes that all web visits end at midnight, which is probably not a valid assumption, but certainly makes the calculation easier. It also doesn't easily account for a person who visits once and then leaves and comes back later in the same calendar day.

Also, I don't think you really need to worry about the referrer header. The first visit from a given IP should be considered the first visit of the day regardless of whether they typed the url into the browser or were referred from google or internally from your own site.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

A much more computationally expensive track would be this

SELECT select `date`, `ip`, timediff(max(t2.`hour`),min(t1.`hour`)) from table t1 join table t2 on (1.ip=t2.ip)
where t1.reference <> '' and t2.reference = ''
and abs(timestampdiff(minute, CONCAT(, " ", t1.hour), CONCAT(, " ", t2.hour)))<100;

Which assumes that a user engages the web site for 100 minutes or less.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
altariamx2003Author Commented:
Ok, let me try with your querys to looking for ideas.

Another question, a friend suggest that I might be a good idea to start php server session and use a variable session to store a id when the user arrives from outside, and use this variable to identify the same user every time that he enter in any link of my site.

And when  the user leaves my website close the session and destroy the variable
This idea looks fine but Im worry about it because if I use the variable that means that I need to open session for any new visitor that came to my site, and if I unterstand this will be computationally expensive for website

is this correct????
altariamx2003Author Commented:
thanks a lot for your help markowitz

the second query give me a lot of ideas for my project
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.