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!
$sql = "SELECT `IP` FROM `Visitors` WHERE `timestamp` > 1117692000 AND `timestamp` < 1117695599 GROUP BY `IP`";
I want to take the results from that, and check out of those ips, how many of them are in the database before the earlier timestamp. I want to do this all in one sql. Is this possible?
SELECT COUNT(*) AS count
FROM visitors
WHERE timestamp < 1117692000
AND ip IN ( SELECT ip
FROM visitors
WHERE timestamp BETWEEN 1117692000 AND 1117695599 )
SELECT COUNT(*) AS 'Count' FROM `Visitors` WHERE `Timestamp` < '1117692000' AND `IP` IN ( SELECT `IP` FROM `Visitors` WHERE `Timestamp` BETWEEN '1117692000' AND '1117695599' )
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `IP` FROM `Visitors` WHERE `Timestamp` BETWEEN '11176920
- select distint ip's that have been used in the interval.
- iterate through them and add them to a $where ($where .= "ip='{$row['ip']}' OR ")
- create the query to select ips before the start of the interval and add the $where
- retrieve ips
The first part retrieves a list of all the ip addresses who have visited the site within a timerange:
PHP (1 line) ::
1
$sql = "SELECT `IP` FROM `Visitors` WHERE `timestamp` > 1117692000 AND `timestamp` < 1117695599 GROUP BY `IP`";
I want to take the results from that, and check out of those ips, how many of them are in the database before the earlier timestamp. I want to do this all in one sql. Is this possible?
No, you can't. If the IP's you are selecting are within a range (After timestamp1 AND before timestamp 2) then your result will only include rows that are in the DB after timestamp1. This will not show any rows before timestamp1, which is what you want.
Your question doesn't make sense to me. You're asking to get all IP between time1 and time2, then out of those, get all the ips that were before time1? You're not going to get any results back if that's what you want.
Could you elaborate a bit?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
$sql = "SELECT v1.IP 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";
This selects all IP in the visitors table that have a timestamp before the early one and a timestamp that is between the early and late.
$sql = "SELECT v1.IP 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";
That worked beautifully... How do I make it simply return the number of rows in the result, I don't need the actual IP addresses.
BTW, I hate to be so picky, but doing this like 20 times a page takes quite a while. Any way to speed it up?
Last edited by Todd_Z on Fri Jun 03, 2005 7:00 pm, edited 1 time in total.