Check for existing URL in MySQL Database PHP

Question

I want users to be able to submit a URL, which gets checked against the database to see if there is already an entry. However, I want the check to also ignore the domain.

For example if www.example.com exists, then www.example.net, www.example.gov, etc should also be rejected. However an SQL check here will come back with 0 records, since they are considered different.

Is there any efficient way to do this? The only way I can think of is to delimit the given URL and take the main domain and search for that particular domain using SQL's 'Like' functionality. However, this would cause problems when dealing with urls like blogspot, etc.

I'm stumped, any advice would be great!

The question is not 'laravel-4' specific. Could you pls delete that tag? — Daniel Gasser
– Daniel Gasser, Commented May 30, 2014 at 9:24
@pc-shooter I added it hoping Laravel might have a useful helper function.. If you can confirm there is no such function/package, by all means I'll take it off :) — Sainath Krishnan
– Sainath Krishnan, Commented May 31, 2014 at 0:32

Chelsea Urquhart · Accepted Answer · 2014-05-30 06:46:19Z

Why not do something like this?

SELECT `name` FROM `domains` WHERE LEFT(`name`, LENGTH(`name`) - LOCATE('.', REVERSE(`name`))) = 'example';

Then you just need to strip out the TLD of the search term via PHP, or replace 'example' with LEFT('example.com', ...). I'd probably do that part via php though.

If you want to also match any subdomains, you could use something like:

SELECT `name` FROM `domains` WHERE SUBSTRING_INDEX(LEFT(`name`, LENGTH(`name`) - LOCATE('.', REVERSE(`name`))), '.', -1) = 'example';

Please note that this if searching for example this second query will match example.com and dev.example.com but NOT dev.dev.example.com. I think you'd need to use PHP if you want to go that far. Also if using the second one, you'd also need to strip out the subdomain via php beforehand (or do the same sort of thing (SUBSTRING_INDEX(LEFT('example.com', ...)))

Hope this helps! BTW if you're using Laravel's query builder, you'll need to use whereRaw to be able to do this.

myfunkyside · Accepted Answer · 2014-05-30 04:59:18Z

0

I think you should get all the existing entries from the database with php, store them in an array, and then loop through that array en check if the newly submitted domain already exists as part of every entry

//store database entries into array
$existingURLs = []; //all urls from database
...
$newdomain = substr($newURL,0,strrpos($newURL,'.')); //removes the '.com'

foreach ($existingURLs as $url) {
    if (strpos($url,$newdomain) !== false) {
        echo 'domain already exists';
    }
}

answered May 30, 2014 at 4:59

myfunkyside

3,9601 gold badge21 silver badges33 bronze badges

7 Comments

Sainath Krishnan Over a year ago

This of course can be worked into a crude solution using a list of such domains maybe, but it would be extremely inefficient on a very large scale

myfunkyside Over a year ago

how many of those kinda subdomains like blogspot are there? If it's only a few you can build in if-clauses inside the if (strpos($url,$newdomain) !== false)... If ('blogspot' is in the string) {//check the subdomain before the first dot}. ...aaand i think i just got my answer:)

myfunkyside Over a year ago

oh, oh, maybe, first check how many dots (.) are in the url.. if there are 3 (or 2 but without 'www') than you have a subdomain right? so thatn you check the subdomain before the first dot

shyammakwana.me Over a year ago

from little search i found this answer,

myfunkyside Over a year ago

yeah, for now i think you're stuck with such a list, just remembered uk has www.domain.co.uk (could of course also check for that, but, well..)

|

Collectives™ on Stack Overflow

Check for existing URL in MySQL Database PHP

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related