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? — toesslab, May 30 '14 at 09: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, May 31 '14 at 00:32

score 1 · Accepted Answer · answered May 30 '14 at 06:46

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.

score 0 · Answer 2 · answered May 30 '14 at 04:59

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 '14 at 04:59

myfunkyside

3,890
1
17
32

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 – Sainath Krishnan May 30 '14 at 05:05
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 May 30 '14 at 05:05
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 – myfunkyside May 30 '14 at 05:07
from little search i found [this answer](http://stackoverflow.com/questions/569137/how-to-get-domain-name-from-url), – shyammakwana.me May 30 '14 at 05:09
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..) – myfunkyside May 30 '14 at 05:11
@myfunkyside The problem with your solution is that it is not feasible in large scale applications. It is a bad programming practice to hard code values into code, or even a database, as that would just lead to unnecessary database calls. – Sainath Krishnan May 30 '14 at 05:34
jep.. I would put that list in a large array in an extra php-file which you include before you do the check, that way it won't cloud up your real code and be as little hard coded as I can think of.. but still, not ideal no, wish I had something better but nogt at the moment and the people on the link from TechnoKnol seem to agree (that doesn't mean you, or I, should stop searching though:) NEVAH! – myfunkyside May 30 '14 at 05:49

Check for existing URL in MySQL Database PHP

2 Answers2