create a query

[neo_phyte]

I have here a simple table:

 

avatar_name                            dt_detection

 

Walegg Etzel                            2007-05-30 19:51:41

Plotunium Paine                          2007-05-30 19:55:03

Brain Cazalet                            2007-05-30  20:03:55

Sebby Voyager                          2007-05-31  08:05:31

Urban Loon                                2007-05-31  08:08:31

Klaas Berjis                                2007-06-01  08:09:05

 

 

Note: the date and time of detect is one field only.

 

Now based on the table above, I would like to create two queries such as below:

 

1. Current online:

Assuming the date right now is 2007-05-30, how do I create a query that display the number of avatar on a current date.

 

2. Total visits:

This is the cumulative count for the number of avatar visits. It should not only be unique Avatar visits. For example, if Walegg Etzel visited. She leaves and then back later on. That should log 2 counts for this statistic).

 

Thanks in advance.

[neo_phyte]

So far this is what I have been doing, this is for current online.

 

This is script returns nothing.

 

$table = "tablename";
$today = gmdate("Y-m-d H:i:s");
$expire = gmdate("Y-m-d 23:59:59");


$result2 = mysql_query( "SELECT COUNT(*) FROM $table where avatar_group = 'Central' AND dt_detection BETWEEN $today AND $expire group by avatar_name");
$numofrows2 = mysql_num_rows($result2); 

 

where avatar_group is a name of the group and dt_detection is detection datetime type of file, it should count how many avatars on it.

 

Thanks.

[Illusion]

the query should be

SELECT SUM(COUNT(dt_detection)  as total_visits FROM $table where avatar_group = 'Central' AND dt_detection BETWEEN '$today' AND '$expire' group by avatar_name

which results the total no of visits in required period for particular group.

It could be better if you use date data type on the dt_detection column, if you use varchar or some other it increases the execution time.Might, there will be other way of writing this query very simple.

 

[neo_phyte]

But how you add to the query when a certain avatar log off then later on shall we say 5 minutes he/she log on again, then that should log 2 counts for this statistics.

[fenway]

But how you add to the query when a certain avatar log off then later on shall we say 5 minutes he/she log on again, then that should log 2 counts for this statistics.

Huh?

[Illusion]

But how you add to the query when a certain avatar log off then later on shall we say 5 minutes he/she log on again, then that should log 2 counts for this statistics.

as long as you are inserting user login time when he login again in  dt_detection column , it comes under the count.