PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
I thought it would be to add COUNT() in there: SELECT COUNT(v1.IP), however, this simply returns the number of times that the ip address is in the database.
$sql = "SELECT count(distinct(v1.IP)) as thismany FROM visitors v1 INNER JOIN visitors v2 ON v2.IP = v1.IP WHERE (v1.timestamp BETWEEN lower_limit AND upper_limit) AND v2.timestamp < lower_limit GROUP BY v1.IP";
// then echo thismany...
$sql = 'SELECT COUNT(DISTINCT(v1.IP)) as thismany FROM Visitors v1 INNER JOIN Visitors v2 ON v2.IP = v1.IP WHERE (v1.timestamp BETWEEN 1117800000 AND 1117803599) AND v2.timestamp < 1117800000 GROUP BY v1.IP LIMIT 0, 30';
SELECT COUNT(DISTINCT V1.IP) AS count
FROM visitors AS v1
INNER JOIN visitors AS v2
WHERE v1.timestamp < $start
AND v2.timestamp BETWEEN $start AND $end
Timvw, that works perfectly, however, it takes sooo long to do. The load time for the page is unacceptably long. Is there anyway to do this much faster? Would it be better if i just caved and did it in php?
Structure Dump:
CREATE TABLE `Visitors` (
`IP` text NOT NULL,
`Timestamp` int(10) unsigned NOT NULL default '0',
`Page` text NOT NULL,
`Referer` text NOT NULL,
`Browser` text NOT NULL,
`Platform` text NOT NULL,
`ID` int(20) unsigned NOT NULL auto_increment,
UNIQUE KEY `ID` (`ID`)
) TYPE=MyISAM;
results of that sql with EXPLAIN at the beginning:
table type possible_keys key key_len ref rows Extra
v1 ALL NULL NULL NULL NULL 6048 Using where
v2 ALL NULL NULL NULL NULL 6048 Using where