Databases
StackOverflow2013
Postsdbo
POMySQL IP address camparing to ISP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL IP address camparing to ISP
    primarykey
    data
    text
    <p>I have 2 tables in my mySQL database.</p> <p>First table is isp:</p> <pre><code>isp_ip_address | isp_name ------------------------- 157.55.33.0 | isp_3 157.55.0.0 | isp_1 193.219.9.0 | isp_2 65.55.213.0 | isp_4 195.182.85.226 | isp_5 78.58.5.117 | isp_6 78.58.5.0 | isp_7 </code></pre> <p>Second table is user_stats:</p> <pre><code>uid | lastlogin_ip | lastlogin ------------------------------ 111 | 78.58.83.111 | 2013-03-12 132 | 78.58.5.117 | 2013-03-12 258 | 195.182.85.226 | 2013-03-14 165 | 157.55.33.78 | 2013-03-12 822 | 65.55.213.216 | 2013-03-15 523 | 157.55.35.38 | 2013-03-12 ..... </code></pre> <p>And so on.</p> <p>I need to compare users lastlogin_ip to isp_ip_address and in a new table insert uid, lastlogin_ip and isp_name. I need to compare ip_address like this: if full lastlogin_ip equals isp_ip_address then stop searching and write isp_name, if they don't match, I need to compare firdt 3 parts of IP addresses and do the same, and if 3 parts don't match, I need to compare 2 parts of IP addresses and do the same.</p> <p>I tried the following SQL code:</p> <pre><code>SELECT user_stats.`uid`, user_stats.`lastlogin_ip`, isp.`isp`, FROM stats.`user_stats` LEFT JOIN stats.`isp` ON( IF( isp.isp_ip_address LIKE user_stats.`lastlogin_ip`, isp.isp_ip_address LIKE user_stats.`lastlogin_ip`, IF( SUBSTRING_INDEX(isp.isp_ip_address,'.',-1) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',-1), IF( SUBSTRING_INDEX(SUBSTRING_INDEX(isp.isp_ip_address,'.',3),'.',-1) LIKE SUBSTRING_INDEX(SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',3),'.',-1), IF( SUBSTRING_INDEX(SUBSTRING_INDEX(isp.isp_ip_address,'.',2),'.',-1) LIKE SUBSTRING_INDEX(SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',2),'.',-1), SUBSTRING_INDEX(isp.isp_ip_address,'.',1) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',1), NULL), SUBSTRING_INDEX(isp.isp_ip_address,'.',2) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',2)), SUBSTRING_INDEX(isp.isp_ip_address,'.',3) LIKE SUBSTRING_INDEX(user_stats.`lastlogin_ip`,'.',3)))); </code></pre> <p>This code compares parts of IP addresses but it duplicates rows, which means that if it finds 3 equal parts and 2 equal parts, it writes the same row two times with different isp_name values.</p> <p>Could you give me a hint how should I edit this code to work as it should? Any help would be appreciated!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShjpotter92
    1. USaurimokas
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload