PHP/PDO - 检查 URL 是否包含特定的域列表

Question

我有一个数组中的域列表，例如

$notAllowedWebsites = array('google.com','yahoo.com','facebook.com');

现在我有一个 MySQL 数据库，其中包含一列充满网址的列，例如

1. facebook.com/testpage
2. google.com/search
3. bbc.co.uk/news/going-to-the-sea
4. ft.com/big-success
5. google.com/time

现在我想从上表中提取上面的 URL，其中 URL 中存在域。换句话说，我想提取: 1.facebook.com/testpage 2. google.com/搜索 3.google.com/time

这是我的代码:

foreach($notAllowedWebsite as $notAllowedWebsite){
    $sqlQueryBusinessWebsite = $dbh->prepare("SELECT business_website FROM client_table WHERE business_website = :business_website");
    $sqlQueryBusinessWebsite->execute(array(':business_website'=>$notAllowedWebsite));
    $sqlResultBusinessWebsite = $sqlQueryBusinessWebsite->fetch();
    $businessWebsiteDB = $sqlResultBusinessWebsite['business_website'];
}

我不确定如何修改此行 $sqlQueryBusinessWebsite = $dbh->prepare("SELECT Business_website FROM client_table WHERE Business_website = :business_website"); 来检查是否网址中存在域名。

谢谢。

Answer 1

使用Regular Expression将是这里最好的解决方案

SELECT business_website FROM business_website WHERE business_website RLIKE '(?=.*facebook)(?=.*yahoo)'