[SOLVED] Difficult counting query

[otuatail]

Ok This is the problem. I have a website that sores all the activity of a visitor. I note the first visit for hit counting purposes. I continue to note all the pages visited. I store a single character that tells me the state of the record. Y=New visit. N=Continuation of the visit. R=WebRobt whant to keep a record of these machines but not have them screw up the hit count.

 

I also store the IP address and the date-time as a Unix time(). This is stored in a string 10 characters. Didn’t want to run into overflow problems at the time, and the page name.

 

Example 1. One guy visits and looks at 3 pages.

 

Date  IP  State

‘1000222230’  ‘100.100.100.100’  ‘Home’ ‘Y’

‘1000223330’  ‘100.100.100.100’  ‘Page1’ ‘N’

‘1000224430’  ‘100.100.100.100’  ‘Page1’ ‘N’

 

The problem is I want to find out the un-happy visitors. The ones that go to the home page and leave.

 

Example 2.

 

‘1000550010’  ‘100.100.100.100’  ‘Home’ ‘Y’

‘1000555010’  ‘122.166.114.127’  ‘Home’ ‘Y’

 

Both of these guys left strait away.

 

I need to count these up.

 

Is this achievable.

 

Desmond.

 

 

 

[ohdang888]

<?php
$res = mysql_query("SELECT `ip` FROM `table name` WHERE `page`='Home' AND `state`='Y' ") or die(mysql_error());// grab all first view pages
while($row = mysql_fetch_array($res)){
$ip = $row['ip'];// ip address that was stored in table

$res2 = mysql_query("SELECT COUNT(ip) FROM `table name` WHERE `ip`='$ip' ") or die(mysql_error());// see if there are any other pageviews for this ip
$row2 = mysql_fetch_array($res2);
$count = $row2['COUNT(ip)'];

if($count == 0){// if there are no other page views for that ip

// create an admin table, and insert the $row['ip'], $row['time'], etc.
}

}
?>

 

and then on the admin table... to count the values...

 

admin.php

<?php
$res = mysql_query("SELECT COUNT(ip) FROM `admin table name`") or die(mysql_error());//count number of ip in admin table
$row = mysql_fetch_array($res);
$count = $row['COUNT(ip)'];

echo "There were $count first views that left after viewing the home page";


?>

[otuatail]

Thanks for that. Hoped it would be a strait quiery but it has to be done this way.

 

Desmond.

 

[fenway]

There's always another way....