Using PHP MySQL, I'm storing in a DB a user's IP and the at my site that they entered on. The rows look like:
ip entrypoint
xxx.xxx.xxx.1 /index.php?source=google
xxx.xxx.xxx.5 /other.php?source=bing
xxx.xxx.xxx.5 /other.php?source=yahoo
xxx.xxx.xxx.4 /more.php
I'm trying to make a query that takes up to 300 IP's from a form and searches for where the users came from so I started with this (shortened to show only 4):
SELECT entrypoint,ip from TABLE WHERE ip IN('xxx.xxx.xxx.1','xxx.xxx.xxx.5','xxx.xxx.xxx.5','xxx.xxx.xxx.4') AND entrypoint LIKE '%source=%';
This gives me a result like:
ip entrypoint
xxx.xxx.xxx.1 /index.php?source=google
xxx.xxx.xxx.5 /other.php?source=bing
xxx.xxx.xxx.5 /other.php?source=yahoo
Some IPs have 1,000+ entries because of proxies, bots etc so I can't use their info. If there is more than one "entrypoint" that contains "source=" for a given IP, I want to ignore that row. It also makes searching large numbers of IPs difficult because if I am searching for one of those IP's and return all of the rows associated with it, I'll run out of memory in PHP.
Instead of making PHP do the work of sorting out the results that share an IP, is there a way to write the query so that it won't return anything for an "ip" if there is more than one value in "entrypoint"? That is I want it to return:
xxx.xxx.xxx.1 /index.php?source=google
If I ran it on just those 4 rows above using those 4 IP's. Since xxx.xxx.xxx.5 had two different entrypoints, I want to ignore those rows.
You should group by ip your result, count the number of distincts entrypoint, and returning the ones with 1 entrypoint
SELECT entrypoint,ip, count(distinct entrypoint) nb
from TABLE WHERE ip
IN('xxx.xxx.xxx.1','xxx.xxx.xxx.5','xxx.xxx.xxx.5','xxx.xxx.xxx.4')
AND entrypoint LIKE '%source=%' group by ip HAVING nb=1;