PHP/PDO - Check if URL contains a particular list of domains

Question

I have a list of domains in an array e.g.

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

Now I have a MySQL database that contains a column full of urls e.g.

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

Now I want to extract the above URLS from the above table where the domains exist in the url. In other words I want to extract: 1. facebook.com/testpage 2. google.com/search 3. google.com/time

This is my code:

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'];
}

I'm not sure how I'm going to modify this line $sqlQueryBusinessWebsite = $dbh->prepare("SELECT business_website FROM client_table WHERE business_website = :business_website"); to check whether the domain exists in the url.

Thanks.

Use IN clause without use of foreach loop acheck http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — Saty, May 10 '16 at 13:10
Is the list of banned/not allowed websites in the database or within code? If it's in the database it would be A LOT easier to manage? — Gavin, May 10 '16 at 13:21
Actually it is in a database I used the array in my question to simply my question. — user6043723, May 10 '16 at 13:33
@Gavin. I think I found my problem. My array is actually values stored in my database. On the outter set I have a mysql function that lists all the "blocked websites". Then I'm trying to call the MySQL code. My problem seems to be in this line - `$sqlResultBusinessWebsite = $sqlQueryBusinessWebsite->fetch();` It doesn't seem to be outputting anything. — user6043723, May 10 '16 at 13:54
@user6043723 - Most likely because the query is expecting an exact match. You would need to use a like and if there is more than one result, you'll need to loop. The solution I've provided will output a list of values where the domain exists in the business_website column. This way you only need to process the results, not loop through and query every single domain. — Gavin, May 10 '16 at 14:04

David D · Answer 1 · 2016-05-10T13:39:46.317

0

use the LIKE clause:

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

edited May 10 '16 at 13:39

answered May 10 '16 at 13:06

David D

1,269
17
22

are you sure the database values start with google.com, yahoo.com? and nothing before? – David D May 10 '16 at 13:12
Yes they are.Thanks. – user6043723 May 10 '16 at 13:13
Ah that's probably why, try it now, I've updated it. – David D May 10 '16 at 13:40

score 0 · Answer 2 · answered May 10 '16 at 13:09

0

Using Regular Expression would be the best solution here

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

answered May 10 '16 at 13:09

Murtaza Khursheed Hussain

15,176
7
58
83

I have hundreds of urls that is why I'm using an array. – user6043723 May 10 '16 at 13:12

score 0 · Answer 3 · answered May 10 '16 at 13:46

Although I'm sure there is a cleaner way, the following will get all sites that contain a domain from the banned list:

CREATE TABLE `banned_sites` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `domain` VARCHAR(255) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

CREATE TABLE `entries` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `website` VARCHAR(255) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

insert into banned_sites (domain) values ('facebook.com'), ('yahoo.com'), ('google.com');
insert into entries (website) values ('http://www.facebook.com/thisisatest'), ('http://www.msn.com'), ('http://google.com'), ('yahoo.com');

Then you can do:

select e.* from entries e inner join banned_sites bs on (e.website like concat('%', concat(bs.domain, '%')))

SQLFiddle isn't working at the moment, otherwise I'd show you it working.

PHP/PDO - Check if URL contains a particular list of domains

3 Answers3