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

Post by Todd_Z »

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

Post by Burrito »

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

Post by Todd_Z »

Changes each value to 1, but still returns the amount of rows as IPs fit the criteria.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Todd_Z wrote:Changes each value to 1, but still returns the amount of rows as IPs fit the criteria.
does that mean it worked?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

What I mean is it returns the following:

1
1
1
1

instead of 4
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Code: Select all

$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...
what does that do for you?
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 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';
Yields:
thismany
1
1
1
1
1
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

that's really odd, it should only return one row. What does mysql_num_rows tell you?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

in that case, 5.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

unfortunately I don't know then...

it could be the joins doing something that I don't know about.

perhaps one of the other's who's posted in this thread can shed some light as I'm fresh out of ideas.

sorry mang
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Everah wrote: Maybe add one more step to it and set v1.IP equal to v2.IP...
That is already done by the INNER JOIN :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

@Todd_Z

please notice my query didn't have a GROUP BY (Well it's shouldn't have)

Code: Select all

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

Post by Todd_Z »

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

Post by timvw »

lookup what EXPLAIN can do for you...
lookup on which columns an INDEX can help...

do your own benchmarking mysql<->php processing...
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

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
Post Reply