Page 1 of 3

MySQL two-part selection

Posted: Thu Jun 02, 2005 3:51 pm
by Todd_Z
The first part retrieves a list of all the ip addresses who have visited the site within a timerange:

Code: Select all

$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?

Posted: Thu Jun 02, 2005 4:11 pm
by timvw
Depends a bit on your DBMS. I believe the following query would do what you asked...

Code: Select all

SELECT COUNT(*) AS count
FROM visitors
WHERE timestamp < 1117692000 
AND ip IN ( SELECT ip
            FROM visitors
            WHERE timestamp BETWEEN 1117692000 AND 1117695599 )

Posted: Thu Jun 02, 2005 5:33 pm
by Todd_Z

Code: Select all

SELECT COUNT(*) AS 'Count' FROM `Visitors` WHERE `Timestamp` < '1117692000' AND `IP` IN ( SELECT `IP` FROM `Visitors` WHERE `Timestamp` BETWEEN '1117692000' AND '1117695599' )
returns...

Code: Select all

#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

Posted: Thu Jun 02, 2005 5:56 pm
by Burrito
you gotta have 4.1 to do subqueries...do you have that?

Posted: Thu Jun 02, 2005 6:21 pm
by Todd_Z
4.0 - BAH
<don't get bluehost>

Posted: Fri Jun 03, 2005 1:20 am
by Syranide
4.0 should be used as 4.1 isn't really supported widely.
just do double queries? or simply apply some PHP functions to sort it out yourself?

(not sure of what you really meant)

Posted: Fri Jun 03, 2005 11:05 am
by Todd_Z
I am working on a really compact script, so I was hoping not to use php, and instead do it in the sql.

Posted: Fri Jun 03, 2005 11:10 am
by Syranide
but I didn't really get your problem?
you mean like getting two sets?

Posted: Fri Jun 03, 2005 4:25 pm
by timvw
the shortest i can think of

- 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

Posted: Fri Jun 03, 2005 5:14 pm
by RobertGonzalez
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.

Posted: Fri Jun 03, 2005 5:47 pm
by pickle
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. :wink:

Could you elaborate a bit?

Posted: Fri Jun 03, 2005 5:51 pm
by timvw
I dont know why... but this popped in my thoughts....

Code: Select all

SELECT DISTINCT V.IP
FROM visitors AS V
INNER JOIN visitors AS V2 USING ip
WHERE V.timestamp < 11111111
AND V2.timestamp BETWEEN 1111111 AND 222222

Posted: Fri Jun 03, 2005 6:24 pm
by RobertGonzalez

Code: Select all

SELECT DISTINCT V.IPFROM visitors AS VINNER JOIN visitors AS V2 USING ipWHERE V.timestamp &lt; 11111111AND V2.timestamp BETWEEN 1111111 AND 222222
Maybe add one more step to it and set v1.IP equal to v2.IP...

Code: Select all

$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.

Posted: Fri Jun 03, 2005 6:56 pm
by Todd_Z

Code: Select all

$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?

Posted: Fri Jun 03, 2005 6:59 pm
by Burrito
select count()

or

mysql_num_rows()