note 85366 deleted from function.ip2long by cmb

Note Submitter: Teguh Iskanto - Kamprettos_at_yahoo ---- A quick way to find which IP address belong to which network , *without* even creating a single PHP function (pure SQL) Scenario : - I have one table that has a list of host ip addresses - I have another table that list all the networks' addresses - I need to find which host belongs to which network nodes table +-------------+-----------------+-------+ | ip | mask | name | +-------------+-----------------+-------+ | 192.168.1.1 | 255.255.255.0 | node1 | | 192.168.1.1 | 255.255.255.252 | node2 | | 192.168.2.1 | 255.255.255.252 | node3 | +-------------+-----------------+-------+ network1 table +-------------+---------------+------+ | ipaddr | netmask | name | +-------------+---------------+------+ | 192.168.1.0 | 255.255.255.0 | net1 | | 192.168.2.0 | 255.255.255.0 | net2 | | 192.168.3.0 | 255.255.255.0 | net3 | | 192.168.4.0 | 255.255.255.0 | net4 | +-------------+---------------+------+ Solution 1 - with PHP ( Long ... ) : 1. create a PHP function that will calculate and compare IP address with the network 2. extract data from the table nodes, calculate the IP with its mask using a PHP function to get the network address 3. do the same with table network1 4. compare result 2 and result 3 , once finished put them into an array to be presented as an HTML 5. done Solution 2 - With SQL ( Very Very Fast & Short ) : 1. create sql with 'inet_aton' function + table joins 2. extract the data from sql outputs 3. done how : SQL : select a.name as host_name, a.ip as host_ip, b.name as net_name from nodes a, network1 b where (inet_aton(a.ip) & inet_aton(a.mask) = inet_aton(b.ipaddr) & inet_aton(b.netmask)); Results : Voilla ... +-----------+-------------+----------+ | host_name | host_ip | net_name | +-----------+-------------+----------+ | node1 | 192.168.1.1 | net1 | | node2 | 192.168.1.1 | net1 | | node3 | 192.168.2.1 | net2 | +-----------+-------------+----------+ Hope this helps