James Sandzalone James Sandzalone - 26 days ago 7
PHP Question

Search & Compare Multiple MySQL Tables

I'm trying to use PHP to search for IPs and ad networks in a MySQL database. Basically we rotate visitors between various ad networks to prevent the same visitor from being sent to the same network within a 24 hour period.

ip_addresses table

id, address, network_id
1, 120.110.140.223, 1
2, 120.110.140.223, 3
3, 115.157.247.46, 1


networks table

id, name, clicks, status, order
1, Random Name, 200, Active, 1
2, Example Name, 500, Inactive, 3
3, Other Name, 100, Active, 2


Basically when a visitor hits our PHP page, I need to be able to echo a specific
id
from the
networks
table. It should be the ID of the first network that isn't already listed in the
ip_addresses
table for the visitor's IP address. The networks should be ordered by the
order
column and should have
status = Active
.

I thought about using PHP to get a list of the
id
's of all networks with their
status
set to
Active
and correctly ordered like this:

SELECT id FROM networks WHERE status = 'Active' ORDER BY id ASC


And then just loop through until I reach the first
id
that isn't in the
ip_addresses
table for their IP. However, I wasn't sure how to do that loop or how to make it stop once the first
network_id
that isn't in the
ip_addresses
table is found.

If I need to clarify anything, please let me know. Thanks!

Answer

You could use NOT IN from a sub-query by passing the user's IP

SELECT id
FROM networks
WHERE status = 'Active'
  AND id NOT IN (
    SELECT network_id
    FROM ip_addresses
    WHERE address = '$ip'
)
ORDER BY id
LIMIT 1;