MySQL two-part selection

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!

Moderator: General Moderators

User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

MySQL two-part selection

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 )
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

you gotta have 4.1 to do subqueries...do you have that?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

4.0 - BAH
<don't get bluehost>
Syranide
Forum Contributor
Posts: 281
Joined: Fri May 20, 2005 3:16 pm
Location: Sweden

Post 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)
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
Syranide
Forum Contributor
Posts: 281
Joined: Fri May 20, 2005 3:16 pm
Location: Sweden

Post by Syranide »

but I didn't really get your problem?
you mean like getting two sets?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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?
Last edited by Todd_Z on Fri Jun 03, 2005 7:00 pm, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

select count()

or

mysql_num_rows()
Post Reply