mySql search "sub.domain.com" from "domain.com"

Question

Summery:

In MySql table Rows are

• domain.com
• domain.net
• stackoverflow.com

I want to search "sub.domain.com" from table... How??

---

Detail: Hi, I am working on email validation script,

I have a list of blacklist domains in array... Now i want to move to database...

But some logically issue..

Previous code, with array

$blacklist_domains  = array("domain.com","domain.net");
    foreach($blacklist_domains as $val) {
        //$domain can be with or without sub domain
        if( preg_match( "/$val/", $domain)){
               return true;
        }
    }

here working is, i have domain list, but when i want to search domain, it may be domain/sub domain (My Theoretically, but this theory still not true) So this loop searching blacklist in domain

foreach($blacklist_domains as $val) {
  //$domain can be with or without sub domain
  if( preg_match( "/$val/", $domain)){
         return true;
  }
}

**run time concept:**

//preg_match( "/blacklist/", "find this");
if( preg_match( "/domain.com/", "sub.domain.com")){
    return true;
}

Question: Now, i want to move this concept to database... in database my rows are,

• domain.com
• domain.net

now i want to search sub.domain.com in table

How OR any better idea?

My aim is, to just check email blacklist domain from my blacklist domain list in database

Thank you

Answer 1

If you would have had sub.domain.com in your database and were searching for domain.com, you would have used LIKE:

SELECT * FROM domains WHERE domain LIKE '%domain.com%';

But you don't

That means, you need a "reversed" LIKE:

SELECT * FROM domains WHERE 'sub.domain.com' LIKE CONCAT('%', domain, '%');

To make your code more efficient, you can even use COUNT() to get the number of rows to directly use in your code:

SELECT COUNT(*) AS count FROM domains WHERE 'sub.domain.com' LIKE CONCAT('%', domain, '%');