CODEWITHSUNDEEP
phpmysql
Patrioticcow
Patrioticcow

Reputation: 27038

php mysql, how to get total time?

i have a table that has users, the pages that they visited , session id and time they spent on each page.

session_id | talentnum    | page_name    | time
008873e0   | user1        | test.php     | 1311630222
008873e0   | user1        | test.php     | 1311630215
008873e0   | user1        | index.php    | 1311630222
008873e3   | user2        | test.php     | 1311630216
008873e3   | user2        | index.php    | 1311895032

how to find ouy how much time a user spent on each session

i think i should find how much the user spent on each page in a session then add the time?? i'm a bit confuze

edit. the time is the time() when they enter that page

any ideas? thanks

Upvotes: 0

Views: 374

Answers (2)

Friek
Friek

Reputation: 1541

This should work:

select distinct(session_id), max(time) - min(time) from test group by session_id

Then you have it grouped by session_id. To have it grouped by user:

select distinct(talentnum), max(time) - min(time) from test group by talentnum

Upvotes: 3

George Cummins
George Cummins

Reputation: 28906

First, get a list of visitors:

SELECT DISTINCT( sesssion_id ) as session FROM _yourTable_;

Then perform another query to get the start and end times of each visit:

SELECT MIN(time) as START_TIME, MAX_TIME(time) as END_TIME WHERE session_id = '$desired_session';

Then, retrieve the results via PHP and subtract the start time from the end time to get the total length of the visit.

Upvotes: 2

Related Questions